Solved

Range value lookup

Posted on 2013-11-19
2
237 Views
Last Modified: 2013-11-19
Hi,

I have a range such as this

ID start          ID End
1200              1299
1300              1399
1400              1499
1500              1599
1600              1699

Now I have an ID say 1378

How do I lookup this id in the range above using formulas?

Thanks!
0
Comment
Question by:Shanan212
  • 2
2 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39660576
Assuming that data is in A2:B6, and your lookup value of 1378 is in E2, this will tell you the row number within that range that the number falls in:

=MATCH(1,INDEX(($A$2:$A$6<=E2)*($B$2:$B$6>=E2),0),0)

You can wrap and INDEX() around to get corresponding date in same row.

e.g.

=INDEX($C$2:$C$6,MATCH(1,INDEX(($A$2:$A$6<=E2)*($B$2:$B$6>=E2),0),0))
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39660583
Or actually simpler:

=MATCH(E2,$A$2:$A$6)

since they are listed in ascending order and no gaps in ranges.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question