WPS Spreadsheet: Match Employee IDs and Pull Phone Numbers
Question details
The user needs to match employee IDs in Sheet1 with IDs in Sheet2 and retrieve the corresponding phone numbers.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Sheet1 contains the employee list and IDs, while Sheet2 contains employee IDs in column A and phone numbers in column B. The user wants to keep every Sheet1 record and populate matching phone numbers.
- Observed behavior
- Phone numbers are not yet pulled into Sheet1; the desired result is a populated phone-number column with blanks for IDs that have no match.
Confirm that employee IDs use the same format in both sheets, such as all text or all numbers, and verify that Sheet2 stores IDs in column A and phone numbers in column B. Select the first empty phone-number cell beside the ID in Sheet1 before entering the formula.
Use XLOOKUP to retrieve phone numbers while keeping every Sheet1 employee
Use this recommended method when your WPS Spreadsheet version supports XLOOKUP and you want unmatched IDs to return blank cells.
XLOOKUP searches each employee ID from Sheet1 in Sheet2 column A and returns the phone number from Sheet2 column B. Because the lookup starts with Sheet1, every Sheet1 employee remains in the result even when no matching ID exists.
Open Sheet1 and click the empty cell beside the employee ID in row 2, such as B2.
Type =XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B,"") in the formula bar or directly in the selected cell, then press Enter.
Select the formula cell and double-click its fill handle, or select the target range and choose Home > Fill > Down, to apply the formula to the remaining Sheet1 employees.
Review the completed phone-number column; an employee ID with no match in Sheet2 will display a blank cell instead of an error.
Use VLOOKUP when XLOOKUP is unavailable
Choose this compatibility method if your WPS Spreadsheet installation does not recognize XLOOKUP.
Create a combined ID list from both sheets
Use this optional approach when the final list must include unmatched employee IDs from Sheet1 and Sheet2.
Match IDs and retrieve phone numbers with WPS Spreadsheet
WPS Spreadsheet can match employee IDs across worksheets with XLOOKUP or VLOOKUP, fill the results down, and preserve unmatched Sheet1 records with blank return values.
- 1. Open the workbook: Launch WPS Office, open the workbook, and select Sheet1.
- 2. Enter XLOOKUP: Click the first empty phone-number cell and enter =XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B,"").
- 3. Fill the results: Press Enter, then drag or double-click the fill handle to copy the formula down the employee list.
- 4. Verify the matches: Check that matching IDs show phone numbers and unmatched IDs remain blank.
Frequently Asked Questions
What formula matches an employee ID and returns a phone number in WPS Spreadsheet?
If Sheet1 cell A2 contains the ID and Sheet2 stores IDs in column A and phone numbers in column B, enter =XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B,"") in the result cell and fill it down.
How can I keep employees with no matching ID?
Use Sheet1 as the starting list and include an empty-string fallback in XLOOKUP: =XLOOKUP(A2,Sheet2!$A:$A,Sheet2!$B:$B,""). This keeps every Sheet1 row and leaves the phone-number cell blank when no match exists.
Why does the lookup return a blank or no match?
Check whether the IDs have the same data type and do not contain extra spaces. You can compare the values with LEN or clean text IDs with TRIM before performing the lookup.
What can I use if XLOOKUP is not supported in my WPS Spreadsheet version?
Use =IFERROR(VLOOKUP(A2,Sheet2!$A:$B,2,FALSE),""). This searches Sheet2 column A, returns the phone number from column B, and displays a blank for missing IDs.
Can I include employee IDs found only in Sheet2?
Yes. In a blank area, use =UNIQUE(VSTACK(Sheet1!A2:A1000,Sheet2!A2:A1000)) to combine both ID lists and remove duplicates, then use XLOOKUP beside the combined list to retrieve phone numbers.
