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 |
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 |
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. |
All times are GMT +1. The time now is 11:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com