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

erbhaskar95

New Member
Joined
Apr 30, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

Please help!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
198
Office Version
  1. 365
Platform
  1. Windows
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")
 

erbhaskar95

New Member
Joined
Apr 30, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
@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
Joined
Jul 30, 2020
Messages
198
Office Version
  1. 365
Platform
  1. Windows
@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.
Glad to help.
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.
 

erbhaskar95

New Member
Joined
Apr 30, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

erbhaskar95

New Member
Joined
Apr 30, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jul 30, 2020
Messages
198
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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"))
 

erbhaskar95

New Member
Joined
Apr 30, 2021
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
198
Office Version
  1. 365
Platform
  1. Windows
[Revision]
 
Last edited:

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
198
Office Version
  1. 365
Platform
  1. Windows
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:

Forum statistics

Threads
1,148,277
Messages
5,745,816
Members
423,980
Latest member
zimza

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top