Use XLOOKUP in WPS Spreadsheets to Pull Student Grades by Subject
Question details
Retrieve a student's grade from another worksheet by matching a subject name with the XLOOKUP function.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains subject names, while another worksheet contains subject labels and corresponding student grades.
- Observed behavior
- The user needs an exact subject match or a match that recognizes a subject name within a longer source label, such as one containing grade-level information.
Confirm that the lookup range on the source worksheet contains subject labels and that the return range contains the matching grades in the same row order. Use the same subject spelling where possible, and remove unwanted leading or trailing spaces before matching.
Use an exact-match XLOOKUP formula for identical subject names
Use this method when the subject names in both worksheets are the same, such as "Math" matching "Math."
XLOOKUP searches the subject in your current sheet and returns the grade from the matching row on the source sheet. The formula returns "Not found" when no matching subject is available.
In the worksheet where you want to display the grade, click the first result cell, such as B2 next to the subject in A2.
Type =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found") and press Enter. Replace Sheet2 and the cell ranges with the names and ranges in your workbook.
Select the cell containing the formula and drag the fill handle down, or copy and paste the formula into the remaining grade cells.
Use wildcard XLOOKUP when source subjects contain extra text
Use this method when the source worksheet has longer labels, such as "Mathematics - Grade 8" while your lookup cell contains "Mathematics."
Pull Grades by Subject with XLOOKUP in WPS Spreadsheets
WPS Spreadsheets lets you use XLOOKUP to match subject names across worksheets and return the related grades. Enter an exact-match formula for consistent labels, or enable wildcard matching when source subjects include grade levels or other descriptive text.
- 1. Open the grade workbook: Open the workbook in WPS Office and select the worksheet where the subject names and returned grades should appear.
- 2. Check the source columns: Open the source worksheet and identify the subject-label range and the grade return range, for example A2:A100 and B2:B100.
- 3. Add an exact-match formula: Click the first result cell and enter =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found").
- 4. Switch to wildcard matching if needed: For source labels with additional text, replace the formula with =XLOOKUP("*"&A2&"*",Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found",2).
- 5. Copy the formula for all subjects: Drag the fill handle from the completed result cell down the grade column to retrieve each remaining subject grade.
Frequently Asked Questions
What does the fourth argument, "Not found," do in XLOOKUP?
The fourth argument is the value returned when XLOOKUP cannot find a match. You can replace "Not found" with another message, such as "Missing grade," or with an empty string ("") to show a blank cell.
How do I match a subject name that is part of a longer label?
Use a wildcard formula with match mode 2: =XLOOKUP("*"&A2&"*",Sheet2!$A$2:$A$100,Sheet2!$B$2:$B$100,"Not found",2). The asterisks allow text before and after the subject in the source label.
Why does XLOOKUP return the wrong grade with wildcard matching?
Wildcard matching returns the first source label that contains the lookup text. Make the lookup subject more specific, such as including the course level, or use the exact-match formula when labels are unique.
Can I copy the XLOOKUP formula down for every student or subject?
Yes. Keep the source ranges absolute with dollar signs, such as Sheet2!$A$2:$A$100 and Sheet2!$B$2:$B$100, then drag the fill handle down. The lookup cell reference, such as A2, changes for each row.
Can WPS Spreadsheets open an Excel workbook that contains XLOOKUP formulas?
Yes. WPS Spreadsheets can open and edit Microsoft Excel .xlsx and .xls files. Check the returned values after opening the workbook, especially if the file uses advanced functions or external workbook references.
