find a number between numbers in two separate columns
 

I am trying to develop a routing guide to find freight rates for my company
and the trucking companies have provided zip codes in ranges. If I have zip
code 44100 in cell A1, zip 44199 in B1, 44200 in A2 and 44299 in B2, and
different rates for those zips across columns C,D, E, and F, how do I tell
Excel to find zip 44150? There is no specific 44150 zip. However, it would
be found in the range between 44100 and 44199? The illustration below might
make it more clear:

44100 44199 Find 44150
44200 44299
44300 44399

find a number between numbers in two separate columns
 

Try something like this:

With this sample data in A1:D4

ZipCode Rate1 Rate2 Rate3
44100 $100 $200 $300
44200 $400 $500 $600
44300 $700 $800 $900

And
F1: 44150
F2: Rate2

Then...this formula finds the ZipCode in Col_A (using an approximate match
in the VLOOKUP) returns the rate associated with the value in F2
G2: =VLOOKUP(F1,$A$2:$D$4,MATCH(F2,$A$1:$D$1,0),1)

In that example, G2 returns $200

Note1: To ensure that leading zeros will display, the ZipCode values in
Col_A and F2 are formatted as TEXT.
<format<cells<number tabCategory: Text

Note2: The 4th argument in the VLOOKUP, the 1, indicates that an approximate
match will be used. A zero there would engage an exact match.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"confused about ranges" wrote:

I am trying to develop a routing guide to find freight rates for my company
and the trucking companies have provided zip codes in ranges. If I have zip
code 44100 in cell A1, zip 44199 in B1, 44200 in A2 and 44299 in B2, and
different rates for those zips across columns C,D, E, and F, how do I tell
Excel to find zip 44150? There is no specific 44150 zip. However, it would
be found in the range between 44100 and 44199? The illustration below might
make it more clear:

44100 44199 Find 44150
44200 44299
44300 44399

find a number between numbers in two separate columns
 

Just use the first column of numbers and then look in the help index for
LOOKUP. Answer will be 44100

--
Don Guillett
SalesAid Software

"confused about ranges" <confused about
wrote in message ...
I am trying to develop a routing guide to find freight rates for my company
and the trucking companies have provided zip codes in ranges. If I have
zip
code 44100 in cell A1, zip 44199 in B1, 44200 in A2 and 44299 in B2, and
different rates for those zips across columns C,D, E, and F, how do I
tell
Excel to find zip 44150? There is no specific 44150 zip. However, it
would
be found in the range between 44100 and 44199? The illustration below
might
make it more clear:

44100 44199 Find 44150
44200 44299
44300 44399