Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
Parce a number with decimals into separate columns JimR Excel Discussion (Misc queries) 3 November 22nd 06 09:04 AM
find closest match to a reference number in a row of numbers Nick Krill Excel Discussion (Misc queries) 4 December 21st 05 12:59 PM
Find similar numbers from two columns of numbers Dennis Andrews Excel Discussion (Misc queries) 1 November 30th 05 08:54 AM
How do you find duplicate values in excel- 2 columns of numbers rickmanz Excel Discussion (Misc queries) 1 December 16th 04 12:16 AM


All times are GMT +1. The time now is 11:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"