Index Match help for multiple criteria match in a range of cells

Hey All,

Hoping someone can help i have attached a file to work on, what I am looking for is for on the 'Operator Check' Tab in cell C8 to return the next operator due on on that date on the next shift, with the changes due to Covid people do not know if someone is coming in after them and we want to be able to let them know if they should shut down machinery to save electricity etc.

I am trying to do an index match with the following criteria.

on the Operator Check Tab

Match Date in cell C3 to column C:C in the Mainshop Schedule
Match Work Center in C4 to row 1 in the Mainshop Schedule (or G1:AZ1)
Match Next Shift in C6 to column D:D in the Mainshop Schedule

So in the attached it should return 'Frank' as that is the match found in cell H12 on the Mainshop Schedule

i have tried the following formula to no avail

Formula:

Please Login or Register  to view this content.

Any and all help much appreciated

Try

=INDEX('Mainshop Schedule'!G2:AZ70,MATCH(1,('Mainshop Schedule'!C2:C70=C3)*(C6='Mainshop Schedule'!D2:D70),0),MATCH(C4,'Mainshop Schedule'!G1:AZ1,0))

Enter with Ctrl+Shift+Enter

Operator Check

C8=INDEX(INDEX('Mainshop Schedule'!B2:AZ70,,MATCH('Operator Check'!C4,'Mainshop Schedule'!B1:AZ1,0)),MATCH(1,('Mainshop Schedule'!C2:C70='Operator Check'!C3)*('Mainshop Schedule'!$D$2:$D$70='Operator Check'!C6),0))

Control+Shift+Enter

Hey Guys,

Many thanks for this both formulas worked!!