CODEWITHSUNDEEP
excelexcel-formula
Jfang
Jfang

Reputation: 380

Return the row number if value is between two values in excel

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

enter image description here

Upvotes: 11

Views: 57617

Answers (3)

renevali
renevali

Reputation: 1

You can use index match.

  • Give the lookup table a name, for example "tbl"
  • Lets say numbers are in column G

Then you can use this formula: =INDEX(tbl[band];MATCH(1;(G11>=tbl[min]) * (G11 < tbl[max]);0))

Tables Formula

Reference:https://exceljet.net/formula/index-and-match-with-multiple-criteria

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Consider:

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

enter image description here

Upvotes: 6

MGP
MGP

Reputation: 2551

Assuming that Band is in the A-Column, Min in the B-Column and so on and the headers in the first row, put this in F2 and drag down as necessary:

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

Please note that you don't have to put in the {}, this indicates, that this is a array formula, so you have to enter this formula with Ctrl+Shift+Enter instead of just Enter.

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

If you are interested in the row instead of the Band, then wrap ROW arround the 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.

Upvotes: 5

Related Questions