How to handle #N/A error in VLOOKUP function
The #N/A error occurs when a given value is not found while using the Excel VLOOKUP function.
Consider the following sheet with “Product Information” data – for demo only.
If we search for a product name that does not exist in the “Product name” column while using the VLOOKUP function, the #N/A error occurs.
See the sheet below where we used this VLOOKUP formula:
Normally, this formula displays the Product price for the given product name. For example:
This formula is applied to the E13 cell, and as we entered a Product name that does exist in the range – the following error occurred:
Showing a descriptive message as the wrong value is entered
By using the IFERROR function, you may at least display a more meaningful message to the end user who has no idea about Excel formulas and should understand the mistake.
To show a descriptive message in the same cell, use the following VLOOKUP formula with the IFERROR function:
As we entered the name = Corn, this is what our sheet displayed:
Why “#Value!” error occur in VLOOKUP?
VLOOKUP function can handle up to 255 characters in the lookup_value. If you try searching for more than 255 characters, it produces #Value! Error.
We entered dummy text in our sheet’s cell and see the output:
