Index Match Across Tabs

**tryingtoexcelatexcel** · 06-18-2015, 12:14 AM

Index Match Across Tabs.xlsx

Hi All,

I was wondering if someone could help with a formula that searches across tabs. I have an excel sheet that I would like to use the index match function or some search & return function but be able to search across the excel sheets instead of writing an index match formula for each column.

Thank you very much in advance

Joseph

**FlameRetired** · 06-18-2015, 01:41 AM

I put a drop down box in B2 to select which year to return. I changed the header in I2 from Whos to Who in order to match the sheet tab.

I put some figures in Me Year 2013 to test the formula. I changed some figures in Who Year 2014 to test the formula.

It is

Formula:

=IFERROR(INDEX(INDIRECT("'"&E$2&"'"&"!E6:K27"),MATCH(Sheet1!$B5,INDIRECT("'"&E$2&"'"&"!B6:B27"),0),MATCH($B$4,INDIRECT("'"&E$2&"'"&"!E2:K2"),0)+2),"")

put in E5:I26.

The file is attached.