Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Vlookup
Please review following formula,
=IF(AD2="B10x5x5",VLOOKUP(AP2,B10X5X5,2),IF(AD2="B 10x10x5", VLOOKUP(AP2,B10X10X5,2),"Your Choice")) Depends on cell AD2, if is "B10x5x5" then Vlookup use table B10X5X5, or if AD2 = "B10X10X5" then Vlookup use table "B10X10X5" Then I trying to figure out a more general formula so I can have more table to choose from, by using AD2 as a table Reference cell, but none of my trying work, I Use Trim(AD2), TEXT(AD2,"##########"), proper(AD2)... Is anyone have similar experience and can help me on this? Thank a lot and rewrite formula as |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Vlookup
Hi!
try this: =IF(ISNUMBER(MATCH(AD2,{"B10x5x5","B10x10x5"},0)), VLOOKUP(AP2,INDIRECT(AD2),2),"Your Choice") Biff "RICKY" wrote in message ... Please review following formula, =IF(AD2="B10x5x5",VLOOKUP(AP2,B10X5X5,2),IF(AD2="B 10x10x5", VLOOKUP(AP2,B10X10X5,2),"Your Choice")) Depends on cell AD2, if is "B10x5x5" then Vlookup use table B10X5X5, or if AD2 = "B10X10X5" then Vlookup use table "B10X10X5" Then I trying to figure out a more general formula so I can have more table to choose from, by using AD2 as a table Reference cell, but none of my trying work, I Use Trim(AD2), TEXT(AD2,"##########"), proper(AD2)... Is anyone have similar experience and can help me on this? Thank a lot and rewrite formula as |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Vlookup
Biff wrote...
.... =IF(ISNUMBER(MATCH(AD2,{"B10x5x5","B10x10x5"},0)) , VLOOKUP(AP2,INDIRECT(AD2),2),"Your Choice") .... You could shorten the test. =IF(OR(AD2={"B10x5x5","B10x10x5"}), VLOOKUP(AP2,INDIRECT(AD2),2),"Your Choice") If there were a lot of ranges to choose from and their names were listed in another range named List, you could use another range, perhaps named Trap, to handle no match found. The formula above could be replaced by =VLOOKUP(AP2,INDIRECT(IF(COUNT(MATCH(AD2,List,0)), AD2,"Trap")),2) and Trap would be 1 row by 2 columns with the first column containing the formula =AP2 and the second column containing the string Your Choice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
Conditional Sum on VLOOKUP Returned Data | Excel Worksheet Functions | |||
How do I or can I put a vlookup or hlookup in conditional formatti | Excel Worksheet Functions | |||
Conditional formatting on cells with a VLOOKUP formula in them | Excel Discussion (Misc queries) | |||
vlookup & conditional formatting | Excel Worksheet Functions |