Handling Excel VLOOKUP Errors (N/A & Value)
The #N/A error occurs when a given value is not found as using the Excel VLOOKUP function.
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:
=VLOOKUP(E13,C2:E11,2,FALSE)
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:
=IFERROR(VLOOKUP(E13,C2:E11,2,TRUE), "Product Does Not Exist!")
As we entered the name = Corn, this is what our sheet displayed:
Why #Value! error occurs 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:
