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.
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))
More by this Author
This article will look at how to create an FA cup draw in Excel. The Excel FA cup draw file is available for download and the functions and techniques that are used are explained. The finished spreadsheet provides...
If you are using pictures in your Excel spreadsheet you may be having some difficulty with sorting the pictures along with the other data in the list. The good news is that you can sort pictures in an Excel list just...
If you are using Microsoft Project to schedule and track your projects, at some point you will probably want to export your project data into Excel. Excel can then be used to analyse and summarise your project data...