How to Compare Phone Numbers in WPS Spreadsheets and Return Matching User Details
Question details
The user wants to match mobile numbers in two spreadsheet tables and return the matching user name and address from the second table, showing UNKNOWN when no match exists.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Comparing phone number lists in two tables to enrich one table with user details.
- Observed behavior
- The desired result is to return the matching user name and address in adjacent columns, or UNKNOWN when the phone number is not found.
Make sure both phone number columns use the same data type and formatting, and confirm the lookup range in the second sheet starts with the mobile number column.
Use XLOOKUP to Return Both User Name and Address
Use this single-formula approach if you have WPS Spreadsheets 2021 or later, or Microsoft 365, because it returns both matching columns at once.
XLOOKUP is the simplest formula for this task because it accepts a two-column return range and has a built-in UNKNOWN fallback for missing phone numbers. The result spills into two adjacent cells in the same row.
Click the first cell in Table 1 where you want the matching user name to appear, for example C2.
Type =XLOOKUP(A2,Sheet2!$A$2:$A$100,Sheet2!$D$2:$E$100,"UNKNOWN") and press Enter.
Use the fill handle to copy the formula down for the remaining phone numbers in Table 1.
Use VLOOKUP with IFERROR for Older WPS Spreadsheets or Excel Versions
Use two separate VLOOKUP formulas when XLOOKUP is not available and you need to return the user name and address independently.
Match Phone Numbers and Return User Details in WPS Spreadsheets
Use XLOOKUP or VLOOKUP in WPS Spreadsheets to compare phone numbers across tables and return the matching name and address, with UNKNOWN shown for missing values. The workbook remains fully compatible with Microsoft Excel XLSX files.
- 1. Open the workbook: Open WPS Spreadsheets and load the workbook that contains both tables.
- 2. Click the first output cell: Click the first cell in Table 1 next to the phone number where you want the matching user name to appear.
- 3. Enter the lookup formula: Type the XLOOKUP formula or the two VLOOKUP formulas for your WPS Spreadsheets version.
- 4. Copy the formula down: Press Enter and drag the fill handle down through the remaining phone number rows.
- 5. Verify the returned details: Review the returned user name and address, and confirm that missing phone numbers display UNKNOWN.
Frequently Asked Questions
Why does VLOOKUP return #N/A even though the phone number looks the same?
The likely cause is inconsistent data types. One column may store phone numbers as text and the other as numbers, or there may be extra spaces, country codes, or different formatting. Convert both phone number columns to the same format before using the lookup formula.
Can I return both user name and address with one formula?
Yes. In WPS Spreadsheets 2021 or later and Microsoft 365, use XLOOKUP with a two-column return range such as Sheet2!$D$2:$E$100. In older versions, use two separate VLOOKUP formulas for column 4 and column 5.
What should I do if XLOOKUP is not available in my WPS Spreadsheets version?
Use the IFERROR and VLOOKUP combination instead. Enter =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$E$100,4,FALSE),"UNKNOWN") for the user name and change 4 to 5 for the address.
How can the formula return UNKNOWN instead of an error?
XLOOKUP has a built-in if_not_found argument, so you can set it to "UNKNOWN". With VLOOKUP, wrap the formula in IFERROR and use "UNKNOWN" as the second argument.
Can I compare phone numbers between two different workbooks?
Yes. You can reference an external workbook in the formula. Open both files, then select the range from the second workbook when building the formula. WPS Spreadsheets will create the external reference automatically.
