Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel XLOOKUP If Not Found Return Blank in 5 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

In this Excel XLOOKUP If Not Found Return Blank Tutorial, you learn how to create an Excel XLOOKUP if not found return blank formula, where the XLOOKUP function:

  • Returns a blank (“”);
  • When it fails to find a valid match for the lookup value.

The XLOOKUP function is available in Excel 2021 and later (including Excel 365).

This Excel XLOOKUP If Not Found Return Blank Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLOOKUP If Not Found Return Blank Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Excel XLOOKUP If Not Found Return Blank Formula Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-return-blank/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,"")


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Step-by-Step Process to Create an Excel XLOOKUP If Not Found Return Blank Formula

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-return-blank/
=XLOOKUP(...)

(2) Use the first argument of the XLOOKUP function (lookup_value) to specify the lookup value. This is the value you search for in the column (when doing a VLookup) or row (when doing an HLookup) you search in (and specify in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-return-blank/
=XLOOKUP(LookupValue,...)

(3) Use the second argument of the XLOOKUP function (lookup_array) to specify the lookup array. This is the column (when doing a VLookup) or row (when doing an HLookup) where you search for the lookup value (you specified in step #2).

As a general rule: Specify the lookup array as:

  • A single-column (1 column, several rows) cell range, when doing a VLookup; or
  • A single-row (1 row, several columns) cell range, when doing an HLookup.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-return-blank/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,...)

(4) Use the third argument of the XLOOKUP function (return_array) to specify the return array. This is the column (when doing a VLookup) or row (when doing an HLookup) with the value to return.

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the return array (you specify in this step #4) is the same as that of the lookup array (you specified in step #3).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-return-blank/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,...)

(5) Set the fourth argument of the XLOOKUP function (if_not_found) to a zero-length string (“”). This ensures that:

  • If XLOOKUP fails to find a valid match for the lookup value (you specified in step #2);
  • XLOOKUP returns a blank (“”).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-return-blank/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn,"")


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

How (and Why) the XLOOKUP If Not Found Return Blank Formula Works

You use the fourth argument of the XLOOKUP function (if_not_found) to specify the text returned by XLOOKUP if it fails to find a valid match for the lookup value.

Setting XLOOKUP's if_not_found argument to a zero-length string (“”) (as in the XLOOKUP if not found return blank formula template/structure above) ensures the XLOOKUP function returns a blank (“”) if it fails to find a valid match for the lookup value.

Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Excel XLOOKUP If Not Found Return Blank Example Formula

This Excel XLOOKUP If Not Found Return Blank Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Excel XLOOKUP If Not Found Return Blank Example Worksheet

The example worksheet has 3 tables/sections with the following characteristics:

(1) Table 1 (cells A6 to H26).

The table:

  • With the data.
  • Where I search with the XLOOKUP if not found return blank example formula.

(2) Lookup value (cells J6 and K6).

The lookup value (Salesperson 800) is stored in cell K6.

(3) XLOOKUP if not found return blank example formula (cells J8 to K9).

  • Cell J9:
    • Stores the XLOOKUP if not found return blank example formula; and
    • Displays the results.
  • Cell K9 displays the XLOOKUP if not found return blank example formula I enter in cell J9.
Example worksheet for Excel XLOOKUP if not found return blank

Excel XLOOKUP If Not Found Return Blank Example Formula

The XLOOKUP if not found return blank example formula stored in cell J9 is as follows:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-return-blank/
=XLOOKUP(K6,A7:A26,H7:H26,"")

The lookup value (stored in cell K6) is:

  • “Salesperson 800”.
  • Not found in the column (column A, cells A7 to A26) I search in with the XLOOKUP if not found return blank example formula.

Therefore, the XLOOKUP if not found return blank example formula returns a blank (“”).

Excel XLOOKUP if not found return blank example formula


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Download the Excel XLOOKUP If Not Found Return Blank Example Workbook

This Excel XLOOKUP If Not Found Return Blank Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLOOKUP If Not Found Return Blank Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link).

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLOOKUP if not found return blank example workbook

Subscribe
Notify of
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure