# looking up values in different lists

#### rspizman

##### New Member
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

Thanks!

Thankyou - works perfectly

Replies
5
Views
348
Replies
1
Views
98
Replies
2
Views
197
Replies
1
Views
229
Replies
15
Views
302

1,203,762
Messages
6,057,218
Members
444,914
Latest member
Mamun12345

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back