How to Use Lookup Formulas to Match Phone Numbers Across Workbooks in WPS Office Spreadsheets
Question details
Retrieve an ID number, last name, and first name from a second workbook by matching phone numbers between W1 Sheet1 and W2 Sheet2.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has two workbooks and wants to match phone numbers in column A of W1 Sheet1 to the same phone numbers in W2 Sheet2, then return the related ID, last name, and first name fields.
- Observed behavior
- Lookup formulas can return the matching row values but do not perform a physical copy-and-paste operation, so results must be converted to static values or copied with VBA if needed.
Confirm that the phone numbers are in the same format in both workbooks and note the exact sheet names and file path for W2. If phone numbers are formatted as text, preserve leading zeros so the lookup values match correctly.
Use XLOOKUP to Match Phone Numbers and Return the Related Fields
Use this when your version of WPS Office Spreadsheets supports XLOOKUP. It is the most flexible way to search W2 Sheet2 for a phone number and return the ID, last name, or first name.
XLOOKUP searches a lookup array for an exact match and returns the corresponding value from a return array. It can reference ranges in another open or closed workbook, but it only brings back the value that the formula points to; it does not physically copy the row.
If W2 is open, the workbook reference can be shorter. If W2 is closed, include the full file path in the formula so WPS Office Spreadsheets can locate the source data.
Open W1 Sheet1 in WPS Office Spreadsheets and click the cell where you want the first returned value, such as D2.
Type =IFERROR(XLOOKUP(A2,'C:\Folder\[W2.xlsx]Sheet2'!$A:$A,'C:\Folder\[W2.xlsx]Sheet2'!$B:$B,""),"") and press Enter. Adjust the path, workbook name, and sheet name to match your file.
For Last Name or First Name, replace the return range $B:$B with $C:$C or $D:$D, or whichever columns contain those fields in W2 Sheet2.
Drag the fill handle down through all rows that have phone numbers to return the matching values for the entire list.
Select the formula cells, press Ctrl+C, right-click the same selection, and choose Paste Special > Values to replace the formulas with static values.
Use VLOOKUP for Older Versions Without XLOOKUP
Use VLOOKUP when XLOOKUP is not available or when you need a lookup formula that also works in older Microsoft Excel-compatible workbooks.
Match Phone Numbers Across Workbooks with WPS Office Spreadsheets
WPS Office Spreadsheets can retrieve matching ID, last name, and first name fields from another workbook with XLOOKUP or VLOOKUP. Review the formula results, paste them as values, and save the file as a standard .xlsx workbook that remains compatible with Microsoft Excel.
- 1. Open both workbooks in WPS Office Spreadsheets: Open W1 and W2 in WPS Office Spreadsheets, or keep W2 closed and note its full file path.
- 2. Enter a lookup formula in the destination cell: Click the first return cell in W1 Sheet1 and enter the XLOOKUP or VLOOKUP formula that references the phone number cell and the matching column in W2 Sheet2.
- 3. Fill the formulas across and down: Adjust the return column for ID, last name, and first name, then drag the fill handle down through the complete phone list.
- 4. Paste the results as values: Select all lookup result cells, press Ctrl+C, right-click the selection, and choose Paste Special > Values so the returned data becomes permanent.
- 5. Save as an Excel-compatible workbook: Save the workbook as a .xlsx file to preserve the results and maintain compatibility with Microsoft Excel.
Frequently Asked Questions
Can a lookup formula automatically copy and paste the matching row from W2 to W1?
No. XLOOKUP and VLOOKUP return the value from the referenced cell or range. To keep the results permanently, select the formula cells and use Paste Special > Values, or use VBA if the full record must be physically copied and pasted.
Why does my VLOOKUP return #N/A even though the phone numbers look the same?
The phone numbers may be stored as text in one workbook and as numbers in the other, or they may contain extra spaces. Check the cell format, use exact-match FALSE, and clean the phone numbers with TRIM or by converting both columns to the same format.
Can XLOOKUP or VLOOKUP read data from a closed workbook?
Yes. Include the full file path, workbook name in square brackets, sheet name, and range in the formula, for example 'C:\Folder\[W2.xlsx]Sheet2'!$A:$A. When the closed workbook is moved or renamed, the formula must be updated.
How do I return both Last Name and First Name from the same matching phone number?
Use separate formulas in adjacent cells and change the return range or column index in each formula. For XLOOKUP, change the return array from $B:$B to $C:$C or $D:$D. For VLOOKUP, change the column index number to 3 or 4.
What should I do if my version of WPS Office does not support XLOOKUP?
Use VLOOKUP or INDEX/MATCH instead. VLOOKUP is available in older spreadsheet versions and can perform an exact-match lookup when the phone number is in the first column of the table range.
