Power Spreadsheets

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

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

In this Excel XLOOKUP If Blank Return Blank Tutorial, you learn how to create an Excel XLOOKUP if blank return blank formula that:

  • Returns a blank (“”);
  • When the XLOOKUP result is a blank cell.

The Excel XLOOKUP if blank return blank formula template/structure you learn below relies on the XLOOKUP and LET functions. XLOOKUP and LET are available in Excel 2021 and later (including Excel 365).

This Excel XLOOKUP If Blank 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 blank 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 Blank 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 blank return blank example workbook

Excel XLOOKUP If Blank Return Blank Formula Template/Structure

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


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

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

(1) Call the XLOOKUP function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/xlookup-blank-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/xlookup-blank-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/xlookup-blank-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/xlookup-blank-return-blank/
=XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn)

(5) Call the LET function, and set its second argument (name_value1) to the XLOOKUP function you created in steps #1 to #4.

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

(6) Use the first argument of the LET function (name1) to assign a name to the value returned by the XLOOKUP function (created in steps #1 to #4, and specified as name_value1 argument in step #5).

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

(7) Use the third argument of the LET function (calculation) to call the IF function.

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

(8) Use the first argument of the IF function (logical_test) to:

  • Call the ISBLANK function; and
  • Set the argument of the ISBLANK function (value) to the name you assigned in step #6 (representing the value returned by the XLOOKUP function).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/xlookup-blank-return-blank/
=LET(XLookupResultName,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn),IF(ISBLANK(XLookupResultName),...))

(9) Set:

  • The second argument of the IF function (value_if_true) to a blank (“”); and
  • The third argument of the IF function (value_if_false) to the name you assigned in step #6 (representing the value returned by the XLOOKUP function).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/xlookup-blank-return-blank/
=LET(XLookupResultName,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn),IF(ISBLANK(XLookupResultName),"",XLookupResultName))


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

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

The XLOOKUP function:

  1. Searches a range or array for a match; and
  2. Returns the corresponding item(s) from a second range or array.

As a general rule: When a formula refers to an empty cell, this blank is calculated as a 0. If the item returned by the XLOOKUP function is a blank, XLOOKUP (as a general rule) returns a 0.

You can (however) create an XLOOKUP if blank return blank formula to:

  • Return a blank (“”);
  • When the XLOOKUP result is a blank cell.

The IF function allows you to:

  • Test whether the XLOOKUP function returns a blank (which, as explained above, is calculated as a 0); and
  • Return:
    • A blank (“”), if the XLOOKUP function returns a blank; and
    • The value returned by the XLOOKUP function otherwise.

You can (among others) use the ISBLANK function to test whether the XLOOKUP function returns a blank.

This basic formula template/structure (with IF, ISBLANK, and XLOOKUP) requires 2 identical XLOOKUPS:

  • One XLOOKUP when specifying:
    • The first argument of the IF function (logical_test) with the ISBLANK function; that is
    • The conditional test that checks whether the XLOOKUP function returns a blank.
  • One XLOOKUP when:
    • Specifying the third argument of the IF function (value_if_false); that is
    • The value returned if the tested condition (whether the XLOOKUP function returns a blank) isn't met.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/xlookup-blank-return-blank/
=IF(ISBLANK(XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn)),"",XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn))

This XLOOKUP if blank return blank formula template/structure:

  • Works; but
  • Uses the same XLOOKUP function twice.

The LET function assigns calculation results to names. Use the LET function to avoid the duplicated XLOOKUP by:

  • Assigning a name to the value returned by the XLOOKUP function (step #6 in the step-by-step process I describe above); and
  • Using this assigned name when working with the IF function (steps #7 to #9 in the step-by-step process I describe above).


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

Excel XLOOKUP If Blank Return Blank Example Formula

This Excel XLOOKUP If Blank 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 blank return blank example workbook

Excel XLOOKUP If Blank 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 blank return blank example formula.

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

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

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

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

Excel XLOOKUP If Blank Return Blank Example Formula

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

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

The lookup value (stored in cell K6) is “Salesperson 8”.

This value is found in row 17 (cell A17) of the column I search in with the XLOOKUP if blank return blank example formula (column A, cells A7 to A26).

The return array in the XLOOKUP if blank return blank example formula is column H (cells H7 to H26). The cell in row 17 (cell H17) is empty.

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

Excel XLOOKUP if blank return blank example formula


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

Download the Excel XLOOKUP If Blank Return Blank Example Workbook

This Excel XLOOKUP If Blank 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 blank 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 Blank 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 blank 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