0

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

Improve this question
1
  • 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
    – Vignesh Rams
    Jan 29, 2019 at 17:52

3 Answers 3

Reset to default
1

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

Improve this answer
7
  • Excellent !!!!!
    – Vignesh Rams
    Jan 30, 2019 at 10:34
  • Crisp and One Liner ... No words to praise you .. now my problem got solved .
    – Vignesh Rams
    Jan 30, 2019 at 11:42
  • Happy to help, Vignesh. Thanks for the feedback.
    – Erik Tyler
    Jan 30, 2019 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.
    – Erik Tyler
    Jan 30, 2019 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
    – Vignesh Rams
    Jan 31, 2019 at 4:39
0 

=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), )))))

Improve this answer
2
  • 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 ???
    – Vignesh Rams
    Jan 29, 2019 at 18:45
  • Any response to this query would be useful
    – Vignesh Rams
    Jan 29, 2019 at 19:42
0 

=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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.