Function Problems

Fix XLOOKUP Concatenation Returning TRUE Instead of Text in WPS Spreadsheets

Elise Williams Nov 13, 2026 868 views

Question details

The user needs a WPS Spreadsheets formula that combines brand, product name, and size from separate lookup columns into a formatted product description instead of returning TRUE.

Fix XLOOKUP Concatenation Returning TRUE Instead of Text in WPS Spreadsheets

Product: WPS Spreadsheets
Device & OS: not provided
Scenario: The user is building a formatted product description from a lookup sheet that contains brand, product name, and size columns.
Observed behavior: The formula returns TRUE instead of the expected text because an outer XLOOKUP uses the concatenated text in the wrong argument position, causing TRUE to be treated as an if_not_found argument.

Before you start

Open the workbook in WPS Spreadsheets and confirm that the lookup sheet contains the brand, product name, and size columns used in the formula. Verify that the lookup value cell, such as A7, matches the product identifier in the lookup range.

Solution 1Recommended

Replace the outer XLOOKUP with three separate XLOOKUP functions and concatenate the results

Use this solution when the current formula returns TRUE because an unnecessary outer XLOOKUP is misusing the concatenated string as its lookup array.

XLOOKUP expects its second argument to be a lookup array. When an outer XLOOKUP is wrapped around a concatenated text string, WPS Spreadsheets can interpret that string as the lookup array and place TRUE in the if_not_found argument position. Removing the wrapper and concatenating the results of three individual XLOOKUP functions directly returns the requested text.

Select the cell with the formula

In WPS Spreadsheets, click the cell that currently returns TRUE and place the cursor in the formula bar.

Remove the outer XLOOKUP wrapper

Delete the outer XLOOKUP function and its unnecessary arguments so the formula begins with the first XLOOKUP lookup, for example XLOOKUP(A7,Products!A2:A9359,Products!C2:C9359).

Enter the three separate XLOOKUP functions joined by ampersands

Type =XLOOKUP(A7,Products!A2:A9359,Products!C2:C9359)&" - "&XLOOKUP(A7,Products!A2:A9359,Products!D2:D9359)&" ("&XLOOKUP(A7,Products!A2:A9359,Products!I2:I9359)&")" in the formula bar.

Press Enter and verify the result

Press Enter and confirm that the cell now displays the brand, product name, and size in the formatted product description instead of TRUE.

Replace the outer XLOOKUP with three separate XLOOKUP functions and concatenate the results

Formula corrected: After this change, each XLOOKUP returns one text value and the ampersands join them into the intended description.

Fix XLOOKUP formula errors in WPS Spreadsheets

Fix XLOOKUP Concatenation Formula Errors in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and replace the outer XLOOKUP with three separate lookups concatenated with ampersands. WPS Spreadsheets supports Excel-style XLOOKUP formulas, so you can correct the formula without leaving your workbook.

1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select the Sheets module, and open the workbook that contains the XLOOKUP formula returning TRUE.
2. Select the formula cell: Click the cell with the incorrect formula and place the cursor in the formula bar.
3. Replace the formula: Delete the outer XLOOKUP and enter =XLOOKUP(A7,Products!A2:A9359,Products!C2:C9359)&" - "&XLOOKUP(A7,Products!A2:A9359,Products!D2:D9359)&" ("&XLOOKUP(A7,Products!A2:A9359,Products!I2:I9359)&")" in the formula bar.
4. Press Enter and confirm: Press Enter and verify that the cell displays the brand, product name, and size in the requested format instead of returning TRUE.

Directly fixes XLOOKUP concatenation errors without switching to another applicationSupports Excel-style XLOOKUP, ampersand concatenation, and formula editingOpens and saves .xlsx workbooks with Microsoft Office format compatibilityProvides a free, lightweight Office suite with a familiar ribbon interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return TRUE instead of text?

An outer XLOOKUP is likely being used incorrectly. When its second argument is built from concatenated text, TRUE can end up in the if_not_found argument position. Remove the outer XLOOKUP and concatenate three separate XLOOKUP results instead.

Can I combine multiple XLOOKUP results in one WPS Spreadsheets formula?

Yes. Use ampersands to join them. For example, =XLOOKUP(A7,Products!A2:A9359,Products!C2:C9359)&" - "&XLOOKUP(A7,Products!A2:A9359,Products!D2:D9359)&" ("&XLOOKUP(A7,Products!A2:A9359,Products!I2:I9359)&")" returns brand, product name, and size in one cell.

Does WPS Spreadsheets support XLOOKUP?

Yes. WPS Spreadsheets supports XLOOKUP in Excel-compatible workbooks, so you can edit and save the formula without converting the file to a different format.

How do I save the corrected workbook as an Excel file?

In WPS Spreadsheets, click the Menu button and choose Save As, then select Excel Workbook (*.xlsx) to keep the file compatible with Microsoft Office.

Excel & Data Problems