looking up values in different lists

[rspizman]

I need to have a way to look up a number and determine if it is between several different number ranges:

For example
Start End
5298 6144
19801 20055
27247 27999
29555 29899

I want to be able to key a value in a cell and determine if the value is between any of the ranges (>= start or <= end). There will be between 10 and 20 separate number ranges.

I don't need anything other than a TRUE or FALSE answer, e.g.:

6140 = TRUE
6145 = FALSE

 

[IML]

assuming your table in A2:B5, and your number to text in D2

=(SUMPRODUCT(--($A$2:$A$5<=D2),--($B$2:$B$5>=D2)))>0

 

[rspizman]

Thanks!

Thankyou - works perfectly