formula to display a score for a value within a stepped range

[deirdreob]

Having trouble with this one - Pls Help me display the appropriate point, when the value falls within the following ranges. Have not been able to find the right vlookup combo.
Note: As I will reuse the basic formula for different upper & lower data range values, I would like avoid hard-coding an If, index and match, or similar formula.

Date Ranges Points Awarded
< 1.0 5 points
1.1 to 1.5 4 points
1.6 to 2.0 3 points
2.1 to 3.0 1 point
> 3.0 0 points

Many thanks in advance!

 

[Aladin Akyurek]

Having trouble with this one - Pls Help me display the appropriate point, when the value falls within the following ranges. Have not been able to find the right vlookup combo.
Note: As I will reuse the basic formula for different upper & lower data range values, I would like avoid hard-coding an If, index and match, or similar formula.

Date Ranges Points Awarded
< 1.0 5 points
1.1 to 1.5 4 points
1.6 to 2.0 3 points
2.1 to 3.0 1 point
> 3.0 0 points

Many thanks in advance!

The header speaks of date ranges, but the values seem to be decimals. If latter:

Create a 2-column table like below:

0, 5
1.1, 4
1.6, 3
2.1, 1
3.1, 0

Name this range as Table. Now you can invoke:

=VLOOKUP(X2,Table,2,1)

to assign points to a score in X2.

 

[deirdreob]

Perfection, Thank You!

 

[Aladin Akyurek]

Perfection, Thank You!

You are welcome. Thanks for providing feedback.