Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
lookup confusion
Hi -- Can someone help me with this formula?
=INDEX(INDIRECT("'"&DealPlatformType&" Platform'!I90:I98"),MAX(1,MATCH(Planner!DealBlockS ize,INDIRECT("'"&DealPlatformType&" Platform'!F90:F98"),1)))+VLOOKUP(Planner!DealBlock Size,INDIRECT("'"&DealPlatformType&" Platform'!F90:H98"),3)*(Planner!DealBlockSize-(VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPl atformType&" Platform'!F90:I98"),1))) I need it to only lookup a value now and not do do so much so for example someone inputs a size and it goes to another tab based on which platform they choose and then picks the price directly across from it so it someone picks 250 it should go to the right tab and pick 250 and then the price directly across from it? Can anyone help? Thank you |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
lookup confusion
It sounds to me as if you're looking for a very simple form of the VLOOKUP()
function. Lets say on the sheet where you make the size selection and want to return the value from another sheet that: the size they select goes into column B and you want the looked up value to show up in column C. Meanwhile, over on a sheet/tab named "myTableSheet" you have information laid out with the sizes in column A and several columns of information and then the price is out in column F. This table goes from row 1 to row 1005 on that sheet. In C2 on the first sheet you would put this formula: =VLOOKUP(B2,'myTableSheet'!$A$1:$F$1005,6,FALSE) what this says is to get the value in B2 on this sheet and then look on the myTableSheet in column A (from rows 1 through 1005) and find the first value in that column that matches - and when you do find the match, then return the value in the 6th column of that table, and the entries in column A don't have to be in order. You can actually get Excel to do most of the work for you - again, using C2 and the other limits I mentioned, you could click in C2 and type =VLOOKUP( and then click in B2 on the sheet type a comma select the myTableSheet and all of the cells in the table type another comma type the 6 type another comma type False and type the closing ) and end it all with the [Enter] key. Now, if the value in B2 is not in the list in column A on the other sheet, you'll get a #N/A error as a result. You can hide that by wrapping the formula in an error trap like this: =IF(ISNA(VLOOKUP(B2,'myTableSheet'!$A$1:$F$1005,6, FALSE)),"No Match",(VLOOKUP(B2,'myTableSheet'!$A$1:$F$1005,6,F ALSE)) Hope this helps. Excel Help for the VLOOKUP() function should give you more advice also. "HS" wrote: Hi -- Can someone help me with this formula? =INDEX(INDIRECT("'"&DealPlatformType&" Platform'!I90:I98"),MAX(1,MATCH(Planner!DealBlockS ize,INDIRECT("'"&DealPlatformType&" Platform'!F90:F98"),1)))+VLOOKUP(Planner!DealBlock Size,INDIRECT("'"&DealPlatformType&" Platform'!F90:H98"),3)*(Planner!DealBlockSize-(VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPl atformType&" Platform'!F90:I98"),1))) I need it to only lookup a value now and not do do so much so for example someone inputs a size and it goes to another tab based on which platform they choose and then picks the price directly across from it so it someone picks 250 it should go to the right tab and pick 250 and then the price directly across from it? Can anyone help? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MDI Confusion | Excel Discussion (Misc queries) | |||
COUNTA Confusion.... | Excel Discussion (Misc queries) | |||
if statement confusion | Excel Discussion (Misc queries) | |||
Conditional Confusion | Excel Worksheet Functions | |||
Confusion..... | Excel Discussion (Misc queries) |