Match Student and Class Across Excel Sheets to Return Grades
Question details
Find a grade by matching both a student name and a class across Excel worksheets or workbooks.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A destination sheet lists students and classes, while a source sheet contains the corresponding grades.
- Observed behavior
- Each destination cell should display the grade for the matching student and class, or a placeholder if no match is found.
Check which columns contain student names, classes, and grades on the source sheet. In the destination sheet, place student names in column A and class headings in row 1; adjust the formula references if your layout differs.
Use INDEX and MATCH to find a grade with two criteria
Use this formula when the source data has one row for each student and class combination.
The formula checks both the student name and class, then returns the grade from the first matching source row.
On Sheet1, put student names in A2:A200, classes in B2:B200, and grades in C2:C200. Change these ranges in the formula if your source data uses different columns or extends beyond row 200.
On the destination sheet, select B2 and enter =IFERROR(INDEX(Sheet1!$C$2:$C$200,MATCH(1,(Sheet1!$A$2:$A$200=$A2)*(Sheet1!$B$2:$B$200=B$1),0)),"-").
Press Enter in a current version of Excel. In older versions, press Ctrl+Shift+Enter to confirm the array formula. Drag the fill handle across the class columns and down the student rows.
If a cell displays a hyphen, compare its student name and class heading with the entries on Sheet1. Check for extra spaces and for numbers stored as text.

Match Student Grades with WPS Spreadsheets
Use INDEX and MATCH in WPS Spreadsheets to look up a grade by student and class, then fill the formula across your results table.
- 1. Open the workbook: Open WPS Spreadsheets, choose Open, and select the workbook containing your source and destination sheets.
- 2. Check the layout: On Sheet1, check that student names are in column A, classes are in column B, and grades are in column C. On the destination sheet, put student names in column A and classes in row 1.
- 3. Enter the formula: Select B2 on the destination sheet and enter =IFERROR(INDEX(Sheet1!$C$2:$C$200,MATCH(1,(Sheet1!$A$2:$A$200=$A2)*(Sheet1!$B$2:$B$200=B$1),0)),"-"). Adjust the ranges and sheet name to match your workbook.
- 4. Fill the results: Confirm the formula, then drag B2's fill handle across and down. If your version requires array-formula entry, confirm it with Ctrl+Shift+Enter.

Frequently Asked Questions
Why does the formula return a hyphen instead of a grade?
The hyphen appears when IFERROR catches an error, including a missing match. Check the student name and class against Sheet1 for extra spaces, different spellings, or numbers stored as text. Also verify that the source ranges include the relevant row.
What happens if a student has two grades for the same class?
MATCH returns the position of the first matching row, so the formula displays only that row's grade. Review duplicate student and class combinations on the source sheet if you need a different result.
Can I look up grades from a separate workbook?
Yes. Open the source workbook and select its student, class, and grade ranges when entering the formula in the destination workbook. Excel will create external workbook references; the links must remain accessible for updates.
Why does the formula need Ctrl+Shift+Enter in older Excel versions?
The formula compares arrays of student names and classes. Older Excel versions require Ctrl+Shift+Enter to confirm this kind of array formula, while current versions generally accept Enter.




