Function Problems

Match Student and Class Across Excel Sheets to Return Grades

Nimra MalikNimra Malik Nov 18, 2026 868 views

Question details

Find a grade by matching both a student name and a class across Excel worksheets or workbooks.

Match Students and Classes Across Excel Sheets to Return Grades
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the source columns

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.

2
Enter the lookup formula

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)),"-").

3
Confirm and fill

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.

4
Check unmatched entries

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.

Use INDEX and MATCH to find a grade with two criteria
Grades in another workbook: Open the source workbook and select its ranges while building the formula to create external references. Keep the source file in its expected location so Excel can update the links.
Work with grades in WPS Spreadsheets

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. 1. Open the workbook: Open WPS Spreadsheets, choose Open, and select the workbook containing your source and destination sheets.
  2. 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. 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. 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.
Match student names and class headings in one formulaOpen and edit Microsoft Excel XLSX and XLS filesFill a lookup formula across multiple students and classesUse a familiar spreadsheet interface
QA img-9

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.