lookup from other sheet based on data in current sheet

[td3201]

I am trying to pull in data into a field based upon the values in other fields. Here's a link to a file that shows this:
Book2.xlsx

For example, I want to pull in their blocking assignment located in 'wristband - assignments'!F:F and place it in 'wristband - B'!B4 based upon the series name in 'wristband - B'!A3 as well as the direction in 'wristband - B'!A4.

For example, I'm the series is Counter and the direction of the play is left, I want to pull in their blocking assignment which has this information in the 'wristband - assignments' sheet.

Thanks for any help!

 

[mart37]

You can use a matrix-formula (arrayformula) with INDEX() and MATCH()
or
you can use VLOOKUP()
because VLOOKUP() can search in one column you need to create an new column with the formula: =B2&"|"&C2
in the VLOOKUP() you can enter as searchvalue: A3&"|"&A4
as searchcolumn the new column