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

--
Thanks,
Shane Devenshire


"Tetsuya Oguma" wrote:

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.