Conditional Vlookup

R

RICKY

Please review following formula,

=IF(AD2="B10x5x5",VLOOKUP(AP2,B10X5X5,2),IF(AD2="B10x10x5",
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
 
B

Biff

Hi!

try this:

=IF(ISNUMBER(MATCH(AD2,{"B10x5x5","B10x10x5"},0)),VLOOKUP(AP2,INDIRECT(AD2),2),"Your
Choice")

Biff
 
H

Harlan Grove

Biff wrote...
....
=IF(ISNUMBER(MATCH(AD2,{"B10x5x5","B10x10x5"},0)),
VLOOKUP(AP2,INDIRECT(AD2),2),"Your Choice")
Click to expand...
....

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.
 
Top