How to Fix XLOOKUP Displaying as Text in Excel & WPS
Question details
The user's XLOOKUP formula with multiple criteria is displaying as a literal text string in the cell rather than calculating and returning a result.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Entering a complex XLOOKUP formula using multiple criteria to fetch data from another table or worksheet.
- Observed behavior
- The formula string is displayed across the cells instead of calculating the value, even though other formulas like COUNTIF work properly in the same worksheet.
Ensure that your spreadsheet software is fully updated to a version that supports the XLOOKUP function, as older versions may not recognize it and will treat the formula as text by default.
Change Cell Format to General and Re-evaluate
Cells formatted as 'Text' will prevent formulas from calculating. Switching to 'General' resolves the most common cause of this issue.
When a cell is pre-formatted as Text, any formula entered into it will be treated as a literal string. Merely changing the format to General is not enough; you must force the spreadsheet to re-evaluate the cell content.
Click on the cell where the XLOOKUP formula is displaying as text.
Go to the 'Home' tab on the top ribbon, find the Number Format dropdown menu, and select 'General'.
Double-click the cell to enter Edit mode (or press F2 on your keyboard), and then press the 'Enter' key. The formula should now calculate properly.
Turn Off 'Show Formulas' Mode
If 'Show Formulas' is accidentally enabled, all cells in your worksheet will display their underlying formulas instead of results.
Remove Leading Characters from the Formula
A leading space or apostrophe tells the spreadsheet to explicitly treat the cell contents as plain text, bypassing any calculation.
Use XLOOKUP Effortlessly in WPS Office
WPS Spreadsheet fully supports modern array functions like XLOOKUP with multiple criteria. Experience seamless formula calculations and excellent compatibility with Microsoft Excel files without worrying about formatting errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Enter the formula: Select an empty cell formatted as General, and type =XLOOKUP( to trigger the formula tooltip.
- 3. Input the multiple criteria: Enter your lookup value, lookup array using boolean logic (e.g., (Range1=Criteria1)*(Range2=Criteria2)), and return array.
- 4. Calculate the result: Press Enter, and WPS Spreadsheet will instantly calculate and display the correct result instead of text.
Frequently Asked Questions
How do I write an XLOOKUP formula with multiple criteria?
You can use boolean logic by multiplying criteria within the lookup array argument. For example: =XLOOKUP(1, (Table[Col1]="Value1") * (Table[Col2]="Value2"), Table[ReturnCol]). This creates an array of 1s and 0s where the lookup value searches for the exact match (1).
Why do COUNTIF and MAXIFS work but XLOOKUP does not?
If older spreadsheet software is used, XLOOKUP might not be natively supported, causing the application to fail to evaluate it and potentially display it as text or an error. Ensure you are using a recent version of Excel or WPS Office that includes XLOOKUP.
What if changing the format to General still doesn't fix it?
After changing the cell's format to General, the cell will not automatically recalculate the existing text string. You must double-click the cell to enter edit mode (or press F2), then press Enter to trigger the calculation.
Does XLOOKUP work across different worksheets?
Yes, XLOOKUP works perfectly across different worksheets and even different workbooks. Just ensure your sheet references are correctly formatted in the formula, such as Sheet2!A2:A10.
