Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup a value when the range contains a high - low format
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup a value when the range contains a high - low format
Hope this helps.
Yes, perfectly. Thanks "Pete_UK" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup a value when the range contains a high - low format
Glad to be of help - thanks for feeding back.
Pete On Aug 7, 1:38 pm, rpalarea wrote: Hope this helps. Yes, perfectly. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highest high for a range | Excel Worksheet Functions | |||
how do i format a cell based on format of a range of cells? | Excel Discussion (Misc queries) | |||
Lookup in a range. | Excel Discussion (Misc queries) | |||
Describe high/low range with CountIf (ex. count if >90 and <100 | Excel Worksheet Functions | |||
Describe high/low range with CountIf (ex. count if >90 and <100 | Excel Worksheet Functions |