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 hub will explain how to create the scrolling text credits from the Star Wars movies in your PowerPoint presentation. To create the Star Wars credits we will use different animation effects simultaneously. ...
Form controls can be added to an Excel spreadsheet to create interactivity with the user. This article looks at using option button controls to allow a user to choose the data they want to see on a chart. The first...
Brock Lesnar is a phenomenon of a man and an athlete. Brock Lesnar weighs a lean 265 lbs and yet possesses extreme speed, agility and athleticism. How a man carrying so much muscle mass can maintain such high levels of...