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

ashok89jan

New Member
Joined
Jun 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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?

LowHighGrade
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​
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
With this situation you can Use Round Function. if your data is in Cell A2 then formula is
Excel Formula:
=ROUND(A2,0)
 

ashok89jan

New Member
Joined
Jun 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
@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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 19, 2021
Messages
326
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
Why make life complicated when round does exactly what the OP has asked for with that data?
 

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
723

ADVERTISEMENT

Data A2:C102
Cell D2 reference value
Formula

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
What happens if there are overlapping ranges?
 

ashok89jan

New Member
Joined
Jun 21, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

LowHighGrade
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Book2
ABCDEFG
1LowHighGradeNumberGrade
299.5100.49A195.5A2
398.599.49B1
497.598.49C1
596.597.49D1
695.596.49A2
794.595.49B2
893.594.49C2
992.593.49D2
1091.592.49A3
1190.591.49B3
1289.590.49C3
1388.589.49D3
1487.588.49A4
1586.587.49B4
1685.586.49C4
1784.585.49D4
1883.584.49A5
1982.583.49B5
2081.582.49C5
2180.581.49D5
2279.580.49A6
2378.579.49B6
2477.578.49C6
2576.577.49D6
2675.576.49A7
2774.575.49B7
2873.574.49C7
2972.573.49D7
3071.572.49A8
3170.571.49B8
3269.570.49C8
3368.569.49D8
3467.568.49A9
3566.567.49B9
3665.566.49C9
3764.565.49D9
3863.564.49A10
3962.563.49B10
4061.562.49C10
4160.561.49D10
4259.560.49A11
4358.559.49B11
4457.558.49C11
4556.557.49D11
4655.556.49A12
4754.555.49B12
4853.554.49C12
4952.553.49D12
5051.552.49A13
5150.551.49B13
5249.550.49C13
5348.549.49D13
5447.548.49A14
5546.547.49B14
5645.546.49C14
5744.545.49D14
5843.544.49A15
5942.543.49B15
6041.542.49C15
6140.541.49D15
6239.540.49A16
6338.539.49B16
6437.538.49C16
6536.537.49D16
6635.536.49A17
6734.535.49B17
6833.534.49C17
6932.533.49D17
7031.532.49A18
7130.531.49B18
7229.530.49C18
7328.529.49D18
7427.528.49A19
7526.527.49B19
7625.526.49C19
7724.525.49D19
7823.524.49A20
7922.523.49B20
8021.522.49C20
8120.521.49D20
8219.520.49A21
8318.519.49B21
8417.518.49C21
8516.517.49D21
8615.516.49A22
8714.515.49B22
8813.514.49C22
8912.513.49D22
9011.512.49A23
9110.511.49B23
929.510.49C23
938.59.49D23
947.58.49A24
956.57.49B24
965.56.49C24
974.55.49D24
983.54.49A25
992.53.49B25
1001.52.49C25
1010.51.49D25
102
Sheet1
Cell Formulas
RangeFormula
F2F2=INDEX($C$2:$C$101,MATCH(E2,$B$2:$B$101,-1))
 

Forum statistics

Threads
1,136,768
Messages
5,677,624
Members
419,707
Latest member
Anna vib

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