How to Match Student Names and Scholarship Amounts Across Excel Workbooks
Question details
The user needs to import scholarship amounts from one Excel workbook into another containing student names and IDs, requiring a reliable method to match records accurately.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging financial or educational data from two separate workbooks based on matching criteria.
- Observed behavior
- Names alone often fail to match correctly due to typos, formatting differences, or duplicate entries, requiring a more robust lookup method like unique IDs.
Ensure both workbooks are open before writing your formulas to make selecting ranges easier and to avoid broken external links. Identify a unique identifier, such as a Student ID, present in both files.
Use XLOOKUP with Unique Student IDs
Using a unique identifier like a Student ID is the most reliable way to match records and prevent errors caused by duplicate or misspelled names.
This method utilizes Microsoft 365's XLOOKUP function to seamlessly pull data between two open workbooks based on matching ID numbers.
Open both the destination workbook (where you want the amounts to appear) and the source workbook (containing the scholarship data).
In the destination workbook, select the first cell where you want the scholarship amount to appear. Type the formula =XLOOKUP( then click the cell containing the Student ID in the current row.
Type a comma, switch to the source workbook window, and select the entire column containing the Student IDs.
Type another comma, then select the column containing the scholarship amounts in the source workbook.
Add ,"Not found") to gracefully handle unmatched records. Press Enter, and then drag the fill handle down to apply the formula to all students.

Use INDEX and MATCH for Older Excel Versions
If you are not using Microsoft 365, XLOOKUP won't be available. INDEX and MATCH is the traditional, robust alternative for cross-workbook matching.
Standardize Name Formats Before Matching
If you must match by name because IDs are unavailable, you need to ensure both lists use the exact same formatting to avoid #N/A errors.
Easily Match Data Across Workbooks with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP, VLOOKUP, and INDEX/MATCH, allowing you to merge data across multiple files seamlessly without complex workarounds.
- 1. Launch WPS Spreadsheet: Open WPS Office and launch the Spreadsheet module.
- 2. Open multiple workbooks in tabs: Open both your student list workbook and the scholarship workbook. They will neatly organize into tabs within the same window.
- 3. Apply the XLOOKUP function: Select the target cell, type =XLOOKUP(), and easily switch between tabs to select your lookup and return arrays just as you would in standard Excel.

Frequently Asked Questions
Why is my XLOOKUP formula returning a #N/A error?
This usually means the lookup value (e.g., the Student ID or Name) in your destination workbook does not exactly match any entry in the source workbook. Check for hidden spaces, formatting differences (like text formatted as a number), or misspellings.
Can I use VLOOKUP instead to match the student data?
Yes, VLOOKUP can be used, but the lookup column (Student ID or Name) must be located to the left of the scholarship amount column in your source data. XLOOKUP and INDEX/MATCH are more flexible because they can search in any direction.
How do I handle multiple students with the exact same name?
Relying on names is risky. If two students have identical names, lookup functions will only return the first match they find. To fix this, always use a unique identifier like a Student ID, or use a complex formula to combine multiple columns (e.g., Name + Date of Birth) for the lookup.
What if one file has 'First Name Last Name' and the other has 'Last Name, First Name'?
You will need to standardize the names before matching. You can use Excel's Text to Columns feature, Flash Fill, or string manipulation formulas like LEFT, RIGHT, and MID to rearrange the text so both lists match perfectly before applying a lookup formula.




