Function Problems

Use INDEX and MATCH to Retrieve Student Grades Across Excel Tabs

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 868 views

Question details

Retrieve student subject data from the correct worksheet and export column using a reliable lookup formula.

Use INDEX and MATCH to Retrieve Student Grades Across Excel Tabs
Product
Microsoft Excel
Device & OS
not provided
Scenario
Several exports have been combined into a workbook with multiple worksheets and headings. A summary sheet needs to find a student's grade or target for a subject.
Observed behavior
The existing formula mixes lookup methods and ranges, and the relationships between the student, subject, and grade tables are not clear enough to determine the correct result.
Before you start

Identify the student identifier, subject heading, and grade or target column on each relevant tab. Write down a small sample with the expected result, and check that identifiers and headings match exactly.

Solution 1Recommended

Match the Student and Subject on the Correct Grade Tab

Use a two-way INDEX and MATCH lookup when students are listed in rows and subjects are listed in columns on one export tab.

This example assumes the summary sheet has a student identifier in A2 and a subject in B2. On the Grades sheet, identifiers are in A2:A100, subject headings are in B1:D1, and grades are in B2:D100. Replace these references with the ranges in your workbook.

The row and column ranges must align with the grade range. This formula searches the Grades tab only; use the appropriate sheet and return range for each export or target.

1
Test the student match

On the summary sheet, enter =MATCH($A2,Grades!$A$2:$A$100,0) in an empty cell. Confirm that it returns the student's position within the identifier range.

2
Test the subject match

In another empty cell, enter =MATCH($B2,Grades!$B$1:$D$1,0). Confirm that it returns the position of the intended subject heading.

3
Return the grade

In the result cell, enter =IFERROR(INDEX(Grades!$B$2:$D$100,MATCH($A2,Grades!$A$2:$A$100,0),MATCH($B2,Grades!$B$1:$D$1,0)),"Not found"). Compare the result with the corresponding cell on the Grades tab.

4
Check other exports

For a grade or target stored on another tab, update the sheet name and all three ranges in the formula. Test the result against a known student and subject before filling the formula down.

Match the Student and Subject on the Correct Grade Tab
Check enrollment and blank grades: A matching subject heading does not prove that a student takes that subject. Check the enrollment table separately if one exists. A blank source cell can also be returned as zero, so verify whether zero represents a grade or a missing value.
Work with spreadsheet lookups

Look Up Student Grades Across Sheets in WPS Office

Use INDEX and MATCH in WPS Spreadsheet to match a student row and subject column on another sheet, then return the corresponding grade.

  1. 1. Open the workbook: Open WPS Spreadsheet and select Open to load your Excel workbook.
  2. 2. Check the source tab: Select the grade worksheet tab and identify the student identifier column, subject heading row, and grade range.
  3. 3. Enter the lookup: On the summary sheet, select the result cell and enter an INDEX formula with one MATCH for the student and another MATCH for the subject. Adjust the sheet name and ranges to match your workbook.
  4. 4. Verify and save: Compare the result with a known grade on the source tab, then use File > Save As to save the workbook in XLSX format.
Match student identifiers and subject headings across spreadsheet tabsOpen and edit Microsoft Excel XLSX and XLS filesWork with familiar spreadsheet formulas and worksheet tabsUse WPS Office for free with an easy-to-use interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does MATCH return #N/A for a student or subject?

Check the lookup value against the source identifier or heading for spelling, extra spaces, and text-versus-number differences. Test each MATCH separately with 0 as the final argument for an exact match.

Can one formula search every export tab automatically?

The example formula searches only the named Grades tab. If data is spread across several exports, first determine which tab contains the required value, or consolidate the data into a consistent table before designing a broader lookup.

How do I return a target instead of a grade?

Find the target values on the source tab and change the INDEX return range to those cells. Update both MATCH ranges as needed so their rows and columns align with the target range.

Does a matching subject heading mean the student takes that subject?

No. The heading identifies a column, not enrollment. Check the student-subject enrollment table separately and use its result to decide whether to return a grade or target.