Use XLOOKUP in WPS Spreadsheets to Confirm All Trade Values Are Sold
Question details
The user needs to use XLOOKUP in WPS Spreadsheets to display an indicator only when every trade value has a Sold status, but trade serial numbers are stored as numbers in one table and as text in another, preventing reliable lookup matches.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is checking a trade workbook with serial numbers in one table and statuses in another, and wants a checkbox or indicator to appear only when all trade values are Sold.
- Observed behavior
- Lookup matches fail because trade serial numbers use mismatched data types, so the all-sold indicator does not reliably appear.
Confirm which columns store the trade serial numbers and whether they are formatted as numbers or text. Check that your WPS Spreadsheets version supports the LET, FILTER, and XLOOKUP functions.
Convert Both Trade Number Columns to Numbers and Use the XLOOKUP All-Sold Formula
Use this method when trade serial numbers are stored as numbers in one table and as text in another, and you need a single reliable indicator for all-sold trade values.
The formula first removes blank or non-numeric trade values with FILTER and ISNUMBER, then looks up each numeric value with XLOOKUP, and finally compares the count of returned Sold statuses with the number of values.
Open the WPS Spreadsheets workbook and select the column that stores trade serial numbers as text, such as the Trades[Trade Serial No.] column.
Go to the Data tab, click Text to Columns, and then click Finish. Alternatively, enter =VALUE(cell) in a helper column and paste the results as values so both lookup and source columns use the same number format.
In the indicator cell, for example N3, enter: =LET(values,FILTER(G3:L3,ISNUMBER(G3:L3)),statuses,XLOOKUP(values,Trades[Trade Serial No.],Trades[Trade Status],""),IF(AND(COUNTA(values)>0,COUNTIF(statuses,"Sold")=COUNTA(values)),"☑",""))
Press Enter and review the indicator. It should appear only when every valid trade value has a Sold status.
Verify All Trade Values Are Sold in WPS Spreadsheets
Open your trade workbook in WPS Spreadsheets, normalize the serial number columns, and use the XLOOKUP all-sold formula to display a checkbox only when every trade value has a Sold status.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click File > Open, and select the Excel workbook containing the trade tables.
- 2. Convert serial numbers to a matching data type: Select a trade serial column, go to Data > Text to Columns > Finish to convert text numbers to numbers.
- 3. Insert the all-sold XLOOKUP formula: Click the indicator cell and enter the LET/FILTER/XLOOKUP formula shown in the solution.
- 4. Save the workbook: Press Ctrl+S to save the updated workbook in Excel-compatible .xlsx format.
Frequently Asked Questions
Why does XLOOKUP not match my trade serial numbers?
XLOOKUP treats numbers and text as different values. If one column stores trade serial numbers as numbers and another stores them as text, the lookup fails until both columns use the same data type.
Can I use XLOOKUP if my trade values include blanks or text?
Yes. Use FILTER with ISNUMBER to exclude blank and non-numeric trade values before passing them to XLOOKUP, as shown in the recommended formula.
How do I convert text numbers to real numbers in WPS Spreadsheets?
Select the column, go to Data > Text to Columns, and click Finish. You can also use the VALUE function in a helper column and then paste the results as values.
Can I display a different indicator instead of the checkbox?
Yes. Replace the "☑" in the IF formula with another symbol or text such as "All Sold", "Yes", or "Complete".
