Google Sheets – Find Row Number from Value Between Ranges

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I have attached a picture containing two column B and C. If my input to this table is 2.3, it should return me row number where my input value is greater than or equal to B column value and less than C column value.

  • Manually for 2.3 it should return the first-row number of the table.
  • Manually for 5.75 it should return the fourth-row number of the table.

enter image description here

Best Answer

Vignesh, having two columns is unnecessary (and actually contradictory), since the "next row" of any row in your first column is the upper limit of the preceding row, and since, as you have it now, some values appear on two rows (e.g., 3.5 is currently the upper limit of row 1 and the lower limit of row 2).

You only need your first column.

If that column's data started in B5, and if the value you want to match were typed into B1, the following formula (placed anywhere other than underneath the data column) would give you the row you want:

=IFERROR(MATCH(B1,B5:B,1),"UNDER RANGE")

Related Topic