Return line number if value is between two values ​​in excel

Question

If I have a lookup table with a range of numbers (Min and Max) in two columns. On the second sheet it contains numbers (1-100). And I would like to find the row number from the lookup table. How can i do

Answer 1

Consider:

=INDEX(A$2:A$11,MATCH(D2,B$2:B$11,1)) 

Answer 2

Assuming Band is in A-column, Min is in B-column, etc. and the headers on the first line, put this in F2 and drag as needed:

 {=INDEX($A$2:$A$11,MATCH(1,(E2<=$C$2:$C$11)*(E2>=$B$2:$B$11),0))} 

Note that you do not need to insert {} , this means that it is an array formula, so you need to enter this formula with Ctrl + Shift + Enter instead of just Enter .

If you have data in different ranges, you will have to adjust the ranges accordingly.

If you're interested in a string instead of a group, wrap a ROW arround formula, so:

 {=ROW(INDEX($A$2:$A$11,MATCH(1,(E2<=$C$2:$C$11)*(E2>=$B$2:$B$11),0)))} 

Again with Ctrl + Shift + Enter .