How to avoid #N/A error in VLOOKUP/HLOOKUP functions.(Microsoft Excel)
When a full match search is performed with the VLOOKUP/HLOOKUP functions (when FALSE is specified as the 4th argument), a #N/A error occurs if there is no match in the search value.
If the #N/A error cell is included in another formula or function, that will also result in a #N/A error.
How to avoid.
Use IFERROR function.
When used for the VLOOKUP function, the formula is as follows.
=IFERROR(VLOOKUP function, Missing case)
In this example, the missing case is set to 0.
The following results are also valid if not found.
- Blank
- Notification message
In the example below, an error message is displayed.
I only want to deal with cases where there are no search results.
The IFERROR function handles all errors.
However, if an error other than #N/A error occurs, it means that there is another anomaly than the absence of the search result.
You may want to detect that as another error.
Use the IFNA function if you want to deal only with N/A errors.
The IFNA function is used in the same way as the IFERROR function.
=IFNA(VLOOKUP function, Missing case)
XLOOKUP function
The XLOOKUP function allows the user to specify a value for no search result in the argument.
This improvement allows the XLOOKUP function alone to achieve this without using the IFNA or IFERROR functions.
=XLOOKUP(Search value,Range to search,Range to result,Missing case)
---