Finding the Value of a range in another column and showing resulti

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TXDalessandros - 20 May 2008 21:48 GMT

If I have a number that I need to know if it falls between a range of two
columns and then I need to show other data if it does fall betweeen that
range what function would I use to do this
Example

Here is the sheet with the ranges 1st 2 columns, the next three columns is
the data I need to supply if I find that the number on another worksheet
falls in between this range
LblRange    End Range    ShpmntWght    Acct#ToBill    Srvc
15155743934    15155843835    1.00    797555855    SDS
30000000026    30049999921    3.00    854352043    EXP
30050000024    30050099925    1.00    795119189    EXP
30050100021    30050699921    1.00    141040378    SDS
30050700024    30051499925    35.00    790219059    GDS
30051500021    30052299922    5.00    853837183    GDS
30052300025    30053299920    2.00    137934050    GDS
30053300023    30098299921    2.00    206892098    NAS

Example sheet of numbers that I need to know if they fall in any of the
above ranges and then show the last 3 columns above if it is true
30192883523
30291659823
30322972024
30456649926
30490193322
30638779920
30803322524
30803322620
30803322723
30861539321

Reply to this Message

Bernard Liengme - 20 May 2008 23:01 GMT

Your Lb range in A, your end range in B , etc
The item to look up in G1
=IF(G1<=INDEX(B:B,MATCH($G1,A:A,1)),INDEX(C:C,MATCH($G1,A:A,1)),"")
If G1 is between two A and B values this gives you the value form C
otherwise a blank

Or =IF(G1<=INDEX(B:B,MATCH($G1,A:A,1)),"X", "") and filter the resulting
list
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> If I have a number that I need to know if it falls between a range of two
> columns and then I need to show other data if it does fall betweeen that
[quoted text clipped - 26 lines]
> 30803322723
> 30861539321

Reply to this Message