How to Match Names Across Excel Lists and Return a Related Value in WPS Office
Question details
The user needs to match names between two Excel lists and return a corresponding value, such as a reason, when a match exists.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has two lists of names in WPS Office Spreadsheets and needs to compare them, then return a related reason from another column when a name exists.
- Observed behavior
- The formula should display the related reason for matched names and leave the cell blank when no match exists.
Before entering the formula, confirm that the name lists are on the same sheet or use correct sheet references, and remove any leading or trailing spaces with TRIM so the names match reliably.
Use XMATCH and XLOOKUP to Match Names and Return the Related Reason
Use this method in WPS Office versions that support modern lookup functions, because it handles the match test and the returned value in one clear formula.
This formula checks whether the name in I4 exists in the G4:G8 list, then uses XLOOKUP to return the related reason from F4:F14 when the name is found.
In WPS Office Spreadsheets, open the workbook that contains the two name lists and the related reason column.
Click the first cell where the returned reason should appear, such as J4.
Type the formula =IF(ISNUMBER(XMATCH(I4,$G$4:$G$8)),XLOOKUP(I4,$E$4:$E$14,$F$4:$F$14,""),"") and press Enter.
Drag the fill handle from the lower-right corner of the cell down through the remaining names to apply the formula.
Use VLOOKUP with IFNA for Older WPS Office or Excel Versions
Use this fallback when XLOOKUP or XMATCH is not available in your current version, or when you need a widely compatible formula.
Match Names Across Lists in WPS Office Spreadsheets Without Leaving Your Excel File
Use WPS Office Spreadsheets to open your Excel workbook and apply XLOOKUP/XMATCH or VLOOKUP/IFNA formulas to return related values when names match. WPS preserves Excel formulas and formatting, so you can continue using the lookup workflow without changing tools.
- 1. Download WPS Office: Visit the official WPS Office download page and install WPS Office, selecting WPS Spreadsheets during setup.
- 2. Open the Excel workbook: Launch WPS Spreadsheets, click the Menu button, choose Open, and select the workbook that contains the name lists.
- 3. Enter the name-matching formula: Click the first result cell, type the XLOOKUP/XMATCH or VLOOKUP/IFNA formula, and press Enter.
- 4. Fill down and verify results: Use the fill handle to copy the formula down and confirm that matching names show the related reason and unmatched names remain blank.
Frequently Asked Questions
Does WPS Office support XLOOKUP and XMATCH?
Recent versions of WPS Office Spreadsheets support XLOOKUP and XMATCH. If those functions are not available, update WPS Office or use the VLOOKUP with IFNA fallback formula.
How can I return a blank instead of #N/A when no match is found?
Wrap the lookup formula in IFNA or use the IF(ISNUMBER(XMATCH(...)),XLOOKUP(...),"") pattern. This returns an empty cell when no match exists.
Can I match names between two different sheets?
Yes. Include the sheet name in each range, such as Sheet2!$G$4:$G$8, in the XLOOKUP, XMATCH, or VLOOKUP formula.
Why does my VLOOKUP return #N/A even when the name appears in both lists?
This usually happens because of extra spaces, different capitalization, or inconsistent formatting. Use TRIM to remove spaces and make sure the values in both lists are formatted the same way.
What is the difference between XLOOKUP and VLOOKUP for matching names?
XLOOKUP can search one range and return a value from another without requiring the lookup column to be leftmost. VLOOKUP requires the lookup value to be in the first column of the table array and is often combined with IFNA to handle missing matches.
