Use INDEX and MATCH in Excel to Calculate Points from Multiple Criteria
Question details
Create a formula that calculates incentive points using overtime hours and an SPH or EPH value.

- 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.
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.
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.
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.
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.
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.
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.

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. Open a spreadsheet: Open WPS Office, choose Spreadsheets, and create a workbook or open an existing Excel file.
- 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. Add the criteria: Enter the SPH or EPH value in C12 and the overtime value in C13, matching the table labels exactly.
- 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.

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.




