is number between to other numbers.

[hans]

In collumn a i have the zipcode a city starts with (4 numbers)
In collumn b i have the ziptcode a city ends with (4 numbers)
In collumn c i have the citynames etc etc
Is there a way to check is a zipcode entert belong to a city?
there are zipcodes not used?

Greetings Hans

 

[Tom Ogilvy]

Dim zip as String, z1 as Long, cell as Range
zip = "12345"
z1 = clng(Left(zip,4))
for each cell in Range(A2:A500")
if z1 >= cell and z1 <= cell.offset(0,1) then
msgbox "City is " & cell.offset(0,2)
exit for
end if
Next

 

[Bob Phillips]

=IF(ISNA(MATCH(1,(K1>=A1:A10)*(K1<=B1:B10),0)),"No
match",INDEX(C1:C10,MATCH(1,(K1>=A1:A10)*(K1<=B1:B10),0)))

where K1 is the zipcode entered

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[hans]

Thank.
Only Bob's solution has an error.

Greetings Hans

 

[Bob Phillips]

Probably NG wrap.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)