Use INDEX and MATCH to Retrieve Student Grades Across Excel Tabs
Question details
Retrieve student subject data from the correct worksheet and export column using a reliable lookup formula.

- 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.
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.
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.
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.
In another empty cell, enter =MATCH($B2,Grades!$B$1:$D$1,0). Confirm that it returns the position of the intended subject heading.
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.
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.

Clarify the Workbook Relationships Before Building the Formula
Use this approach when subjects, enrollment, grades, or targets are spread across exports with unclear or repeated headings.
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. Open the workbook: Open WPS Spreadsheet and select Open to load your Excel workbook.
- 2. Check the source tab: Select the grade worksheet tab and identify the student identifier column, subject heading row, and grade range.
- 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. 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.

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.




