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