# Lookup number of values within a range

#### sanwar1

##### New Member
Hello all, I have a task that I need help with. Let's say on one cell I have the value of "267.0-268.3". And I have a master list that might be like something below:

267.0
267.1
267.3
267.8
268.9

I need a formula that will look at "267.0-268.3" and return the number of codes on master list that fall between those two numbers. So in this particular example, it will return the value of "4".

Any help is appreciated.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

#### RonB1111

##### Well-known Member
With codes in A1:A5, and cell A9 containing 267.0-268.3, use:
Code:
``=SUMPRODUCT(--(A1:A5>=--LEFT(A9,FIND("-",A9)-1)),--(A1:A5<=--RIGHT(A9,LEN(A9)-FIND("-",A9))))``

#### sanwar1

##### New Member
Hello Ron,

Thank you for the response. I tried this, but it doesn't seem to work. It's returning a value of 0.

#### RonB1111

##### Well-known Member
It works on my spreadsheet. Would you post the data the way it's set-up in your worksheet. Do you have the data in cells A1 thru A5. Exactly how is the 267.0-268.3 entered in A9?

#### sanwar1

##### New Member
Okay, I think my issue is the formatting of data in my original table. I'll clean that up and try again. I just did what you suggested in a new sheet and it worked fine. Thank you so much for the help. That was exactly what I was looking for.

#### RonB1111

##### Well-known Member
You're welcome. Thank you for providing feedback.

Replies
2
Views
95
Replies
3
Views
203
Replies
1
Views
186
Replies
2
Views
176
Replies
2
Views
96

1,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

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