Remove the #N/A Message from Vlookup Results

If you have used the Vlookup function in Excel before, you will probably be familiar with the #N/A message that can appear as a result of Vlookup not being able to find the value you are looking for.

The #N/A message can look untidy and unprofessional. Take the scenario below for instance. The spreadsheet below has Vlookup functions in cells H7 and I7 that will return the stock level and stock value for the product code entered into cell H4. Because cell H4 is currently empty, Vlookup has returned the #N/A error message in cells H7 and I7.

Excel Vlookup function returning the #N/A message
Excel Vlookup function returning the #N/A message

Using the ISNA Function

To remove the #N/A message, we first need Excel to detect that one is present. To do this we can use the ISNA function. This function looks to see if the #N/A message is present, and if so returns the result of True.

The formula below shows the ISNA function added to the Vlookup function in cell H7 returning the result of True.

=ISNA(VLOOKUP($H$4,$A$4:$F$9,5,FALSE))

ISNA function being use to detect #N/A error messages
ISNA function being use to detect #N/A error messages

Adding the IF Function to the Mix

Now the result of True is not really an improvement on #N/A. It’s still un ugly response that does not mean much to the general Excel user.

The next step is to add the IF function to the mix to get Excel to take a different course of action depending on if the ISNA function returns True or False.

If it returns True we would like to display the text “Product not found”, and if it returns False we would like the result found by the Vlookup function.

The formula below shows the IF function included with the Vlookup and ISNA functions. Cells H7 and I7 are both display “Product not found”. If a product code is entered into cell H4 the required stock level and stock value would appear instead.

=IF(ISNA(VLOOKUP($H$4,$A$4:$F$9,5,FALSE)),"Product not found",VLOOKUP($H$4,$A$4:$F$9,5,FALSE))

IF function displaying a different message when the Vlookup value is not found
IF function displaying a different message when the Vlookup value is not found

More by this Author


3 comments

Tom Siccardi 5 years ago

This worked great. Thanks.


Diego 5 years ago

One of the reasons why I like visiting your blog so much is because it has become a daily reference I can use in order to learn new nice stuff. It's like a curiosities box that surprises you over and over again.


Karen 4 years ago

Thanks

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working