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

T

TXDalessandros

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
 
B

Bernard Liengme

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top