# More than, less than, equal to, cell checking formula question

#### PeterCollins

##### New Member
I’m trying to query a value in a row/column and depending on the value provide an answer in another row/column. The data I’m checking against is a number sequence which needs to be queried to see if it’s between a series of more than/less than/equal to statements. Ive done these before for single number ranges, but this time im checking against 7 number ranges to return a result and its not working for me.

So here’s the formula im trying to write:

In J2 there’s a number, for example: 321.4427409

I need to see which of the statements below it falls into and return the text answer indicated, into cell M2.

>0 or <= 359.9 returns answer: One
>360 or <= 719.9 returns answer: Two
>720 or <= 1079.9 returns answer: Three
>1080 or <= 1439.9 returns answer: Four
>1440 or <=1799.9 returns answer: Five
>1800 or <= 2159.9 returns answer: Six
>2160 or <= 2520 returns answer: Seven

If anyone can help with the formula Id be ever so grateful. Thank you. Ive got 7 workbooks to check and each has about 46,000 rows of numbers to check.

Last edited:

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Eric W

##### MrExcel MVP
Do you mean "and" instead of "or" in your condition list? If so, try:

=LOOKUP(J2,{0,360,720,1080,1440,1800,2160,2520},{"One","Two","Three","Four","Five","Six","Seven","Other"})

#### Fluff

##### MrExcel MVP, Moderator
=LOOKUP(J2,{0,360,720,1080,1440,1800,2160},{1,2,3,4,5,6,7})

#### Eric W

##### MrExcel MVP
If you don't want the number actually spelled out, you can do something as simple as:

=INT(J2/360)+1

#### DataBlake

##### Well-known Member

>0 or <= 359.9 returns answer: One
>360 or <= 719.9 returns answer: Two
>720 or <= 1079.9 returns answer: Three
>1080 or <= 1439.9 returns answer: Four
>1440 or <=1799.9 returns answer: Five
>1800 or <= 2159.9 returns answer: Six
>2160 or <= 2520 returns answer: Seven

will this work?
Code:
``=IF(AND(J2>0,J2<=359.9),"One",IF(AND(J2>359.9,J2<=719.9),"Two",IF(AND(J2>719.9,J2<=1079.9),"Three",IF(AND(J2>1079.9,J2<=1439.9),"Four",IF(AND(J2>1439.9,J2<=1799.9),"Five",IF(AND(J2>1799.9,J2<=2159.9),"Six",IF(AND(J2>2159.9,J2<=2520),"Seven","error")))))))``

#### PeterCollins

##### New Member
Excellent stuff Fluff and Eric. totally sorted. I was using IF statements and not enough brackets in what I was attaempting. but whatever, yours just sorted it in seconds. I just might get home for tea now! THANK YOU BOTH

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback

Replies
1
Views
448
Replies
1
Views
565
Replies
6
Views
543
Replies
3
Views
840
Replies
21
Views
1K

1,148,147
Messages
5,745,055
Members
423,917
Latest member
Frank1931

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