Index Formula pulling through a list with two or more conditions

You can try to use this one modified to you actual data:
=IFERROR(INDEX($A$3:$A$17, SMALL(IF(ISNUMBER((SEARCH($H$3, $B$3:$D$17))*(SEARCH($H$1, $B$2:$D$2))), ROW($A$3:$A$17)-MIN(ROW($A$3:$A$17))+1, ""), ROW(A1))),"")
Array entered.

Try

INDEX('Sheet1'!$B:$B,SMALL(IF(('Sheet1''!$G:$G='sheet2'!$Y$2)*('Sheet1''!$H:$H='sheet2'!$Z$2),ROW('sheet1'!$F:$F),""),ROWS('sheet2'!$1:1))),"")

Highlighted is your second condition: you can add more as required using "*" which acts as an AND condition.

OR conditions use "+"

Thanks for both the responses, they weren't working for what I wanted as both conditions were in the same row, so I wanted it to pull into the list if it was either one or the other.
But, if I use the second formula another way it works brilliantly.

Thanks for the help