Hi - Dave here.
Happy Friday!
If you use lookup functions like VLOOKUP or XLOOKUP, one thing you need to be aware of is the possibility of encountering #N/A errors. This error occurs when a function cannot find the specified data.
This is useful information, but spreadsheet errors are scary to many people, so you may want to return a more friendly result.
The way to handle #N/A errors with VLOOKUP and XLOOKUP is different. For VLOOKUP, I recommend using the IFNA function, as shown below:
Download the workbook and read the article
You can also use the IFERROR function, but IFNA is a safer option because it only catches #N/A errors. See the article above for details.
XLOOKUP and #N/A
With XLOOKUP, you do not need a separate function to trap the #N/A error because XLOOKUP provides a special argument for this purpose. You can use the following generic syntax:
=XLOOKUP(A1,range1,range2,"Not found message")
Download the workbook and read the article
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure with practice worksheets, we offer
video training.
Have a great weekend!
Dave
