| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex MAX and Lookup | Excel Discussion (Misc queries) | |||
| Complex formulae??????????? Excel 2003 | Excel Discussion (Misc queries) | |||
| Complex Lookup | Excel Discussion (Misc queries) | |||
| How to solve a complex problem using Excel 2003 | Excel Discussion (Misc queries) | |||
| Where to find complex matrix math add-ins for Excel 2003? | Excel Worksheet Functions | |||
Linear Mode
