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

FunsizedNerd

New Member
Joined
Mar 20, 2019
Messages
17
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))
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test following :

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

Hope this will help
 

FunsizedNerd

New Member
Joined
Mar 20, 2019
Messages
17
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
What about creating a dynamic named range ... and use this name in your data validation formula ...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top