How to Return Examination Scores Based on Points Missed in Excel
Question details
The user needs to build a formula that looks up an examination score percentage from a scoring table, based on two specific variables: the examination type and the number of points missed.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Grading exams where the final percentage depends on a two-dimensional grid of exam types and points deducted.
- Observed behavior
- Requires identifying the correct scoring row for the exam type and the correct column for the points missed to return the intersecting percentage value.
Ensure your scoring table is organized in a grid format, with examination types listed in the first column and the number of points missed listed across the top row. Defining named ranges for your table, rows, and columns will make writing and troubleshooting your formulas much easier.
Use INDEX and MATCH for a Two-Way Lookup
Combine the INDEX and MATCH functions to perform a 2-way lookup, finding a value at the exact intersection of a specific row and column.
The INDEX function returns the value of a cell in a specific row and column of a given range. By using two separate MATCH functions—one to find the row number of the exam type and another to find the column number of the points missed—you can accurately pull the correct percentage from the scoring grid.
Create a table where the leftmost column contains the examination types and the top row contains the points missed (e.g., 0, 1, 2, 3). The interior cells should contain the resulting percentages.
Determine the row position of the exam type by typing MATCH(exam_type_cell, exam_type_range, 0).
Determine the column position for the points missed by typing MATCH(points_missed_cell, points_missed_range, 0).
Assemble the final formula to extract the intersecting value. Enter =INDEX(scoring_grid_range, MATCH(exam_type_cell, exam_type_range, 0), MATCH(points_missed_cell, points_missed_range, 0)) into the result cell and press Enter.
Combine VLOOKUP with MATCH
Use VLOOKUP to find the correct exam type row, and use MATCH to dynamically determine which column index contains the points missed.
Calculate Examination Scores Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, and VLOOKUP, allowing you to build dynamic scoring tables and grade exams effortlessly. It is highly compatible with Microsoft Excel formats and provides a seamless grading experience.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your student scores and the grading table.
- 2. Apply the 2-Way Lookup Formula: Click on the target cell and enter your =INDEX(..., MATCH(...), MATCH(...)) formula exactly as you would in Excel.
- 3. Drag to AutoFill: Hover over the bottom-right corner of the formula cell and drag the fill handle down to calculate the scores for the rest of the students instantly.
Frequently Asked Questions
Can I use XLOOKUP for this two-way scoring table?
Yes. If you are using a modern version of Excel or WPS Spreadsheet, you can nest two XLOOKUP functions to perform a two-way lookup. The formula structure looks like =XLOOKUP(exam_type, exam_range, XLOOKUP(points_missed, points_range, scoring_grid)).
What if the points missed falls between values in my table?
If your points missed are tracked in ranges rather than exact numbers, change the match_type argument in your MATCH function from 0 (exact match) to 1 (less than) or -1 (greater than). Ensure your table headers are sorted in the required ascending or descending order for this to work.
Why is my INDEX and MATCH formula returning an #N/A error?
The #N/A error usually indicates that either the examination type or the points missed value does not exactly match any of the headers in your scoring table. Check for hidden spaces, formatting differences, or typos in your lookup values and table headers.
