Excel: Index/ match across multiple tabs using headings from a row to search tab

Question

I am using the index/ match function to pickup data from different tabs to draw the data into a summary tab, however I have a data set with lots of tabs and would like to automatically search the tab based on the heading in the summary tab. So looking at the below example data set, instead of manually typing the tab name in each column (eg, “Data1”, “Data2”, etc), I would like it to look up the tab name in row 1 so I can drag the formula across.

Answer 1

Hi,

In cell B3, enter this formula and drag to the right.

=index(indirect("'"&B$1&"'!B:B),match($A3,indirect("'"&B$1&"'!A:A),0))

Hope this helps.