How to use Index and Match based on multiple criteria using multiple worksheets

Good day!

I need help on using index and match formula with multiple criteria across multiple worksheets.

Excel File Scenario:
8 worksheets containing yearly data on training attendance of various employees
1 master worksheet containing the master list of employees

Objective
I would like to search for the "PROGRAM DATE START" information across the 8 worksheets and match it with the "NAME" and "Program Title" found on the "Masterlist" worksheet.

I've attached here my test file.

Thanks in advance!

Originally Posted by FDibbins

Hi, welcome to the forum

This can most certainly be done, but have you considered keeping ALL base data on 1 sheet? All you would need to add would be the date, and the summary would be FAR simpler

Thank you for your response. Yes, I thought of having all base data in one worksheet but it will use up too many rows. The data I included in the test file is just a small portion of the actual data.

I also segregated the data per year because there will be other users who are not too familiar with excel. It will be easier for them to navigate the file with a per year label.

See attached:

Named range "SheetList" in column H

in Column F: "helper" to find Sheet associated with Name.

=IFERROR(INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$C$2:$C$1000"),$A2)>0),0)),"")

Enter with Ctrl+Shift+Enter

in B2

=IFERROR(INDEX(INDIRECT("'" & $F2 &"'!$B2:$B200"),MATCH(1,(INDIRECT("'" &$F2 &"'!$C2:$C200")=$A2)*(INDIRECT("'" &$F2 &"'!$A2:$A200")=B$1),0)),"")

Enter with Ctrl+Shift+Enter

Copy across and down

Originally Posted by JohnTopley

See attached:

Named range "SheetList" in column H

in Column F: "helper" to find Sheet associated with Name.

=IFERROR(INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$C$2:$C$1000"),$A2)>0),0)),"")

Enter with Ctrl+Shift+Enter

in B2

=IFERROR(INDEX(INDIRECT("'" & $F2 &"'!$B2:$B200"),MATCH(1,(INDIRECT("'" &$F2 &"'!$C2:$C200")=$A2)*(INDIRECT("'" &$F2 &"'!$A2:$A200")=B$1),0)),"")

Enter with Ctrl+Shift+Enter

Copy across and down

Thank you! It worked perfectly. I appreciate the help.