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

[NBVC]

Assuming your table is in A1:F7

and your inputs are in A14:B14, respectively as C and 1987, then formula:

=INDEX($B$2:$B$7,MATCH(1,IF(MOD(B14,2)=1,INDEX(($A$2:$A$7=A14)*($C$2:$C$7<=B14)*($D$2:$D$7>=B14),0),INDEX(($A$2:$A$7=A14)*($E$2:$E$7<=B14)*($F$2:$F$7>=B14),0)),0))

[ganick]

very prompt, fast, and correct.
Realy thank you!

[dleiman]

How would it be if only Street, StartNo, EndNo and need to retrieve Zip

Street Zip Start End
A 1426 701 1800
A 1428 1801 2300
A 1428 2301 2800
A 1429 2801 4100
B 1427 1 1300
B 1427 1301 1500
B 1430 1501 1700
C 1130 1101 1800
C 1261 1801 2700
C 1261 1801 2700