Finding row number from value which is in between ranges

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. • i want to know the row number where my input value falls on .based on the row number i cud retrieve other values of the row. Problem i got here is ..my input is not exact value which matches with Column B. it just a number which falls within the range of Column B and Column C. Hope this info wud be useful to move forward .thanks much Jan 29 '19 at 17:52

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")

• Excellent !!!!! Jan 30 '19 at 10:34
• Crisp and One Liner ... No words to praise you .. now my problem got solved . Jan 30 '19 at 11:42
• Happy to help, Vignesh. Thanks for the feedback. Jan 30 '19 at 17:19
• Vignesh, you've marked the answer given by @MARK MY WORDS as the correct answer here. Yet he has an up-score of zero, while I have an up-score of one. I'm finding it difficult to know which answers you are using, or when you still need help. If you mark an answer as the correct answer, it means that solution worked for you and you no longer need help. Jan 30 '19 at 17:29
• I am very much new to this forum and i do not know the rules of this game.. wil follow it up surely in the upcoming threads.. thanks erik Jan 31 '19 at 4:39

=IF(AND(B12 >= B4, B12 < C4), ROW(A4),
IF(AND(B12 >= B5, B12 < C5), ROW(A5),
IF(AND(B12 >= B6, B12 < C6), ROW(A6),
IF(AND(B12 >= B7, B12 < C7), ROW(A7),
IF(AND(B12 >= B8, B12 < C8), ROW(A8), ))))) • Thanks much sir. As an example i have given a table with 5 rows. say for example if i have 300 rows in a table .. how to go abt it ??? Jan 29 '19 at 18:45
• Any response to this query would be useful Jan 29 '19 at 19:42

=VLOOKUP(IFERROR(ARRAYFORMULA(VLOOKUP(D12,
{QUERY(IFERROR(B4:B, ), "select Col1", 0),
QUERY(QUERY(B4:B, "select *", 0), "select Col1", 0)}, 2)), ),
{ARRAYFORMULA(QUERY(B4:B8, "select *", 0)),
{ARRAYFORMULA(ROW(INDIRECT("A1:A"&
COUNTA(QUERY(QUERY(B4:B, "select *", 0), "select Col1", 0)))))}}, 2, 0)+3 note1: you don't need RANGE END column
note2: if your first range rule starts at 10th-row change ending +3 to +9
note3: if you have 300 ranges, change B4:B8 to B4:B303