How to Use XLOOKUP Across Multiple Columns to Return a Completion Date in WPS Spreadsheets
Question details
The user needs to look up a reference ID that may appear in one of three columns on a raw-data worksheet and return the matching completion date.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook has a raw-data worksheet with reference IDs spread across columns A through C and completion dates in column F. The user wants to enter an ID in A2 and pull the corresponding completion date into another cell.
- Observed behavior
- XLOOKUP with only one lookup column may return blank or incorrect results because the reference ID can exist in multiple columns; a combined lookup array is needed to search all three columns.
Make sure the raw-data worksheet is named Raw and contains the reference IDs in columns A through C and completion dates in column F. Place the reference ID to search for in cell A2 on the summary worksheet before entering the formula.
Use XLOOKUP with XMATCH and ISNUMBER to Search Multiple Columns
Use this method when the reference ID can appear in any of the three columns A, B, or C and must be matched exactly before returning the completion date from column F.
This formula turns the three-column search into a TRUE/FALSE test. XMATCH checks whether the ID in A2 exists anywhere in Raw!A2:C100, ISNUMBER converts the result to TRUE or FALSE, and XLOOKUP returns the corresponding completion date from Raw!F2:F100 when TRUE is found.
Click the cell where you want the completion date to appear, such as the cell next to your reference ID on the summary worksheet.
Type =XLOOKUP(TRUE,ISNUMBER(XMATCH(A2,Raw!A2:C100)),Raw!F2:F100,"") and press Enter. Adjust Raw!A2:C100 and Raw!F2:F100 to match the exact ranges in your workbook.
Check that the formula returns the completion date for the correct reference ID. If it is blank, confirm the ID exists in the raw-data range and that both ranges have the same number of rows.
Use XLOOKUP with a Wildcard Match for Embedded Reference IDs
Use this method when the reference ID is in a single column but may be embedded inside longer text, such as a notes field or combined cell value.
Use XLOOKUP Across Multiple Columns in WPS Spreadsheets
WPS Spreadsheets supports XLOOKUP, XMATCH, and array formulas, so you can search multiple columns and return a completion date without switching tools. Open your workbook in WPS Spreadsheets, adjust the formula ranges, and the result updates automatically.
- 1. Open the workbook: Launch WPS Spreadsheets and open the .xlsx workbook that contains the raw-data worksheet and the summary worksheet.
- 2. Select the result cell: Click the cell where you need the completion date, for example the cell next to the reference ID in A2.
- 3. Enter the multi-column XLOOKUP formula: Type =XLOOKUP(TRUE,ISNUMBER(XMATCH(A2,Raw!A2:C100)),Raw!F2:F100,"") and press Enter. Adjust the ranges if your raw data has more or fewer rows.
- 4. Confirm and fill down: Check the returned completion date, then drag the fill handle down to apply the formula to additional reference IDs.
- 5. Save the file: Press Ctrl+S or click Menu > Save to keep the workbook in .xlsx format.
Frequently Asked Questions
Can XLOOKUP search multiple columns at once in WPS Spreadsheets?
Yes. Use =XLOOKUP(TRUE,ISNUMBER(XMATCH(A2,Raw!A2:C100)),Raw!F2:F100,"") to test columns A through C and return the matching value from column F.
Why does my XLOOKUP formula return blank when the ID exists?
Usually the lookup and return ranges are not the same size, the ID has leading or trailing spaces, or the match mode is not correct. Clean the raw data with TRIM and make both ranges equal in height.
How do I return a completion date if my reference ID is embedded in text?
Use a wildcard XLOOKUP such as =XLOOKUP("*"&A2&"*",Raw!A2:A100,Raw!F2:F100,"",2). This searches for the ID inside a cell and returns the corresponding completion date.
Is XLOOKUP available in WPS Spreadsheets?
Yes. WPS Spreadsheets includes XLOOKUP and XMATCH in current versions, so you can open .xlsx workbooks and use these functions without converting files.
