if function selecting from a range

rjwalker2514

New member
Joined
Aug 25, 2018
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2016
I would like a formula to produce a value based on a measurement range

Example:

If I enter into cell A1 any value from 0.1 to 2.4 inclusive I would like cell A2 to show 400.
If I enter into cell A1 any value from 2.5 to 4.0 inclusive I would like cell A2 to show 600.

All measurements are to one decimal place

Any help greatly appreciated
 
What if neither condition is met?
 
Then try:

=IF(AND(A1>=0.1,A1<=2.4),400,IF(AND(A1>=2.5,A1<=4),600,0))
 
You can also do something along these lines:
=INDEX({0;400;600},MATCH(A1,{0;0.1;2.5}))
The main advantage is that you can easily add to your ranges.
Your thresholds are {0;0.1;2.5}
Your results are {0;400;600}
just add more thresholds and results to both to handle as many ranges as you want (almost).

In the same vein:
=VLOOKUP(A1,{0,0;0.1,400;2.5,600},2,TRUE)
Here you can see the thresholds and results are in the same place:
{0,0;0.1,400;2.5,600}
note the semicolons separating threshold/result pairs; just add more as required.

In both cases keep the thresholds in ascending order.
In both cases you can replace a result or two with strings such as "Out of range":
=INDEX({0;400;600;"Too high"},MATCH(A1,{0;0.1;2.5;9}))
or
=VLOOKUP(A1,{0,0;0.1,400;2.5,600;9,"Too high"},2,TRUE)
 
Last edited:
Back
Top