ExcelNewbie2020
Active Member
- Joined
- Dec 3, 2020
- Messages
- 333
- Office Version
- 365
- Platform
- Windows
I have this scenario.. i need to lookup a number if its within a range and if its within the range i need to lookup another number if it is also within a range..
for example in cell H5 (4450000) it is within the range in range 1 (from/to)..since it is within the range 2nd step is to check if cell I5 (12) if within range 2 (from/to).which is not so the result is "not ok".
for cells H6 and I6 since they are both within the range the result is "ok"..
for cell H7 outside the range result should be empty cell..
for example in cell H5 (4450000) it is within the range in range 1 (from/to)..since it is within the range 2nd step is to check if cell I5 (12) if within range 2 (from/to).which is not so the result is "not ok".
for cells H6 and I6 since they are both within the range the result is "ok"..
for cell H7 outside the range result should be empty cell..
Book1 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | range 1 | range 2 | ||||||||||
2 | from | to | from | to | ||||||||
3 | 4100000 | 4920999 | 13 | 13 | ||||||||
4 | 4100000 | 4999999 | 16 | 20 | range 1 | range 2 | formula here | |||||
5 | 4100000 | 4999999 | 31 | 33 | 4450000 | 12 | not ok | |||||
6 | 4200000 | 17 | ok | |||||||||
7 | 5100000 | 25 | empty cell | |||||||||
8 | ||||||||||||
9 | ||||||||||||
10 | ||||||||||||
11 | ||||||||||||
12 | ||||||||||||
13 | ||||||||||||
14 | ||||||||||||
Sheet1 |