In this Excel IF XLOOKUP (for Error Handling) Tutorial, you learn how to create an Excel IF XLOOKUP (for error handling) formula, where you:
- Combine the XLOOKUP and IF functions;
- To handle (certain) potential errors (returned by XLOOKUP).
The Excel IF XLOOKUP (for error handling) 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 IF XLOOKUP (for Error Handling) 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.
Table of Contents
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).
- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet: Click here to open.
- Excel VLOOKUP Compare 2 Columns and Find Matches: Click here to open.
- Excel VLookup Sum Multiple Row Values (in Same Column): Click here to open.
- Excel VLOOKUP Multiple Columns: Click here to open.
- Excel VLOOKUP Sum Multiple Columns (Values): Click here to open.
- Excel VLookup Sum Multiple Column Values (with XLOOKUP): Click here to open.
- Excel VLookup Sum Multiple Rows and Columns: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column: Click here to open.
- Excel VLookup Return Multiple Values with the INDEX Function: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function: Click here to open.
- Excel VLookup Return Multiple Values in One Cell Separated by a Comma: Click here to open.
- Excel VLOOKUP Multiple Sheets: Click here to open.
- Excel VLOOKUP Multiple Sheets in Different Workbook: Click here to open.
- Excel VLOOKUP Sheet in Multiple Different Workbooks: Click here to open.
This Excel IF XLOOKUP (for Error Handling) Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.
- Excel XLOOKUP Tutorial: Click here to open.
- Excel XLOOKUP in Table: Click here to open.
- Excel Nested XLOOKUP (Dynamic Lookup Value): Click here to open.
- Excel XLOOKUP If Not Found Return Blank: Click here to open.
- Excel IF XLOOKUP (for Error Handling): Click here to open.
- Excel XLOOKUP If Blank Return Blank: Click here to open.
- Excel XLOOKUP Wildcard: Click here to open.
- Excel XLOOKUP Between 2 Values: Click here to open.
- Excel XLookup Return All Matches: Click here to open.
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:
- Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
- Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
- Excel Macro Tutorial for Beginners: Click here to open.
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:
- Premium Courses at the Power Spreadsheets Academy: Click here to open.
- Books at the Power Spreadsheets Library: Click here to open.
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.
Excel IF XLOOKUP (for Error Handling) Formula Template/Structure
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-if-xlookup-error/
=LET(XLookupResultName,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn),IF(IsFunction(XLookupResultName),ValueIfError,XLookupResultName))
Step-by-Step Process to Create an Excel IF XLOOKUP (for Error Handling) Formula
(1) Call the XLOOKUP function.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-if-xlookup-error/
=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-if-xlookup-error/
=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-if-xlookup-error/
=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-if-xlookup-error/
=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/excel-if-xlookup-error/
=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/excel-if-xlookup-error/
=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/excel-if-xlookup-error/
=LET(XLookupResultName,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn),IF(...))
(8) Use the first argument of the IF function (logical_test) to:
- Call an IS function (ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT); and
- Set the argument of the applicable IS function (value) to the name you assigned in step #6 (representing the value returned by the XLOOKUP function).
When creating an IF XLOOKUP (for error handling) formula, you use an IS function to test whether the XLOOKUP function returns a specific (potentially erroneous) value. If XLOOKUP returns the applicable (potentially erroneous) value, the IS function returns TRUE.
You may (theoretically) consider using the ISNA function to handle potential #N/A errors.
- The XLOOKUP function returns the #N/A error when it fails to find a valid match; and
- The ISNA function returns TRUE if its value argument refers to the #N/A error.
As a general rule: A better way to handle such potential #N/A errors is via XLOOKUP's fourth argument (if_not_found).
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-if-xlookup-error/
=LET(XLookupResultName,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn),IF(IsFunction(XLookupResultName),...))
(9) Set the second argument of the IF function (value_if_true) to the value to return if:
- XLOOKUP returns a potentially erroneous value; and (therefore)
- The IS function you use in step #8 returns TRUE.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-if-xlookup-error/
=LET(XLookupResultName,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn),IF(IsFunction(XLookupResultName),ValueIfError,...))
(10) Set 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/excel-if-xlookup-error/
=LET(XLookupResultName,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn),IF(IsFunction(XLookupResultName),ValueIfError,XLookupResultName))
How (and Why) the IF XLOOKUP (for Error Handling) Formula Works
You can combine the XLOOKUP and IF functions:
- In several ways; and
- For several reasons.
One of the main reasons to combine XLOOKUP and IF is to handle errors (cases where XLOOKUP's output is erroneous). The IF XLOOKUP (for error handling) formula template/structure you learned above helps you:
- Handle such erroneous output; and
- Identify the error's cause(s).
In this context, you can (among others) use an IS function (ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT) to test whether the XLOOKUP function returns a specific (potentially erroneous) value.
The (main) exception to this general rule is the #N/A error returned by the XLOOKUP function when it fails to find a valid match. You may (theoretically) consider using the ISNA function to handle such potential #N/A errors. A better way to do handle such potential #N/A errors is via XLOOKUP's fourth argument (if_not_found).
The basic formula template/structure (with IF, an IS function, and XLOOKUP) I describe above requires 2 identical XLOOKUPs:
- One XLOOKUP when specifying:
- The first argument of the IF function (logical_test) with the IS function; that is
- The conditional test that checks whether the XLOOKUP function returns an erroneous value.
- 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 an erroneous value) isn't met.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-if-xlookup-error/
=IF(IsFunction(XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn)),ValueIfError,XLOOKUP(LookupValue,ColumnOrRowWhereYouSearch,ColumnOrRowWithValueToReturn))
This IF XLOOKUP (for error handling) 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 #10 in the step-by-step process I describe above).
Excel IF XLOOKUP (for Error Handling) Example Formula
This Excel IF XLOOKUP (for Error Handling) 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.
Excel IF XLOOKUP (for Error Handling) Example Worksheet
The example worksheet has 3 tables/sections with the following characteristics:
(1) Table 1 (cells A6 to H26).
The table:
- With data.
- Where I search with the IF XLOOKUP (for error handling) example formula.
(2) Lookup value (cells J6 and K6).
The lookup value (Salesperson 8) is stored in cell K6.
(3) IF XLOOKUP (for error handling) example formula (cells J8 to K9).
- Cell J9:
- Stores the IF XLOOKUP (for error handling) example formula; and
- Displays the results.
- Cell K9 displays the IF XLOOKUP (for error handling) example formula I enter in cell J9.
Excel IF XLOOKUP (for Error Handling) Example Formula
The IF XLOOKUP (for error handling) example formula stored in cell J9 is as follows:
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-if-xlookup-error/
=LET(XLookupResult,XLOOKUP(K6,A7:A26,H7:H26),IF(ISTEXT(XLookupResult),"XLOOKUP returns text",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 IF XLOOKUP (for error handling) example formula (column A, cells A7 to A26).
The return array in the IF XLOOKUP (for error handling) example formula is column H (cells H7 to H26). The cell in row 17 (cell H17) contains text (“text”). I:
- Work with the ISTEXT function to identify such potential errors (where XLOOKUP returns text); and
- Have the IF XLOOKUP (for error handling) example formula return “XLOOKUP returns text” when such an error occurs.
Download the Excel IF XLOOKUP (for Error Handling) Example Workbook
This Excel IF XLOOKUP (for Error Handling) 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.
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).
- Excel VLOOKUP Tutorial (under development): Click here to open.
- Excel VLOOKUP from Another Sheet: Click here to open.
- Excel VLOOKUP Compare 2 Columns and Find Matches: Click here to open.
- Excel VLookup Sum Multiple Row Values (in Same Column): Click here to open.
- Excel VLOOKUP Multiple Columns: Click here to open.
- Excel VLOOKUP Sum Multiple Columns (Values): Click here to open.
- Excel VLookup Sum Multiple Column Values (with XLOOKUP): Click here to open.
- Excel VLookup Sum Multiple Rows and Columns: Click here to open.
- Excel VLookup Multiple Criteria with INDEX MATCH: Click here to open.
- Excel VLookup Multiple Criteria with XLOOKUP: Click here to open.
- Excel VLookup Multiple Criteria with the FILTER Function: Click here to open.
- Excel VLOOKUP Return Multiple Values with Helper Column: Click here to open.
- Excel VLookup Return Multiple Values with the INDEX Function: Click here to open.
- Excel VLookup Return Multiple Values with the FILTER Function: Click here to open.
- Excel VLookup Return Multiple Values in One Cell Separated by a Comma: Click here to open.
- Excel VLOOKUP Multiple Sheets: Click here to open.
- Excel VLOOKUP Multiple Sheets in Different Workbook: Click here to open.
- Excel VLOOKUP Sheet in Multiple Different Workbooks: Click here to open.
This Excel IF XLOOKUP (for Error Handling) Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.
- Excel XLOOKUP Tutorial: Click here to open.
- Excel XLOOKUP in Table: Click here to open.
- Excel Nested XLOOKUP (Dynamic Lookup Value): Click here to open.
- Excel XLOOKUP If Not Found Return Blank: Click here to open.
- Excel IF XLOOKUP (for Error Handling): Click here to open.
- Excel XLOOKUP If Blank Return Blank: Click here to open.
- Excel XLOOKUP Wildcard: Click here to open.
- Excel XLOOKUP Between 2 Values: Click here to open.
- Excel XLookup Return All Matches: Click here to open.
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:
- Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
- Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
- Excel Macro Tutorial for Beginners: Click here to open.
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:
- Premium Courses at the Power Spreadsheets Academy: Click here to open.
- Books at the Power Spreadsheets Library: Click here to open.
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.