WPS Spreadsheets: Match Names Between Two Lists and Return Phone Numbers
Question details
Match each name in one list with the corresponding phone number in a reference list and populate the results, including repeated names in the destination list.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a working list and a separate reference list containing names and phone numbers. They need to return phone numbers for matching names and copy the formula through the working list.
- Observed behavior
- Phone-number cells in the working list need to be populated from the reference list. If a name appears multiple times in the working list, each matching row should return the corresponding reference number.
Confirm that the reference list has a Name column and a Phone Number column, and that names use consistent spelling and spacing in both lists. If duplicate names exist in the reference list itself, decide which occurrence should be returned because standard lookup formulas return the first match.
Use XLOOKUP to return phone numbers for matching names
Use this method in newer WPS Spreadsheets versions for a clear formula that handles missing names without displaying an error.
Assume the name to search is in A2, the reference names are in ListB[Name], and the reference phone numbers are in ListB[Phone Number]. XLOOKUP returns the first matching phone number, so the same formula can be used for repeated names in the destination list.
Open the working list in WPS Spreadsheets and select the first phone-number cell beside the name in A2.
Type =XLOOKUP(A2,ListB[Name],ListB[Phone Number],"") in the formula bar, then press Enter. Replace the table and column names if your reference list uses different names.
Select the formula cell and drag its fill handle down the phone-number column, or copy it and paste it into the remaining rows.
Use VLOOKUP or INDEX and MATCH for broad compatibility
Use these formulas when XLOOKUP is unavailable or when the workbook must remain compatible with older spreadsheet versions.
Match names and populate phone numbers in WPS Spreadsheets
WPS Spreadsheets lets you combine two lists with XLOOKUP, VLOOKUP, or INDEX and MATCH. Enter the lookup formula once, fill it down, and use the result in a familiar Excel-compatible workbook.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the working list and reference list.
- 2. Select the first output cell: Click the phone-number cell beside the first name in the working list.
- 3. Add a lookup formula: Enter =XLOOKUP(A2,ListB[Name],ListB[Phone Number],"") or use the VLOOKUP or INDEX/MATCH alternative if your version does not support XLOOKUP.
- 4. Fill the results: Press Enter, then drag the fill handle down the phone-number column to match every row.
Frequently Asked Questions
Which formula should I use to match names and return phone numbers?
Use =XLOOKUP(A2,ListB[Name],ListB[Phone Number],"") in newer WPS Spreadsheets versions. For older-compatible workbooks, use =IFERROR(VLOOKUP(A2,$H$2:$I$100,2,FALSE),"") or an INDEX and MATCH formula.
Will the formula work when the same name appears in multiple rows?
Yes. Copy the lookup formula down the destination column, and each row will search for its own name. If the reference list has one record per name, every repeated destination row returns the same phone number.
What happens if a name is not found in the reference list?
With XLOOKUP's final argument set to "", the result is blank. With VLOOKUP or INDEX and MATCH, wrap the formula in IFERROR, such as =IFERROR(VLOOKUP(A2,$H$2:$I$100,2,FALSE),"").
Why does VLOOKUP return the wrong phone number?
Make sure the reference-name column is the first column in the VLOOKUP range, use FALSE for exact matching, and check for extra spaces or inconsistent spelling. VLOOKUP returns the first match when duplicate names exist in the reference list.
