IFERROR (& IFNA) XLOOKUP
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.
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.
In this example, we replace the #N/A Error with the text “Value Not Found,”.
=XLOOKUP(E3,B3:B7,C3:C7,"Value Not Found")
Or we can also set it to 0:
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.
=XLOOKUP(E3,B3:B7,C3:C7,XLOOKUP(E3,'2 Sheets - Data 2'!B3:B7,'2 Sheets - Data 2'!C3:C7))
XLOOKUP & IFERROR
For arithmetic calculations involving XLOOKUP, we can use the IFERROR Function to handle all types of error.
=IFERROR(1.5*XLOOKUP(E3,B3:B7,C3:C7),"Value Not Found")
The #N/A Error handling of the XLOOKUP Function won’t work on the above scenario:
=1.5*XLOOKUP(F3,C3:C7,D3:D7,"Value Not Found")