How to check if a certain range of numbers falls in a specified fixed range of numbers?

New Member
Hello there,

I need some help getting information from my data.

Lets say I have a number range in excel that I want to check what range it falls under and indicate it with some value or number or text.

Starting Ending

21.28 39.62
39.78 64.93
122.14 142.28
142.69 167.81
1580 1610
3249 3300

I want to check if my starting and ending points fall in the below category and indicate which range it falls to with (numbers, text etc) or anything that I can use later.

For example,
39.78 to 64.93 falls in S.N (1) Range and so the formula should indicate it by telling us that it falls in that category 1.
122.14 to 142.28 also falls in S.N (1) Range but max value is limited to 152.62 only
1580 to 1610 does not fall in any range and so the formula should return a false value or no value

S.N Range (Starting) Range (Ending)

1 0 152.62
2 396.27 610.16
3 680.52 804.28
4 888.22 1039.66
5 1524.08 1617.37
6 2037.18 2240.69
7 2692.1 2954
8 3232.55 3432.83
9 3780.27 3974.62

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Habtest

Board Regular
1580 to 1610 does not fall in any range and so the formula should return a false value or no value
Hello, doesn't 1,580-1,610 fall into #5 1,524-1,617? Assuming that a typo, see if it works.
Book1
ABC
1StartingEnding
2
321.2839.621
439.7864.931
5122.14142.281
6142.69167.81Not in any range
71,580.001,610.005
83,249.003,300.008
9
10S.NRangeRange
11(Starting)(Ending)
121-152.62
132396.27610.16
143680.52804.28
154888.221,039.66
1651,524.081,617.37
1762,037.182,240.69
1872,692.102,954.00
1983,232.553,432.83
2093,780.273,974.62
21
22
Sheet1
Cell Formulas
RangeFormula
C3:C8C3=IF(AND(A3<=B3,LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20)=IFERROR(LOOKUP(B3,\$C\$12:\$C\$20,\$A\$13:\$A\$21),1)),LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20),"Not in any range")

New Member
@Habtest Thank you very much for your help. It meant a lot. This formula worked perfectly. I really appreciate you managed some time to solve this for me. However, I didnt understood how this formula worked. If you would be kind enough to explain the formula mechanism whenever you have time, I would be extremely grateful to you.

Habtest

Board Regular
@Habtest Thank you very much for your help. It meant a lot. This formula worked perfectly. I really appreciate you managed some time to solve this for me. However, I didnt understood how this formula worked. If you would be kind enough to explain the formula mechanism whenever you have time, I would be extremely grateful to you.
The key is to find to which SN range floor/ceiling each input range floor/ceiling belong.
E.g. 21.28-39.62, the largest range start in the table <=21.28 is 0 in #1, the smallest range end >=39.62 is 152.62 also in #1. As they are both in #1, it's a match, thus 21.28-39.62 fits in #1
E.g. 142.69-167.81; the floor fits in #1 but the ceiling in #2, thus not a match.

LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20)=IFERROR(LOOKUP(B3,\$C\$12:\$C\$20,\$A\$13:\$A\$21),1)) here does the comparison between floor range number and ceiling number. LOOKUP() function finds, in an ascending list, the largest number which is less than a lookup value, when there is no match. You may find plenty tutorials about the function.

New Member

Thank you very much @Habtest. Now I understood the mechansim.

New Member
Hello @Habtest. I was wondering how the formula would tweak if in the above example if I need a small adjustment. Here 142.69 - 167.81 Terminated somewhere in the range 0 - 152.62 and hence we coded the formula to display it as "Not in any range" message. But what if we want to display it as "Partial" message for the range that ends somewhere in the middle and display "Not in any range" message only for those range which does not fall at the specified range at all (neither the starting nor the ending range)

Habtest

Board Regular

Hi there, see if it works:
Test.xlsx
ABC
1StartingEnding
2
321.2839.621
439.7864.931
5122.14142.281
6142.69167.81Partial
7158016105
8324933008
9
10S.NRangeRange
11(Starting)(Ending)
1210152.62
132396.27610.16
143680.52804.28
154888.221039.66
1651524.081617.37
1762037.182240.69
1872692.12954
1983232.553432.83
2093780.273974.62
Sheet1
Cell Formulas
RangeFormula
C3:C8C3=IF(AND(A3<=B3,LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20)=IFERROR(LOOKUP(B3,\$C\$12:\$C\$20,\$A\$13:\$A\$21),1)),LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20),IF(OR(LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20)=IFERROR(LOOKUP(A3,\$C\$12:\$C\$20,\$A\$12:\$A\$20),0)+1,LOOKUP(B3,\$C\$12:\$C\$20,\$A\$12:\$A\$20)=IFERROR(LOOKUP(B3,\$B\$12:\$B\$20,\$A\$12:\$A\$20),0)+1),"Partial","Not in any range"))

New Member
Thank you very much @Habtest. This formula works perfectly well in the dummy excel file. But for some strange reason does not work with my main excel file where I use this formula. All I get is "Not in any range" message. not even "Partial" or "number" even when I alter the numbers to see if change happens. I kept old formula and the new formula side by side for comparison. But the new formula shows only "Not in any range" whereas the old one is showing correct info.

[Revision]

Last edited:

Habtest

Board Regular
I've revised the formula, see if it works:

Test.xlsx
ABC
1StartingEnding
2
321.2839.621
439.7864.931
5122.14142.281
6142.69167.81Partial
7158016105
8324933008
9
10S.NRangeRange
11(Starting)(Ending)
1210152.62
132396.27610.16
143680.52804.28
154888.221039.66
1651524.081617.37
1762037.182240.69
1872692.12954
1983232.553432.83
2093780.273974.62
Sheet1
Cell Formulas
RangeFormula
C3:C8C3=IF(AND(A3<=B3,LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20)=IFERROR(LOOKUP(B3,\$C\$12:\$C\$20,\$A\$13:\$A\$21),1)),LOOKUP(A3,\$B\$12:\$B\$20,\$A\$12:\$A\$20),IF(SUMPRODUCT((A3>=\$B\$12:\$B\$20)*(A3<=\$C\$12:\$C\$20))+SUMPRODUCT((B3>=\$B\$12:\$B\$20)*(B3<=\$C\$12:\$C\$20)),"Partial","Not in any range"))

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,254
Messages
5,769,055
Members
425,513
Latest member
9375021758

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.

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