Conditional Vlookup

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

Hi!

try this:

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

Biff

"RICKY" <RICKY@discussions.microsoft.com> wrote in message
news:83D21324-B752-4906-B2C9-CDB62F3BD5FE@microsoft.com...
> 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

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.