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.
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.
Other VLookup Tutorials
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))