Lookup a value when the range contains a high - low format

[Guest]

My data appears as follows:

A B
27000 - 27499 3
27500 - 27999 4

and so on. The data is arranged horizontally in two columns and runs for 181
rows including headers.

Column A are US zip codes in a beginning - ending range. Column B is a
corresponding value assigned to each range of zip codes. Zip code ranges are
sorted lowest to highest by starting value, but do not always incorporate a
complete range. For example, the ending range of one set can read 26899 while
the next range can have a beginning value for its set of 27000. This is
usually because the US postal system has reserved the missing numbers for
future use.

Is there a way to use LOOKUP or INDEX/MATCH to return the value in Column B
when given one entire 5-digit zip code?

TIA

Rich

 

[Pete_UK]

If the zip codes are always 5-digit and are already in sequence, then
insert a new column B and put this formula in B2 (assuming you have a
header row):

=1*LEFT(A2,5)

and copy down by double-clicking the fill handle (the small black
square in the bottom right corner of the cursor). Then you can access
the data with something like:

=VLOOKUP(cell,B$2:C$182,2)

where cell is where you put the code you are looking for.

Hope this helps.

Pete

 

[Guest]

Hope this helps.

Yes, perfectly. Thanks

If the zip codes are always 5-digit and are already in sequence, then
insert a new column B and put this formula in B2 (assuming you have a
header row):

=1*LEFT(A2,5)

and copy down by double-clicking the fill handle (the small black
square in the bottom right corner of the cursor). Then you can access
the data with something like:

=VLOOKUP(cell,B$2:C$182,2)

where cell is where you put the code you are looking for.

Hope this helps.

Pete

 

[Pete_UK]

Glad to be of help - thanks for feeding back.

Pete