Return to Excel Formulas List

IFERROR (& IFNA) XLOOKUP

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to handle XLOOKUP #N/A errors in Excel. If your version of Excel does not support XLOOKUP, read how to error handle VLOOKUPs instead.

iferror ifna xlookup Main

XLOOKUP: #N/A Error-Handling

Unlike VLOOKUP, XLOOKUP has the built-in option to handle #N/A Errors. By entering a value into the 4th argument, the XLOOKUP Function will replace a #N/A error with the specified value.

iferror ifna xlookup 01

In this example, we replace the #N/A Error with the text “Value Not Found,”.

iferror ifna xlookup 02

Or we can also set it to 0:

iferror ifna xlookup 03

Note: The XLOOKUP Function requires at least three arguments: lookup value, lookup array and return array. By default, the XLOOKUP Function finds an exact match from the top of the lookup array going down (i.e., top-down). Once it finds a match, it returns the corresponding value from the return array. Otherwise, it returns an error.

 

Nested XLOOKUP: 2 Sheets at Once

Instead of a customized value, we can nest another XLOOKUP in the 4th argument of the XLOOKUP Function to perform a lookup among multiple sets of data that are stored in different sheets.

iferror ifna xlookup 04

XLOOKUP & IFERROR

For arithmetic calculations involving XLOOKUP, we can use the IFERROR Function to handle all types of error.

iferror ifna xlookup 05

The #N/A Error handling of the XLOOKUP Function won’t work on the above scenario:

iferror ifna xlookup 06