formula for a value Range in one cell resulting in a defined value in a other cell

Create an ascending lookup table of each range’s lower limit, then use an approximate-match VLOOKUP in C4.

Recommended formula

For example, enter this table in P2:Q4:

Then enter this formula in C4:

=IFERROR(VLOOKUP(M4,$P$2:$Q$4,2,TRUE),"")

TRUE makes VLOOKUP select the row with the largest lower limit that is less than or equal to M4. The first column of the lookup table must be sorted from smallest to largest.^1^

Expected results:

• M4 = 5000 through 5100 → 850
• M4 = 5101 through 5200 → 900
• M4 = 5201 onward, until the next threshold → 950
• M4 < 5000 → blank because of IFERROR

Add further ranges as new rows—for example, 5302 | 1000 if the preceding range really ends at 5301. The formula does not need to change.

Boundary caution

Your ranges assume whole numbers. If M4 can contain decimals, values such as 5100.5 are not covered by “5000–5100” or “5101–5200.” Define whether those values should use the lower or upper band, then adjust the thresholds accordingly.

Also verify whether 5201–5301 is intentional; it contains 101 whole-number values, unlike the preceding 100-value ranges.

Direct formula without a lookup table

For only a few fixed ranges, place this in C4:

=IFS(
 AND(M4>=5000,M4<=5100),850,
 AND(M4>=5101,M4<=5200),900,
 AND(M4>=5201,M4<=5301),950,
 TRUE,""
)

AND requires both the lower-bound and upper-bound comparisons to be true.^2^ For a growing list of ranges, the lookup-table method is easier to update and less error-prone than a long nested formula.^1^

If Excel uses semicolons as formula separators in your regional settings, replace each comma with a semicolon.

References

1. IF function – nested formulas and avoiding pitfalls
2. AND function