How to Use XLOOKUP in WPS Spreadsheets to Match Two Columns and Update a Sheet
Question details
Return a value from a master sheet when two fields, such as an ID and a program, match values on another worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a master table and a detail sheet, and needs the detail sheet to automatically show the matching value when both ID and Program match.
- Observed behavior
- The expected formula should update automatically when the master data changes.
Open your workbook in WPS Spreadsheets and confirm that both worksheets contain the two lookup columns, for example ID and Program. If you plan to use Excel tables, make sure each range has a header row before converting it to a table.
Use XLOOKUP with concatenated criteria and WPS tables
Best for workbooks that will grow or be updated, because table structured references adjust automatically and keep the formula readable.
Convert the lookup ranges to tables so the formula uses column names instead of fixed cell references. The concatenation inside XLOOKUP checks both ID and Program as a single combined key.
Select the result data range, go to Home > Format as Table, choose a style, and confirm the range has headers. Repeat this for the master data range.
Select the master table, go to Table Tools > Table Name, and rename it to Table1 for the example.
In the result table, click the first cell of the target column and enter =XLOOKUP([@ID]&[@Program],Table1[ID]&Table1[Program],Table1[% of hours],"").
Press Enter and copy the formula down the result column. WPS Spreadsheets updates the returned values when the master table changes.
Use XLOOKUP without converting to tables
Use this when you prefer direct cell references or cannot restructure the sheets into tables.
Use XLOOKUP in WPS Spreadsheets to match two columns and return the correct value
WPS Spreadsheets supports XLOOKUP in .xlsx workbooks, so you can replace helper columns with a concatenated lookup that returns the correct value only when ID and Program both match.
- 1. Open your workbook: Launch WPS Office, open the .xlsx workbook containing the master and result sheets.
- 2. Convert both ranges to tables: Select each data range and go to Home > Format as Table; confirm the ranges have headers.
- 3. Enter the XLOOKUP formula: Click the first result cell and type =XLOOKUP([@ID]&[@Program],Table1[ID]&Table1[Program],Table1[% of hours],"").
- 4. Fill down and verify: Press Enter, copy the formula down the result column, and change a master value to confirm the result updates.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP?
Yes, current versions of WPS Spreadsheets support XLOOKUP when you open or create an .xlsx workbook. If the function is not recognized, update WPS Office to the latest version.
How can I match two columns without creating a helper column?
Concatenate the two lookup columns directly inside XLOOKUP. For example, use A2&C2 as the lookup value and Sheet1!$A$2:$A$200&Sheet1!$C$2:$C$200 as the lookup array.
Why does the formula return #N/A even though the values appear to match?
Check for extra spaces, line breaks, or number-as-text mismatches in the key columns. Use TRIM or CLEAN to clean text values before matching.
Do I need to press Ctrl+Shift+Enter for this XLOOKUP formula?
No. In modern WPS Spreadsheets and Excel 365, XLOOKUP handles arrays natively, so press Enter normally.
Can the result update automatically when the master sheet changes?
Yes. XLOOKUP is a live formula, so when the master table values change, the returned results recalculate automatically in WPS Spreadsheets.
