Return to Excel Formulas List


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


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