Get a row number of range where a value is between minimum and max

[Tetsuya Oguma]

Hi all,

This might be pretty a breeze for some. I am looking for ONE SINGLE formula
that returns a row number where a supplied value is within minimum (Column A)
and maximum (Column B) range.

For Example:
A B
1 1 10
2 15 20
3 30 40

With the supplied value of 2, I want the formula to return '1' as the number
'1' is between the range of number '1' and number '10' in row 1.

With the supplied value of 11, I want the formula to return BLANK as the
number is not in any range.

With the supplied value of 30, I want the formula to return '3' as the
number '30' is between the range of number '30' and number '40' in row 3.

I hope the above examples help you understand what I look for.

Thanks in advance.

 

[ShaneDevenshire]

Hi,

Here is one formula:

=IF(SUMPRODUCT((B6>=A$1:A$3)*(B6<=B$1:B$3)),MATCH(B6,A$1:A$3,1),"BLANK")

 

[Ashish Mathur]

Hi,

In another column, enter running numbers 1,2,3,4, etc. (say col. C)

Enter the following formula
=IF(ISERROR(SUMPRODUCT((A1:A3<=$A$5)*(B1:B3>=$A$5),C1:C3)),"",SUMPRODUCT((A1:A3<=$A$5)*(B1:B3>=$A$5),C1:C3))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com