Match Data Between Excel Sheets with XLOOKUP or VLOOKUP in WPS Office
Question details
The user needs to retrieve a corresponding value from Sheet2 when an ID in Sheet1 matches a value in Sheet2 column B.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user has data in two sheets and wants to return the matching value from Sheet2 column C next to each matching ID in Sheet1.
- Observed behavior
- The desired goal is for the matched value from Sheet2 to appear next to each ID in Sheet1.
Open the workbook in WPS Office Spreadsheets or Excel and confirm that Sheet1 contains the IDs to look up and that Sheet2 has the lookup values in column B and the return values in column C. Make sure the sheet names in the workbook match the names used in the formula.
Use XLOOKUP to Match All IDs and Return Values as a Spilled Array
Best for Microsoft 365 and current WPS Office versions that support dynamic arrays; enter one formula to return the matching values for the whole ID range.
XLOOKUP searches Sheet2!B1:B100 for each ID in Sheet1!A1:A100 and returns the corresponding value from Sheet2!C1:C100. The "" argument replaces #N/A errors with blank cells for unmatched IDs.
Click the cell where you want the returned values to start, such as C1 in Sheet1.
Type =XLOOKUP(A1:A100,Sheet2!B1:B100,Sheet2!C1:C100,"") and press Enter.
Check that the matching values appear in the output cells; if the results do not spill, use the VLOOKUP method below or update WPS Office.
Use VLOOKUP for Older Excel Versions and Copy It Down
Use this classic approach when you need compatibility with Excel 2019 or earlier, or when XLOOKUP is not available in your version.
Match Excel Sheet Data with XLOOKUP or VLOOKUP in WPS Office Spreadsheets
Open your .xlsx workbook in WPS Office Spreadsheets and use the same XLOOKUP or VLOOKUP formula to retrieve matching values from another sheet. WPS Office preserves Excel formatting and formulas, so you can match IDs without converting files or changing your workflow.
- 1. Open the Excel workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the workbook that contains Sheet1 and Sheet2.
- 2. Select the result cell in Sheet1: Click the cell where you want the matched value to appear, such as C1.
- 3. Enter the lookup formula: Type =XLOOKUP(A1:A100,Sheet2!B1:B100,Sheet2!C1:C100,"") in supported versions, or =VLOOKUP(A1,Sheet2!B1:C100,2,FALSE) for older compatibility, and press Enter.
- 4. Copy the formula down if necessary: When using VLOOKUP, drag the fill handle down through all ID rows; XLOOKUP with a range may spill automatically in supported versions.
Frequently Asked Questions
Why does my XLOOKUP formula return #N/A?
It means the ID was not found in Sheet2 column B. Add the if_not_found argument "" to return a blank instead, for example =XLOOKUP(A1,Sheet2!B1:B100,Sheet2!C1:C100,"").
Does WPS Office support XLOOKUP?
Current WPS Office Spreadsheets versions support XLOOKUP. If it is not available, use VLOOKUP or update WPS Office to a version that supports dynamic arrays.
Can I use VLOOKUP when the lookup column is not the first column in the range?
No, VLOOKUP always looks in the first column of the specified range. Use XLOOKUP or rearrange the range so the lookup column is first, for example Sheet2!B1:C100.
How do I avoid #N/A when some IDs are missing in VLOOKUP?
Wrap the VLOOKUP formula in IFERROR, for example =IFERROR(VLOOKUP(A1,Sheet2!B1:C100,2,FALSE),""), to return a blank cell instead of an error for unmatched IDs.
