An Interesting Function Challenge

[Gustavo]

Hello All,
I have the following challenge that I can not solve:

COLUMNS:
A1 B1 C1 D1 E1(MyZipCode)
BEGZIP ENDZIP State ST 01002 (what state?)
00600 00999 PUERTO RICO PR
01000 02799 MASSACHUSETTS MA
02800 02999 RHODE ISLAND RI
03000 03899 NEW HAMPSHIRE NH
03900 04999 MAINE ME
05000 05999 VERMONT VT
06000 06999 CONNECTICUT CT
07000 08999 NEW JERSEY NJ

column A: BegZip
column B: EndZip
column C: State
column D: StateCode

I need to enter a unique zip code number and find the
state from the list.
I think is like a vlookup that reads the ranges from
begzip to endzip for every row. Is this possible at all?
I work with Excel 2000
Thanks in advance
Gustavo

 

[Jason Morin]

=VLOOKUP(E1,A,4)

HTH
Jason
Atlanta, GA

 

[Gustavo]

Thanks for the suggestion Jason...but that won't work
because is never going to find the zip code exact value.
I am looking for a function to find the value between the
begzip and endzip range in every row.
I probably did not explain myself clearly
Gustavo

 

[Guest]

Actually Jason's idea may work for you - at least based on the sample data in your post. Since each BEGZIP is one more than the previous ENDZIP, you probably can ignore the ENDZIP. The final parameter (which you can skip) in vlookup tells Excel whether or not to use exact matches. The default (TRUE) does NOT use exact matches, but does require that your values be sorted.

Art

 

[Gustavo]

Actually it does work great. I had an extra "," in the
formula that was causing the error. I apologize to Jason
and thank you both
Gustavo

-----Original Message-----
Actually Jason's idea may work for you - at least based

on the sample data in your post. Since each BEGZIP is
one more than the previous ENDZIP, you probably can
ignore the ENDZIP. The final parameter (which you can
skip) in vlookup tells Excel whether or not to use exact
matches. The default (TRUE) does NOT use exact matches,
but does require that your values be sorted.