INDEX & MATCH: Reference a Table with name corresponding to value in cell

[FunsizedNerd]

This is the formula I currently have to reference a table and match the value in a specific cell to a column in the table (used in data validation)

=INDEX(M11OD, ,MATCH(B2,Direction_list2,0))

I want to know if it's possible to change the table name based on the value in another cell (say B1)
e.g.
=INDEX(B1&"OD", ,MATCH(Direction_Cell2019,Direction_list2,0))

and if B1 = M9 this would return
=INDEX(M9OD, ,MATCH(Direction_Cell2019,Direction_list2,0))

 

[James006]

Hello,

You could test following :

[COLOR=#333333]=INDEX(Indirect(B1&"OD"), ,MATCH(Direction_Cell2019,Direction_list2,0))[/COLOR]

Hope this will help

 

[FunsizedNerd]

Just results in an error when I put it into data validation, and gives a different result compared to the original formula even with the same values in each cell

 

[James006]

What about creating a dynamic named range ... and use this name in your data validation formula ...