Lookup/Match multiple variables

Hi,

On your voters sheet, insert a new column E and populate with this formula

=IF(MOD(C2,2),"O","E")

copy down.

Then use in F2

=INDEX(Streets!$E$1:$E$1000,MATCH(1,(--(Streets!$A$1:$A$1000=D2)*(Streets!$B$1:$B$1000<=C2)*(Streets!$C$1:$C$1000>=C2)*(IF(Streets!$D$1:$D$1000="B",1,(Streets!$D$1:$D$1000=E2)))),0))

This is an array formula and must be confirmed with Ctrl-Shift-Enter. Adjust ranges to suit.

Then copy down.

see attached for working example.

A single cell non-array alternative might be:

E2:
=LOOKUP(2,1/((Streets!$A$2:$A$9=$D2)*ISNUMBER(SEARCH(Streets!$D$2:$D$9,IF(MOD($C2,2),"BO","BE")))*(Streets!$B$2:$B$9<=$C2)*(Streets!$C$2:$C$9>=$C2)),Streets!$E$2:$E$9)
copied down