Data Import & Export

How to Match Student Names and Scholarship Amounts Across Excel Workbooks

Maira MehtabMaira Mehtab Nov 11, 2026 868 views

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.

How to Match Student Names and Scholarship Amounts Across Excel Workbooks
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.
Before you start

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.

Solution 1Recommended

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.

1
Open both workbooks

Open both the destination workbook (where you want the amounts to appear) and the source workbook (containing the scholarship data).

2
Start the XLOOKUP formula

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.

3
Select the lookup array

Type a comma, switch to the source workbook window, and select the entire column containing the Student IDs.

4
Select the return array

Type another comma, then select the column containing the scholarship amounts in the source workbook.

5
Handle missing data and complete

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 XLOOKUP with Unique Student IDs
Why use IDs?: IDs prevent mismatches that occur when students share the same name or have slightly different formatting (e.g., missing middle initials).

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. 1. Launch WPS Spreadsheet: Open WPS Office and launch the Spreadsheet module.
  2. 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. 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.
100% compatible with Microsoft Excel formulas and .xlsx formats.Built-in XLOOKUP support for modern, straightforward data matching.Free, lightweight, and fast, even when handling large datasets.Tabbed interface allows you to view multiple workbooks in a single window for easy cross-referencing.
QA img-9

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.