WPS Spreadsheets: Use XLOOKUP with a Combined Key in a Split Excel Table
Question details
The user needs to retrieve values by matching fields stored in separate columns against a related split table on another worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A lookup requires both values in cells B3 and C3 to identify the matching row in the LISTS_ALL worksheet.
- Observed behavior
- The related key fields and return values are separated across columns, so a combined lookup key is needed before XLOOKUP can return the matching data.
Confirm which source columns form the combined key and which columns contain the values to return. Choose a delimiter, such as a comma, that does not occur within either key field, and verify that the source ranges have matching row counts.
Use XLOOKUP with concatenated lookup fields
Use this solution when the lookup key is split across two cells and the source worksheet stores the corresponding fields in separate columns.
Join the lookup fields with the same delimiter on both sides of the XLOOKUP formula. The lookup array and return array must cover the same source rows, and the combined key should identify one matching row.
For example, the formula below combines B3 and C3, searches the combined fields on LISTS_ALL, and returns the matching values from columns G and H.
Open the destination worksheet and click the cell where the matching result should appear.
Enter =XLOOKUP(B3&","&C3,LISTS_ALL!$B$3:$B$2508&","&LISTS_ALL!$C$3:$C$2508,LISTS_ALL!$G$3:$H$2508," ") and press Enter.
Make sure the comma delimiter is identical in B3&C3 and in the LISTS_ALL lookup array, and adjust the row limits or columns if your source table differs.
Drag the fill handle from the completed result cell down the destination column to retrieve results for additional rows.
Build a helper key column before using XLOOKUP
Use a helper column when the combined-array formula is difficult to maintain, does not calculate correctly, or needs easier troubleshooting.
Retrieve split-table values with XLOOKUP in WPS Spreadsheets
WPS Spreadsheets lets you combine multiple lookup fields directly in an XLOOKUP formula, search a related worksheet, and return one or more matching columns without manually merging the source table.
- 1. Open the workbook: Launch WPS Office, open the Excel workbook, and go to the worksheet where the lookup result is needed.
- 2. Select the output cell: Click the first result cell and prepare the formula bar for the XLOOKUP formula.
- 3. Enter the XLOOKUP formula: Use =XLOOKUP(B3&","&C3,LISTS_ALL!$B$3:$B$2508&","&LISTS_ALL!$C$3:$C$2508,LISTS_ALL!$G$3:$H$2508," ") and press Enter.
- 4. Verify and fill results: Check the returned values, then drag the fill handle down to apply the lookup to other rows.
Frequently Asked Questions
Why does the XLOOKUP formula return no match?
Check that the delimiter is identical in the lookup value and source array, then remove unintended spaces with TRIM if needed. Also verify that B3 and C3 use the same text or number formatting as LISTS_ALL columns B and C.
Can XLOOKUP return values from two columns at once?
Yes. Set the return range to LISTS_ALL!$G$3:$H$2508. WPS Spreadsheets versions that support dynamic arrays can spill both returned columns into adjacent cells, which must be empty.
What if my WPS version does not calculate concatenated XLOOKUP arrays?
Create a helper key column on LISTS_ALL with =B3&","&C3, fill it down, and use that column as the XLOOKUP lookup array.
Can I use a delimiter other than a comma?
Yes. Replace the comma in every concatenation with a delimiter that does not appear in either field, such as a vertical bar: B3&"|"&C3. Use the same delimiter in the source and destination formulas.
