Ever searched for something, but couldn’t find it anywhere in the places that you were looking for? The same might happen in MS Excel too! One can search for a particular entity, be it a text or a number or a date within a table, only to find out that it was never there in the first place.
MS Excel gives a bit of thought to this & searches for ways in which the search term ‘Not Available’ can be conveyed.
A moment of the light bulb! – ‘Not Available’ is reduced in size to #N/A and is displayed in the cell where the search results were supposed to be returned.
Now you know the story behind how #N/A was coined as an error indicator, it is time that we get on with exploring the ways of sorting it out and we are going to see just that in this article!
Fixing the #N/A Error in Excel:
Before we dive further into fixing this error, let us have an understanding on which formula this error is most prominent to occur. Reiterating the explanation given above, when a particular search term ain’t available within the data tabulated, it results in #N/A error to be displayed in that cell.
This can further be traced back to the formulae which are deployed for looking up a particular entity – Yep! You’ve guessed it right! The #N/A error occurs predominantly in formulas used to lookup search terms such as,
- VLOOKUP
- HLOOKUP
- MATCH
- MATCH-INDEX
Let us make use of the following dataset to demonstrate when #N/A error occurs & the different ways of handling it.
We shall get started by finding the region to which ‘Company A’ belongs, in the list of data given above and the formula of our choice shall be the VLOOKUP. The formula is constructed as shown below.
Once done, hit ENTER & you might be in for a surprise!
Those who might have paid a bit more attention would have figured out at the very start that there was no ‘Company A’ in the list of available data.
Now that we have it, what shall we do about it? The very first thing is a no-brainer – searching for only the terms available in the tabulated data. But, should such a necessity rise, there are a couple of ways to keep the #N/A error in check.
- Using IFERROR
- Using IFNA
Method 1 – Using IFERROR:
MS Excel is very nice that it has exclusive formulae to help us handle the errors that result from other formulae. One such formula is IFERROR whose syntax is as follows.
=IFERROR (Value, Value_if_error)
Under the ‘Value’ portion one shall enter the formula which returns the error value, which in this case is the VLOOKUP followed by a comma (,) after which the value to be displayed if the formula results in an error is to be entered, for instance, “Data Not Available” (remember to include the double quotes too!).
Method 2 – Using IFNA:
Another gift from MS Excel in dealing explicitly with the #N/A error is the IFNA formula, which shall display the default message which we set, should the formula result in an error. Following is its syntax,
=IFNA (Value, Value_if_na)
Similar to the approach adopted above the lookup formula is to be entered in the ‘Value’ section followed by a comma (,) after which the default message is to be entered within double quotes.
Summary
Now that we have reached the end of this article, hope it has elaborated on the ways to tackle #N/A error in MS Excel. Here’s another article which details how to sort out #NAME error. There are numerous other articles too in QuickExcel that can come in handy for those who are in looking to level up their skills in MS Excel. Cheers!