Return Data from Table using Cell Reference

MartinS

Active Member
Joined
Jun 17, 2003
Messages
420
Probably an easy one, but the obvious doesn't work.
I have a column of references to an Excel table, where they are being looked up using Index/Match, but the actual header is stored alongside the formula.
Rather than use INDEX/MATCH, is it possible to do something like =INDEX(Table1[$c32], mem_index) where c32 contains the text, say, DoB which is one of the table column headings? It doesn't seem to allow this syntax, but as I have over 600 formula requiring this syntax, I was trying to avoid using so many INDEX/MATCH formulae?
Thanks
Martin
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,161
Office Version
  1. 365
Platform
  1. Windows
You can use indirect like
Excel Formula:
=INDEX(INDIRECT("Table1["&$c32&"]"), mem_index)
although indirect is slow & volatile.
 
Solution

MartinS

Active Member
Joined
Jun 17, 2003
Messages
420
Thanks, I didn't think of it in that format, but appreciate your comment about the speed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,161
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,708
Messages
5,654,858
Members
418,156
Latest member
LegendK91

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