Function Problems

Use INDEX and MATCH in Excel to Calculate Points from Multiple Criteria

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Create a formula that calculates incentive points using overtime hours and an SPH or EPH value.

Use INDEX and MATCH in Excel to Calculate Incentive Points
Product
Microsoft Excel
Device & OS
not provided
Scenario
An incentive program assigns points based on two criteria represented in a lookup table.
Observed behavior
The desired formula should return the points at the intersection of the matching overtime and SPH or EPH criteria.
Before you start

Define how overtime hours and SPH or EPH values map to points, including whether they are exact categories or ranges. The formula below assumes each input exactly matches a unique row or column label.

Solution 1Recommended

Use INDEX and two MATCH functions to find incentive points

Use this method when each overtime value and SPH or EPH value matches a discrete label in a two-dimensional points table.

INDEX returns the points from the table, while the two MATCH functions identify the corresponding row and column. The row and column labels must align with the points range.

1
Set up the lookup table

In Excel, enter overtime labels in A4:A7, SPH or EPH labels in C2:G2, and the corresponding points in C4:G7. Each points cell should represent the intersection of its row and column labels.

2
Enter the criteria

Enter the SPH or EPH value to look up in C12 and the overtime value in C13. Use values that exactly match the labels in the table.

3
Enter the formula

Select the cell where you want the points and enter =INDEX($C$4:$G$7,MATCH($C$13,$A$4:$A$7,0),MATCH($C$12,$C$2:$G$2,0)). Press Enter to return the matching points.

4
Check the result

Compare the returned value with the intersection of the selected overtime row and SPH or EPH column. If Excel returns #N/A, check C12 and C13 against the labels and confirm that numbers are not stored as text.

Use INDEX and two MATCH functions to find incentive points
Range-based criteria need different rules: The 0 in each MATCH requires an exact match. If points depend on thresholds or ranges, define the boundaries and inclusive rules before changing the formula; approximate matching also requires correctly sorted lookup labels.
Calculate points in WPS Spreadsheets

Calculate incentive points with WPS Spreadsheets

Build the same two-dimensional lookup in WPS Spreadsheets and use INDEX and MATCH to return points for the selected criteria.

  1. 1. Open a spreadsheet: Open WPS Office, choose Spreadsheets, and create a workbook or open an existing Excel file.
  2. 2. Create the points table: Enter overtime labels in A4:A7, SPH or EPH labels in C2:G2, and the corresponding points in C4:G7.
  3. 3. Add the criteria: Enter the SPH or EPH value in C12 and the overtime value in C13, matching the table labels exactly.
  4. 4. Calculate the points: Select an output cell and enter =INDEX($C$4:$G$7,MATCH($C$13,$A$4:$A$7,0),MATCH($C$12,$C$2:$G$2,0)). Press Enter, then check the result against the table.
Calculate points from overtime and SPH or EPH criteriaOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and a table-based layoutWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the INDEX and MATCH formula return #N/A?

One of the values in C12 or C13 may not exactly match a lookup label. Check C2:G2 and A4:A7 for missing values, extra spaces, or numbers stored as text.

Can this formula handle overtime ranges instead of exact hours?

Not as written: MATCH with 0 requires an exact value. Define the range boundaries and whether each boundary is inclusive, then use a formula designed for those rules and sort labels as required for approximate matching.

What if my lookup table is in different cells?

Replace C4:G7 with your points range, A4:A7 with its aligned row labels, and C2:G2 with its aligned column labels. Also replace C13 and C12 with the cells containing your criteria.

Can I use the same formula in WPS Spreadsheets?

Yes. Open the workbook in WPS Spreadsheets and enter the INDEX and MATCH formula in the output cell, adjusting its references to match your table.