Complex value lookup? (Excel 2003)
 

I am setting up a cost calculation workbook. For the purposes of this
discussion, it will have two worksheets: VARIABLES and COST CALCULATIONS.

Some of the items on the VARIABLES sheet have multiple prices with price
break tiers. E.g. (totally made up prices below)

Software #Licenses (up to) Price
Adobe Acrobat 10 $500
Adobe Acrobat 25 $450
Adobe Acrobat 50 $400
Adobe Acrobat 51+ $375
WebTrends 75 $100
WebTrends 150 $75
WebTrends 151+ $70

In the COST CALCULATIONS sheet, there will be columns with Software and #
licenses. So, if I have

Software #Licenses
Adobe Acrobat 14

I need to have my formula grab the $450 price. For the life of me I can't
figure out how to do this in Excel. Is this possible?

As always, thanks very much for your help.

Ann Scharpf


--
Ann Scharpf

Complex value lookup? (Excel 2003)
 

<Is this possible?

Sure, but you'll have to change the layout of your price table. Or create a
new one which is derived (through formulas) from the original one.

First, you need a threshold value for the lowest price, that is, zero.
So, for Adobe, the layout should be:
0 500
10 450
25 400
50 375

Second, you need to define names for the areas in the table that corrsepond
to products. Since you can not have spaces in a defined name, use
underscores instead. So, for adobe, in cells A1:B4, define the name
adobe_acrobat. Etc.

Now, with the product name in C1 and the quantity in D1, use this formula:

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","_")),2)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
I am setting up a cost calculation workbook. For the purposes of this
discussion, it will have two worksheets: VARIABLES and COST CALCULATIONS.

Some of the items on the VARIABLES sheet have multiple prices with price
break tiers. E.g. (totally made up prices below)

Software #Licenses (up to) Price
Adobe Acrobat 10 $500
Adobe Acrobat 25 $450
Adobe Acrobat 50 $400
Adobe Acrobat 51+ $375
WebTrends 75 $100
WebTrends 150 $75
WebTrends 151+ $70

In the COST CALCULATIONS sheet, there will be columns with Software and #
licenses. So, if I have

Software #Licenses
Adobe Acrobat 14

I need to have my formula grab the $450 price. For the life of me I can't
figure out how to do this in Excel. Is this possible?

As always, thanks very much for your help.

Ann Scharpf


--
Ann Scharpf

Complex value lookup? (Excel 2003)
 

This is outstanding! I have never used INDIRECT() or SUBSTITUTE(). Works
like a gem. Thank you so much! Now I have to mull it over so I can
*UNDERSTAND* why it works!

--
Ann Scharpf


"Niek Otten" wrote:

<Is this possible?

Sure, but you'll have to change the layout of your price table. Or create a
new one which is derived (through formulas) from the original one.

First, you need a threshold value for the lowest price, that is, zero.
So, for Adobe, the layout should be:
0 500
10 450
25 400
50 375

Second, you need to define names for the areas in the table that corrsepond
to products. Since you can not have spaces in a defined name, use
underscores instead. So, for adobe, in cells A1:B4, define the name
adobe_acrobat. Etc.

Now, with the product name in C1 and the quantity in D1, use this formula:

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","_")),2)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
I am setting up a cost calculation workbook. For the purposes of this
discussion, it will have two worksheets: VARIABLES and COST CALCULATIONS.

Some of the items on the VARIABLES sheet have multiple prices with price
break tiers. E.g. (totally made up prices below)

Software #Licenses (up to) Price
Adobe Acrobat 10 $500
Adobe Acrobat 25 $450
Adobe Acrobat 50 $400
Adobe Acrobat 51+ $375
WebTrends 75 $100
WebTrends 150 $75
WebTrends 151+ $70

In the COST CALCULATIONS sheet, there will be columns with Software and #
licenses. So, if I have

Software #Licenses
Adobe Acrobat 14

I need to have my formula grab the $450 price. For the life of me I can't
figure out how to do this in Excel. Is this possible?

As always, thanks very much for your help.

Ann Scharpf


--
Ann Scharpf

Complex value lookup? (Excel 2003)
 

Glad it works for you!
Don't hesitate to post again (in this same thread) if you have difficulties
understanding how it works. We try to make you self-supporting.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
This is outstanding! I have never used INDIRECT() or SUBSTITUTE(). Works
like a gem. Thank you so much! Now I have to mull it over so I can
*UNDERSTAND* why it works!

--
Ann Scharpf


"Niek Otten" wrote:

<Is this possible?

Sure, but you'll have to change the layout of your price table. Or create
a
new one which is derived (through formulas) from the original one.

First, you need a threshold value for the lowest price, that is, zero.
So, for Adobe, the layout should be:
0 500
10 450
25 400
50 375

Second, you need to define names for the areas in the table that
corrsepond
to products. Since you can not have spaces in a defined name, use
underscores instead. So, for adobe, in cells A1:B4, define the name
adobe_acrobat. Etc.

Now, with the product name in C1 and the quantity in D1, use this
formula:

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","_")),2)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
I am setting up a cost calculation workbook. For the purposes of this
discussion, it will have two worksheets: VARIABLES and COST
CALCULATIONS.

Some of the items on the VARIABLES sheet have multiple prices with
price
break tiers. E.g. (totally made up prices below)

Software #Licenses (up to) Price
Adobe Acrobat 10
$500
Adobe Acrobat 25
$450
Adobe Acrobat 50
$400
Adobe Acrobat 51+
$375
WebTrends 75
$100
WebTrends 150
$75
WebTrends 151+ $70

In the COST CALCULATIONS sheet, there will be columns with Software and
#
licenses. So, if I have

Software #Licenses
Adobe Acrobat 14

I need to have my formula grab the $450 price. For the life of me I
can't
figure out how to do this in Excel. Is this possible?

As always, thanks very much for your help.

Ann Scharpf


--
Ann Scharpf

Complex value lookup? (Excel 2003)
 

Thanks! I think I've got it now. I tend to use mixed UpperLowerCase named
ranges instead of using underscores. So I modified the formula as follows:

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","_")),2)

Changed to :

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","")),2)

I read help about the INDIRECT() function. I think I need to find some
resources to read more about that. It's really cool that you can pass the
text of the range name and have it work. And I never would've gleaned that
info from the MS help file because all the examples in the help use $A$2 type
references.)

If you can recommend any web pages with good info on the INDIRECT()
function, I'd appreciate it.

So thank you so much!!!!

--
Ann Scharpf


"Niek Otten" wrote:

Glad it works for you!
Don't hesitate to post again (in this same thread) if you have difficulties
understanding how it works. We try to make you self-supporting.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
This is outstanding! I have never used INDIRECT() or SUBSTITUTE(). Works
like a gem. Thank you so much! Now I have to mull it over so I can
*UNDERSTAND* why it works!

Complex value lookup? (Excel 2003)
 

Hi Ann,

Here is a tutorial about the INDIRECT() function (and many other subjects,
BTW)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
Thanks! I think I've got it now. I tend to use mixed UpperLowerCase
named
ranges instead of using underscores. So I modified the formula as
follows:

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","_")),2)

Changed to :

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","")),2)

I read help about the INDIRECT() function. I think I need to find some
resources to read more about that. It's really cool that you can pass the
text of the range name and have it work. And I never would've gleaned
that
info from the MS help file because all the examples in the help use $A$2
type
references.)

If you can recommend any web pages with good info on the INDIRECT()
function, I'd appreciate it.

So thank you so much!!!!

--
Ann Scharpf


"Niek Otten" wrote:

Glad it works for you!
Don't hesitate to post again (in this same thread) if you have
difficulties
understanding how it works. We try to make you self-supporting.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
This is outstanding! I have never used INDIRECT() or SUBSTITUTE().
Works
like a gem. Thank you so much! Now I have to mull it over so I can
*UNDERSTAND* why it works!

Complex value lookup? (Excel 2003)
 

Hi Ann,

Here is a tutorial about the INDIRECT() function (and many other subjects,
BTW)

http://www.contextures.com/xlFunctions05.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
Thanks! I think I've got it now. I tend to use mixed UpperLowerCase
named
ranges instead of using underscores. So I modified the formula as
follows:

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","_")),2)

Changed to :

=VLOOKUP(D1,INDIRECT(SUBSTITUTE(C1," ","")),2)

I read help about the INDIRECT() function. I think I need to find some
resources to read more about that. It's really cool that you can pass the
text of the range name and have it work. And I never would've gleaned
that
info from the MS help file because all the examples in the help use $A$2
type
references.)

If you can recommend any web pages with good info on the INDIRECT()
function, I'd appreciate it.

So thank you so much!!!!

--
Ann Scharpf


"Niek Otten" wrote:

Glad it works for you!
Don't hesitate to post again (in this same thread) if you have
difficulties
understanding how it works. We try to make you self-supporting.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ann Scharpf" wrote in message
...
This is outstanding! I have never used INDIRECT() or SUBSTITUTE().
Works
like a gem. Thank you so much! Now I have to mull it over so I can
*UNDERSTAND* why it works!