Function Problems

How to Make XLOOKUP Return Specific Text When a Matching Cell is Blank

Aamir Naveed Akram Oct 9, 2026 868 views

Question details

The user needs a formula that outputs specific text instead of a blank space when an XLOOKUP search finds a match but the corresponding return cell is empty.

How to Make XLOOKUP Return Custom Text for Blank Matching Cells

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Pulling data across different worksheets where some of the target cells contain no data, and a descriptive label is needed instead of a blank output.
Observed behavior: The standard XLOOKUP formula returns an empty blank result when the matching cell on the other worksheet is empty.

Before you start

Ensure your version of spreadsheet software supports both the XLOOKUP and LET functions, as these are available in newer versions such as Microsoft 365 and modern WPS Office builds.

Solution 1Recommended

Use the LET and IF Functions with XLOOKUP

Combine XLOOKUP with the LET and IF functions to evaluate the result once and replace any blank outcomes with your custom text.

Using the LET function is the most efficient way to handle this problem because it calculates the XLOOKUP only once, stores it in a variable, and then checks if it is blank.

Select the target cell

Click on the cell where you want the lookup result to be displayed.

Enter the combined formula

Type the formula: =LET(r, XLOOKUP(A2, 'Archer Search Report'!A:A, 'Archer Search Report'!F:F), IF(r="", "Not yet completed", r)) and adjust the sheet names and cell references to match your data.

Apply the formula

Press Enter to calculate the result. You can then click and drag the fill handle at the bottom-right corner of the cell to apply this formula to the rest of your column.

Use the LET and IF Functions with XLOOKUP

How this works: The LET function assigns the XLOOKUP calculation to the variable 'r'. The IF function then checks if 'r' equals empty text (""). If it is empty, it outputs 'Not yet completed'; otherwise, it outputs the actual value found by XLOOKUP.

Solution 2

Use a Standard IF Statement (For Older Versions Without LET)

If your software supports XLOOKUP but does not support the LET function, you can use a traditional IF function to achieve the same result.

Efficient Spreadsheet Data Processing

Use WPS Spreadsheet for Advanced Formula Lookups

WPS Spreadsheet fully supports advanced functions like XLOOKUP, LET, and IF. It provides a lightweight, highly compatible environment to handle massive datasets and complex cross-sheet calculations with ease.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your cross-worksheet data.
2. Input the LET formula: Click your target cell and input =LET(r, XLOOKUP(A2, Sheet2!A:A, Sheet2!F:F), IF(r="", "Not yet completed", r)).
3. Fill the data: Press Enter and double-click the cell's fill handle to apply the custom blank-handling logic to the entire column.

Fully compatible with Microsoft Excel formulas and functionsNative support for modern functions like XLOOKUP, LET, and dynamic arraysLightweight architecture for faster file loading and calculationFamiliar user interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the 'if_not_found' argument in XLOOKUP instead of IF and LET?

No. The 'if_not_found' argument only triggers when the search value itself is entirely missing from the lookup array. If the search value exists but the corresponding return cell is simply empty, XLOOKUP considers it a successful match and returns a blank, which is why wrapping it in an IF statement is necessary.

Why is my XLOOKUP formula returning a 0 instead of a blank?

By default, spreadsheet applications may evaluate an empty target cell as a zero in certain formatting contexts. If your lookup is returning a zero, you can adjust the IF condition to check for zero as well: =LET(r, XLOOKUP(A2,A:A,F:F), IF(OR(r="", r=0), "Not yet completed", r)).

What if my software does not support the XLOOKUP function at all?

If you are using a legacy version of a spreadsheet application, you can use a combination of VLOOKUP and IF. For example: =IF(VLOOKUP(A2, 'Sheet2'!A:F, 6, FALSE)="", "Not yet completed", VLOOKUP(A2, 'Sheet2'!A:F, 6, FALSE)).