Use a cell value to determine a table to then lookup

[MITchip]

I have 3 tables here, from top to bottom named Table1, Table2, Table3. In cell B2 I want the value of table column B, but I want the table the value is chosen from to change based on the value in cell A2. How do I write the index/match formula to account for A2 not being static?

 

[Faseeh]

@MITchip

Try this: =OFFSET($D$1,LOOKUP(A2,"TABLE"&{1,2,3},{3,6,9})-1,1)

 

[NARAYANK991]

Hi ,

Another way :

=INDIRECT(A2 & "[B]")

Narayan

 

[MITchip]

@Faseeh - That certainly answers the question I asked, and it's my fault but I should have been more specific. I could be adding many tables to my workbook, so I don't want to use any sort of relative reference...I think that @NARAYANK991 has the right response for my problem.

Thank you both for the help.