Match lookup array more than 1 column

Hello, I'm trying to find the first cell value that matches 2 conditions:
1st condition is the Registered day
2nd condition is the desired professor for the shift

When both conditions are met, the first person to match the conditions goes on top of the corresponding shift with professor.

Here is what my sheet1 looks like:
1.png

And here is what my sheet 2 should looks like:
2.png

I used
=INDEX(Sheet1!B2:B51, MATCH(1, (Sheet1!D2:D51=Sheet2!A2)*(Sheet1!I2:I51=Sheet2!B6),0), 1)
and works fine for Person 1's Monday AM schedule

but this does not go through columns E to H or J to M so I edited to
=INDEX(Sheet1!B2:B51, MATCH(1, (Sheet1!D2:H51=Sheet2!A2)*(Sheet1!I2:M51=Sheet2!B6),0), 1)
and I'm getting #N/A error.

I figured that lookup array only sweeps 1 column but would there be any alternative way I can make this work?



ps. I couldn't attach the excel file in the previous post so I've deleted the old post to repost

Originally Posted by tim201110

=IFERROR(INDEX(Sheet1!$B$1:$B$51, AGGREGATE(15,6, ROW(Sheet1!$B$2:$B$51)/ISNUMBER(SEARCH(A$2,Sheet1!$D$2:$D$51))/(Sheet1!$I$2:$I$51=B$5)/ISNUMBER(SEARCH(A$4,Sheet1!$D$2:$D$51)),ROWS($A$6:$A6))), "")

Thanks for the reply!
I think it's probably me that cannot put this formula into application but,
would there be any way to get this formula to sweep through D:H and I:M?

For example, Person 4 can have Wednesday PM with corresponding Supervisor as a first shift in column D
but our current formula only thinks column D is for Monday AM.

Thanks again in advance



UPDATE

Nvm, solved with this fuction

=IFERROR(INDEX(Sheet1!$B$1:$B$51, AGGREGATE(15,6, ROW(Sheet1!$B$2:$B$51)/ISNUMBER(SEARCH($A$3,Sheet1!$D$2:$H$51))/(Sheet1!$I$2:$M$51=A$5)/ISNUMBER(SEARCH(A$4,Sheet1!$D$2:$H$51)),ROWS(A$6:A6))), "")

Now, I'm trying to figure out how each person gets its cell filled with corresponding color of level
Ex. Level 3 person gets purple color, Level 1 gets yellow, so on.