Complex value lookup? (Excel 2003)

[Ann Scharpf]

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

 

[Niek Otten]

<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)

 

[Ann Scharpf]

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!

 

[Niek Otten]

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

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]

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!!!!

 

[Niek Otten]

Hi Ann,

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

 

[Niek Otten]

Hi Ann,

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

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