Function Problems

How to Assign Points Based on Run Time Ranges Using Excel Formulas

Partner Editor Oct 12, 2026 870 views

Question details

The user needs an approximate-match formula to assign points to running times based on predefined thresholds, ensuring out-of-range times yield zero points.

How to Assign Points Based on Run Time Ranges Using Excel Formulas

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Evaluating race or running times against a scoring table to dynamically calculate a participant's points.
Observed behavior: Requires a function that returns accurate point values for times falling between boundaries and assigns zero for times outside the valid range.

Before you start

Ensure your threshold table (e.g., column A) is sorted in ascending order, as approximate-match functions require sorted data to return correct results.

Solution 1Recommended

Use INDEX and MATCH for Approximate Value Lookups

This approach uses an IF statement combined with INDEX and MATCH to find the appropriate points for a given run time, handling boundaries effectively.

By nesting INDEX and MATCH inside an IF formula, you can ensure that times falling below the minimum threshold or exceeding the maximum threshold receive exactly zero points.

Prepare your data table

Organize your data with run time thresholds in column A, the corresponding points in column B, and the specific run times you want to evaluate in column D (starting at D2).

Select the target cell

Click on cell E2 where you want the assigned points to be displayed.

Enter the INDEX MATCH formula

Type the formula: =IF(D2<MIN(A:A),0,IF(D2>MAX(A:A),0,INDEX(B:B,MATCH(D2,A:A,1)))) into the formula bar.

Apply to remaining cells

Press Enter, then drag the fill handle from the bottom-right corner of cell E2 down to apply the formula to the rest of the column.

Use INDEX and MATCH for Approximate Value Lookups

Data Sorting Requirement: The MATCH function with a match_type of 1 requires the lookup array in column A to be sorted in ascending order to work correctly.

Solution 2

Use XMATCH for More Precise Approximate Matches

If standard MATCH returns a point value one tier higher than expected, XMATCH provides better control over how approximate match items are evaluated.

Efficient Formula Processing

Master Lookup Formulas in WPS Office

WPS Spreadsheet fully supports advanced lookup formulas like INDEX, MATCH, and XMATCH. It makes building dynamic scoring systems and assigning points straightforward and efficient.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your run time data.
2. Select the calculation cell: Click the cell where you want to calculate points.
3. Input the formula: Type your INDEX and MATCH formula directly into the formula bar and press Enter.
4. Copy down the column: Double-click the fill handle to automatically copy the formula down.

Fully compatible with Microsoft Excel formulas and functionsBuilt-in error checking for complex nested IF statementsFree, lightweight, and fast for large datasetsIntuitive formula hints and syntax highlighting

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MATCH formula return an #N/A error?

An #N/A error usually occurs if the lookup value is smaller than the minimum value in your lookup array when using a match_type of 1. The nested IF function in our solution prevents this by explicitly checking the MIN value and returning 0 for out-of-range times.

Can I use VLOOKUP instead of INDEX and MATCH for assigning points?

Yes, you can use VLOOKUP with the range_lookup argument set to TRUE. However, INDEX and MATCH offer more flexibility and prevent errors if you ever need to insert new columns into your threshold table.

How do I handle thresholds if they are sorted in descending order?

If your run times are sorted in descending order, you should use a match_type of -1 in the MATCH function, or use XMATCH with the match_mode parameter set to 1 (exact match or next larger item).

Excel & Data Problems