Lookup based on separate columns for different ranges and on odd or even entry

[ganick]

Hi,

I have the following problem (given the data below):
Streets Sheet
StreetName ZipCode MinOddNo MaxOddNo MinEvenNo MaxEvenNo
A 111 1 9999 0 0
A 222 0 0 2 9998
B 333 1 9999 2 9998
C 444 1 1001 2 2010
C 555 1003 2015 2012 4000
C 666 2017 9999 4002 9998

To explain the data, must say that the first row means that the odd numbering of A
street, ONLY, belongs to 111 zip code. Whilst second row meaning is that the even numbering of A street belongs to 222 zip code.

I want to pick up the correct ZipCode derived from user input (lets say):
UserSheet
StreetNa StreetNo ZipCode
C 1987 [555]

Using
=OFFSET(StreetStart;MATCH(A2;StreetColumn;0)-1;1;COUNTIF(StreetColumn;A2);1)
on the zip code cell on the user sheet returns the correct value for the B street (no duplicates). Doesn't work for duplicates because it returns multiple values (COUNTIF that returns the height for the OFFSET is more than 1).

[where "StreetStart" is named object for StreetName (header cell); the names of the streets follows downwards (Street Sheet)
"StreetColumn" is named object for the whole column containing street names
(Street Sheet)]

Don't know if this is the right approach, but what I want is a "range lookup" given a street and number to derive the zip code, using only functions if possible.

Thx