Complex value lookup? (Excel 2003)

  • Thread starter Thread starter Ann Scharpf
  • Start date Start date
A

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
 
<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)
 
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!
 
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 said:
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!
Click to expand...
 
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!!!!
 
Hi Ann,

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