Lookup/Match multiple variables

On one sheet I have a list of voters and their street addresses. On the next sheet is a list of streets and the corresponding voting precinct. The problem is some of the streets go through more than one voting precinct, which means there are multiple rows with the same street name. Also, since in some places the road is the boundary line between the precincts, houses on one side (odd-numbered addresses) will be in one precinct, while houses on the other side (even-numbered addresses) will be in another. If you take a look at the Streets sheet, it hopefully will be more clear. I need a formula (preferably no VBA) that will fill in the PRECINCT column of the Voters sheet based on the voter's street address.

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