• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Enter row numbe if cell value falls within range of numbers in different columns

In column A1 - A6 I have numbers like:
3.75
7
9
2.35
5
11

Columns
B1-B4 & C1-C4
1 4
5 8.5
9 10
11 14

Column D should return the row number that each number in column A falls between in columns B - C

Answers
D1 = 1
D2 = 2
D3 = 3
D4 = 1
D5 = 2
D6 = 4
 
The above said works fine and if you do not want to hamper with Array formulas, then go for the traditional IF..else construct way of doing this..

=IF(AND($A1>=$B$1,$A1<=$C$1),ROW(B$1),IF(AND($A1>=$B$2,$A1<=$C$2),ROW(B$2),IF(AND($A1>=$B$3,$A1<=$C$3),ROW(B$3),IF(AND($A1>=$B$4,$A1<=$C$4),ROW(B$4),"N/A"))))
 
Hi ,

A brief recap of the syntax for the MATCH function :

First parameter : the value which is to be matched

Second : the range over which the match value is to be looked for

Third : whether it will be an exact match or not ; if not , whether the value to be returned from the range is a lesser value or a greater value than the match value.

In this case , the match value is 1.

The range is an array of values , constructed from the two operations :

(A1>=$B$1:$B$4)

and

(A1<=$C$1:$C$4)

Since each of the above operations will result in an array of TRUE / FALSE results , the multiplication of two Boolean values will result in a numeric value , which will be either 0 or 1.

Hence the usage of 1 for the match value. What this will return is the position where both of the above conditions are satisfied ; the conditions are :

The value in column A in cell A1 is greater than or equal to any value in column B , and at the same time , it is less than or equal to the corresponding value in column C i.e. the value in A1 is between the values in columns B and C.

Narayan
 
Back
Top