Function Problems

How to Use XLOOKUP to Return Data, No Data, or N/A in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to create a formula that looks up values across different worksheets and returns the corresponding data, 'No Data' if the matched result cell is blank, and 'N/A' if the lookup value doesn't exist.

Product: Excel
Device & OS: not provided
Scenario: Comparing values across different worksheets to extract attributes like Name, Age, or Country, while handling both empty result cells and unmatched lookup records.
Observed behavior: A standard lookup either returns a 0 for blank matches or an error for missing data. A nested formula combining XLOOKUP and IF is required to output three distinct text states.

Before you start

Verify that your lookup arrays and return arrays are exactly the same size before constructing the formula, and ensure you use absolute references for ranges that shouldn't shift.

Solution 1Recommended

Use an IF Statement with XLOOKUP

By nesting your XLOOKUP function inside an IF statement, you can check whether the returned value is blank and output a custom 'No Data' message, while relying on XLOOKUP's built-in 'if_not_found' argument to handle missing matches.

When referencing data across worksheets, it's crucial to use lookup values from the current row rather than table headers to avoid reference errors.

The standard XLOOKUP function has a built-in argument for missing matches, but it doesn't automatically handle blank return cells (which usually return as 0). Wrapping it in an IF formula solves this.

Select the destination cell

Click on the cell where you want the result to appear (for example, cell B2 in your active worksheet).

Enter the nested formula

Type the following formula: =IF(XLOOKUP(A2,$A$14:$A$18,$B$14:$B$18,"N/A")="","No Data",XLOOKUP(A2,$A$14:$A$18,$B$14:$B$18,"N/A")). Make sure to replace the lookup and return ranges ($A$14:$A$18 and $B$14:$B$18) with the actual references on your other worksheet.

Apply to the rest of the column

Press Enter to calculate the result. Then, click and drag the fill handle at the bottom-right corner of cell B2 down to copy the formula to the remaining rows.

Absolute Cell References: Notice the dollar signs ($) in the ranges like $A$14:$A$18. These create absolute references, preventing the lookup range from shifting downward when you fill the formula down your column.

Advanced Formulas Made Easy

Master Complex XLOOKUP Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP, IF, and complex nested arrays. You can easily manage cross-worksheet data lookups with the exact same syntax used in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook.
2. Input your Formula: Select the target cell and type your nested IF and XLOOKUP formula just as you would in Excel.
3. Drag to Fill: Use the smart fill handle to apply the logic down your entire dataset instantly.

100% compatible with Microsoft Excel .xlsx files and standard formulas.Native support for modern functions including XLOOKUP and dynamic arrays.Smooth, lightweight performance even when processing thousands of cross-worksheet lookup formulas.Completely free for standard daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP return a 0 instead of a blank cell?

By default, spreadsheet software returns a 0 when XLOOKUP successfully finds the lookup value but the corresponding cell in the return array is completely empty. Wrapping the XLOOKUP in an IF function (checking if the result is "") is the standard way to replace that 0 with a custom text like 'No Data'.

Can I use XLOOKUP across entirely different workbooks?

Yes, XLOOKUP can reference ranges in completely different, open workbook files. The syntax remains the same, but the range reference will include the external workbook's name (e.g., '[Data.xlsx]Sheet1!$A$1:$A$10').

What if my version of Excel does not support XLOOKUP?

If you are using an older version of Excel, XLOOKUP will return a #NAME? error. You can achieve the same result using a combination of IF, ISNA, and VLOOKUP (or INDEX/MATCH). Alternatively, you can download WPS Office for free, which natively supports XLOOKUP.

Excel & Data Problems