1) I have 6 lookup tables named TABLE_1, TABLE_2 ..., TABLE_6.
2) User specifies the a lookup value in cell A1, and enters a number between 1 and 6 in cell B2
Here's what I want:
If the user inputs "1" in cell B1, then return the result from formula: VLOOKUP(A1,TABLE_1,2,FALSE)
If the user inputs "2" in cell B1, then return the result from formula: VLOOKUP(A1,TABLE_2,2,FALSE)
I tried setting up my formula as follows, but it doesn't work - Getting #REF! error.
=VLOOKUP(A1,INDIRECT(CHOOSE(B1,"TABLE_1", "TABLE_2", "TABLE_3", "TABLE_4", "TABLE_5", "TABLE_6")),2,FALSE)
Apparently, INDIRECT can handle literal cell references (e.g. "A1:B10"), but not Named Ranges (e.g., "TABLE_5").
Can anyone suggest an alternative - preferably without the use of intermediate cells/computation?