Lookup a value when the range contains a high - low format
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
On Aug 7, 12:46 pm, rpalarea
wrote:
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
|