Check a number is between two columns and then pick a value

[ashok89jan]

I have the data like given below till Low =0.5, High= 1.49, and Grade = 1 My need is, if I enter a number in a different cell, say 95, I need that to be compared with the entire Low and High columns and find in between which range it sits in (In the below sample it sits between Low= 94.5 and High 95.49), then pick the Grade for this range (which in the sample is 95). Can someone please help. Can we do this without VBA?

Low	High	Grade
99.5​	100​	100​
98.5​	99.49​	99​
97.5​	98.49​	98​
96.5​	97.49​	97​
95.5​	96.49​	96​
94.5​	95.49​	95​
93.5​	94.49​	94​
92.5​	93.49​	93​
91.5​	92.49​	92​
90.5​	91.49​	91​
89.5​	90.49​	90​

...... till

3.5​	4.49​	4​
2.5​	3.49​	3​
1.5​	2.49​	2​
0.5​	1.49​	1​

 

[maabadi]

Welcome to MrExcel Message Board.
With this situation you can Use Round Function. if your data is in Cell A2 then formula is

=ROUND(A2,0)

 

[ashok89jan]

@maabadi. My concern is I need to search for a number if it is within the range columns - Low and High and if a range is identified, then pick the corresponding Grade. The above is a sample and it's not rounding that I would need to do. Thank you

 

[Fluff]

With the data you have supplied, Round will do exactly what you want. If you have not supplied accurate data, then you will need to do so.

 

[Radoslaw Poprawski]

Im not sure but i think Author looks for =isbetween() formula that will tell him if a value X is between values Y and Z.

Is value(95) bewtween Low and high ranges

 

[Fluff]

Why make life complicated when round does exactly what the OP has asked for with that data?

 

[kvsrinivasamurthy]

Data A2:C102
Cell D2 reference value
Formula

=SUMIFS($C$2:$C$102,$A$2:$A$102,"<="&D2,$B$2:$B$102,">="&D2)

 

[Fluff]

What happens if there are overlapping ranges?

 

[ashok89jan]

I think the sample what I had given is confusing. Please consider this sample. My need is if I enter a value say 95 it should give me B2, If I enter 93 it should give me D2, If I enter 5, it should give D10.

Low	High	Grade
99.5​	100​	A1
98.5​	99.49​	B1
97.5​	98.49​	C1
96.5​	97.49​	D1
95.5​	96.49​	A2
94.5​	95.49​	B2
93.5​	94.49​	C2
92.5​	93.49​	D2

------

6.5​	7.49​	B10
5.5​	6.49​	C10
4.5​	5.49​	D10
3.5​	4.49​	A9
2.5​	3.49​	B9
1.5​	2.49​	C9
0.5​	1.49​	D9
0​	0.49​	A10

 

[maabadi]

Try this:

Book2
	A	B	C	D	E	F	G
1	Low	High	Grade		Number	Grade
2	99.5	100.49	A1		95.5	A2
3	98.5	99.49	B1
4	97.5	98.49	C1
5	96.5	97.49	D1
6	95.5	96.49	A2
7	94.5	95.49	B2
8	93.5	94.49	C2
9	92.5	93.49	D2
10	91.5	92.49	A3
11	90.5	91.49	B3
12	89.5	90.49	C3
13	88.5	89.49	D3
14	87.5	88.49	A4
15	86.5	87.49	B4
16	85.5	86.49	C4
17	84.5	85.49	D4
18	83.5	84.49	A5
19	82.5	83.49	B5
20	81.5	82.49	C5
21	80.5	81.49	D5
22	79.5	80.49	A6
23	78.5	79.49	B6
24	77.5	78.49	C6
25	76.5	77.49	D6
26	75.5	76.49	A7
27	74.5	75.49	B7
28	73.5	74.49	C7
29	72.5	73.49	D7
30	71.5	72.49	A8
31	70.5	71.49	B8
32	69.5	70.49	C8
33	68.5	69.49	D8
34	67.5	68.49	A9
35	66.5	67.49	B9
36	65.5	66.49	C9
37	64.5	65.49	D9
38	63.5	64.49	A10
39	62.5	63.49	B10
40	61.5	62.49	C10
41	60.5	61.49	D10
42	59.5	60.49	A11
43	58.5	59.49	B11
44	57.5	58.49	C11
45	56.5	57.49	D11
46	55.5	56.49	A12
47	54.5	55.49	B12
48	53.5	54.49	C12
49	52.5	53.49	D12
50	51.5	52.49	A13
51	50.5	51.49	B13
52	49.5	50.49	C13
53	48.5	49.49	D13
54	47.5	48.49	A14
55	46.5	47.49	B14
56	45.5	46.49	C14
57	44.5	45.49	D14
58	43.5	44.49	A15
59	42.5	43.49	B15
60	41.5	42.49	C15
61	40.5	41.49	D15
62	39.5	40.49	A16
63	38.5	39.49	B16
64	37.5	38.49	C16
65	36.5	37.49	D16
66	35.5	36.49	A17
67	34.5	35.49	B17
68	33.5	34.49	C17
69	32.5	33.49	D17
70	31.5	32.49	A18
71	30.5	31.49	B18
72	29.5	30.49	C18
73	28.5	29.49	D18
74	27.5	28.49	A19
75	26.5	27.49	B19
76	25.5	26.49	C19
77	24.5	25.49	D19
78	23.5	24.49	A20
79	22.5	23.49	B20
80	21.5	22.49	C20
81	20.5	21.49	D20
82	19.5	20.49	A21
83	18.5	19.49	B21
84	17.5	18.49	C21
85	16.5	17.49	D21
86	15.5	16.49	A22
87	14.5	15.49	B22
88	13.5	14.49	C22
89	12.5	13.49	D22
90	11.5	12.49	A23
91	10.5	11.49	B23
92	9.5	10.49	C23
93	8.5	9.49	D23
94	7.5	8.49	A24
95	6.5	7.49	B24
96	5.5	6.49	C24
97	4.5	5.49	D24
98	3.5	4.49	A25
99	2.5	3.49	B25
100	1.5	2.49	C25
101	0.5	1.49	D25
102
Sheet1

Cell Formulas
Range	Formula
F2	F2	=INDEX($C$2:$C$101,MATCH(E2,$B$2:$B$101,-1))