WPS Spreadsheets: Fix XLOOKUP Results with Text and Number Mismatches
Question details
The user needs to correct inconsistent XLOOKUP results when lookup codes appear identical but are stored as different data types.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A Department column is being matched against the Table3[Code] column to return a label, but one range may contain text values while the other contains numbers or hidden spaces.
- Observed behavior
- XLOOKUP returns inconsistent results or fails to match codes that look the same.
Inspect the Department and Table3[Code] columns for left-aligned numeric-looking values, hidden spaces, and inconsistent number formats. Confirm which column should be treated as text or numbers before cleaning the data.
Match the lookup columns and clean the XLOOKUP values
Use this solution when codes look identical but XLOOKUP returns incorrect results because of text, number, or spacing differences.
XLOOKUP requires compatible lookup values. A numeric 1001 and text value "1001" can look the same in a worksheet but may not match reliably. Hidden spaces can also prevent an exact match.
In WPS Spreadsheets, select the Department column and the Table3[Code] column, then open the Home tab and check the Number Format and horizontal alignment. Values that remain left-aligned when they look numeric may be stored as text.
Select both lookup columns and use Home > Number Format to apply a compatible format. Convert the codes to numbers if they are numeric identifiers, or format both columns as Text if leading zeros must be preserved.
In a helper column, enter =TRIM([@Department]) for the Department values or =TRIM(Table3[@Code]) for the table codes. Fill the formula down, then copy the cleaned results and use Home > Paste > Paste Values over the original values if appropriate.
If a code should be numeric but is stored as text, use =VALUE([@Department]) in a helper column, fill it down, and replace the original values with the calculated results using Paste Values.
In the result column, enter =XLOOKUP([@Department],Table3[Code],Table3[Label],"Not found",0,1) and press Enter. The 0 match mode requests an exact match, while 1 searches from the first item.
Clean XLOOKUP Data Types in WPS Spreadsheets
WPS Spreadsheets lets you inspect number formats, remove extra spaces with TRIM, convert numeric text with VALUE, and use an exact-match XLOOKUP formula to return consistent results.
- 1. Inspect the lookup columns: Open the worksheet in WPS Spreadsheets and compare the Department and Table3[Code] columns using Home > Number Format and alignment controls.
- 2. Standardize the values: Use TRIM to remove extra spaces or VALUE to convert numeric text, then copy the helper-column results and choose Home > Paste > Paste Values.
- 3. Apply exact matching: Enter =XLOOKUP([@Department],Table3[Code],Table3[Label],"Not found",0,1) in the result column and press Enter.
Frequently Asked Questions
Why does XLOOKUP fail when both codes look identical?
The codes may use different data types. One value can be a number while the other is text, or one may contain hidden spaces. Check the Number Format and alignment, then standardize both columns.
How can I tell whether a numeric-looking code is stored as text in WPS Spreadsheets?
Select the cell and check its alignment and Number Format on the Home tab. Numeric-looking text is often left-aligned, while numbers are commonly right-aligned under General formatting. You can also test it with VALUE.
What formula removes spaces before an XLOOKUP?
Use TRIM in a helper column, such as =TRIM([@Department]) or =TRIM(Table3[@Code]), fill the formula down, and paste the cleaned results as values before running XLOOKUP.
How do I convert text numbers into numbers for XLOOKUP?
Enter =VALUE([@Department]) in a helper column, fill it down, then copy the results and choose Home > Paste > Paste Values. Apply the same conversion to the other lookup column if necessary.
What XLOOKUP formula performs an exact match in a WPS table?
Use =XLOOKUP([@Department],Table3[Code],Table3[Label],"Not found",0,1). The 0 argument sets exact match mode, and the final 1 searches from the first item.
