Function Problems

How to Use Excel XLOOKUP to Return TBD for Errors and Handle Dates

Emma Brown Sep 28, 2026 869 views

Question details

The user needs an XLOOKUP formula in Excel that returns "TBD" when there is an error, a missing match, or a blank result, while successfully returning valid dates for matches.

How to Use Excel XLOOKUP to Return TBD for Errors and Handle Dates

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for data across multiple sheets where lookup results might contain missing values, blanks, or specific status texts, and a clean "TBD" or valid date output is required for reporting.
Observed behavior: A standard XLOOKUP returns zero for blank results and #N/A when no match exists, failing to handle complex custom formatting requirements for missing data and dates automatically.

Before you start

Ensure your lookup and return arrays are exactly the same size. Also, format the destination cells as 'Short Date' before applying the formula so that matched date serial numbers display correctly.

Solution 1Recommended

Combine XLOOKUP with IF and IFERROR functions

Use a nested formula approach to accurately handle missing matches, blank cells, and unexpected errors while returning valid dates when data is found.

By default, XLOOKUP provides an 'if_not_found' argument to handle missing matches. However, when the lookup value exists but the corresponding return cell is completely empty, XLOOKUP outputs a 0 instead of a blank.

To properly return 'TBD' for blanks, missing values, and unexpected error types, you must wrap the XLOOKUP function in an IF statement and an IFERROR function.

Select the target cell

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

Use XLOOKUP's built-in argument

Start by constructing the base formula using XLOOKUP's 'if_not_found' argument: =XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "TBD")

Wrap with an IF statement

To prevent empty destination cells from returning a 0, wrap your XLOOKUP in an IF statement to force a 'TBD' result if the cell is blank: =IF(XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "TBD")="", "TBD", XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "TBD"))

Add IFERROR as a final fallback

Wrap the entire formula in IFERROR to catch any remaining formula calculation errors (like #VALUE! or #REF!): =IFERROR(IF(XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "TBD")="", "TBD", XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "TBD")), "TBD")

Format cell as a date

Press Enter to apply the formula. Select the cell, navigate to the Home tab, and apply a Date number format so successful matches are displayed properly instead of as raw serial numbers.

Combine XLOOKUP with IF and IFERROR functions

Performance Tip: Referencing entire columns (such as Sheet2!A:A) can occasionally slow down large workbooks. Consider using exact data ranges like Sheet2!A2:A1000 for better performance.

Solution 2

Use the LET function to simplify the formula

If you are using a modern spreadsheet application, the LET function avoids calculating the same XLOOKUP twice, making the formula shorter and faster.

Advanced Spreadsheet Solution

Process Complex Formulas Efficiently with WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP, IFERROR, and LET. It allows you to seamlessly process complex data, handle errors, and format dates accurately without compatibility issues.

1. Open your workbook in WPS: Launch WPS Office and open your dataset in the WPS Spreadsheet application.
2. Select the destination cell: Click on the specific cell where you want your filtered lookup result to appear.
3. Apply the nested formula: Type your full formula, such as =IFERROR(XLOOKUP(A2, Sheet2!A:A, Sheet2!D:D, "TBD"), "TBD").
4. Format for dates: Hit Enter to calculate, right-click the cell, choose 'Format Cells', and select 'Date' to display matched dates properly.

100% compatible with Microsoft Excel formulas and .xlsx file formats.Includes full built-in support for modern array functions like XLOOKUP and LET.Lightweight and incredibly fast, even when calculating large datasets with complex nested formulas.Completely free to use with a highly intuitive, Excel-like user interface.

microsoft office alternative - wps office

Frequently Asked Questions

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

When XLOOKUP successfully finds a match but the referenced return cell is totally empty, Excel outputs a 0 by default. To output a blank or 'TBD', you must wrap the formula in an IF statement to explicitly evaluate for blanks.

What is the difference between IFERROR and XLOOKUP's 'if_not_found' argument?

The 'if_not_found' argument only triggers when the lookup value does not exist in the lookup array. IFERROR is broader and catches all standard Excel calculation errors, such as #VALUE!, #NAME?, or #REF!, acting as a secondary safety net.

Why are the returned dates showing up as random numbers?

Dates in spreadsheets are stored as sequential serial numbers (e.g., 44197 represents a specific date in 2021). If your XLOOKUP returns a 5-digit number instead of a date, simply select the cell, navigate to the Home tab, and change the Number Format from 'General' to 'Short Date'.

Excel & Data Problems