Range value lookup

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!
LVL 13
Shanan212Asked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
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
 
NBVCCommented:
Or actually simpler:

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

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

From novice to tech pro — start learning today.