How to Use IF ISNA to Hide VLOOKUP Errors

by Matthew Kuo on November 29, 2015

in Excel, Visual Design

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the


When writing a series of VLOOKUP formulas, one of the annoying things is having to see the “#N/A” error after Excel has determined a lookup value is not available.  While we don’t want to show any values when they are truly unavailable, from a visual design perspective, it’s sometimes better just to show a blank space or a “not found” message.  Doing so makes the output look more polished and visually appealing.  It also draws less attention to the error values and lets you focus on the values that you have actually found.


The best way to mask “#N/A” errors is by using the IF ISNA formula combination.  It’s important to note that you can also use IFERROR to perform the same task, and using IFERROR does require fewer inputs.  However, IFERROR is prone to causing errors because it will mask all error types, including the ones that are not “#N/A” errors.  Therefore, using IF ISNA is what I recommend.

The ISNA Formula

= ISNA ( value )

The ISNA formula by itself is very simple.  All it does is return a “TRUE” or “FALSE” value based on whether or not an input value is equivalent to the “#N/A” error.

For example, if I were to write the ISNA formal to reference one of the “#N/A” values from our previous table, it would return with a “TRUE” result.  If I were to write the same formula for another cell, that didn’t have the “#N/A” error, the result would be “FALSE”.


The IF ISNA Formula Combination

= IF ( ISNA ( original formula ), value_if_error, original formula )

To use the IF ISNA formula combination, you just need to wrap the ISNA formula inside an IF logic condition.

The key to using the IF ISNA formula combination is that you need to put in your original formula twice

Writing your formula twice makes the process take a bit longer, but it is a necessary step to get the formula to work.  Below we’ll go through an example with VLOOKUP.


Objective:  To write a VLOOKUP formula returning multiple values while masking the “#N/A” error whenever it comes up

Step 1:  Start your IF Statement


Step 2:  Start your ISNA Statement


Step 3:  Write your original intended formula

In this case it is a basic VLOOKUP formula.  Click here for a tutorial on VLOOKUP.


Step 4: Close out your ISNA Statement

This is important because you need to add another parenthesis after you’ve finished your VLOOKUP formula.  This is a very easy part to miss because your VLOOKUP also ends in a parenthesis.  After putting in the parenthesis, close out the logical_test with a comma.


Step 5: Enter your error condition

The error condition is what we want to show up in place of the “#N/A” error, or the value_if_true within the IF Statement.  For this example we’ll use a ” – ” to indicate the value is not available, as for this particular exercise, we’ll essentially assume countries that do not show up in the source data have a value of 0.


Step 6: Re-enter your original formula (which is your non-error condition)

The easiest way to do this is to copy and paste your original formula over into the last portion of the syntax.  Be careful about the number of parentheses you include.


Step 7: Close out your IF Statement

Finish your IF Statement with a final parenthesis.


Step 8: Copy your completed formula down

Make sure that you’ve reference locked properly within the VLOOKUP formula.  Once that has been confirmed, all you need to do is double click the lower right hand corner of the cell you want to copy down.  This final view gives you a picture of what your data set looks like with all of the “#N/A” errors masked.


What Excel Does

The logic for Excel is really simple here.  If your VLOOKUP formula returns a “#N/A” error, Excel will show whatever value you input for your error condition.  If your VLOOKUP formula does not return a “#N/A” error, then Excel will show whatever value your VLOOKUP was originally intending to return.  It’s important to note that Excel looks ONLY for the “#N/A” error, not any other errors that might come up.

Additionally, it should also be noted that this whole process of masking errors also works exactly same with the formula as well.


Using IF ISNA to mask errors is a popular trick to improve the visual output of your VLOOKUP summaries.  When writing the IF ISNA formula combination, remember that you’ll need to write your original formula twice.  You also need to be careful about the number of parentheses you include when writing the formula, as making a mistake here is very easy.  (Sometimes Excel will catch parentheses mistakes for you, but sometimes it won’t)  Overall, while it does take a bit more time to write than just using the formula itself, doing so is generally worth it if you’re using those outputs for an important meeting or deliverable.

{ 11 comments… read them below or add one }

Alfred December 2, 2015 at 6:58 am

This is really helpful. Surprising that this info came in handy few days after I was asked to help eliminate the preponderance of the “#N/A” in an excel sheet with data derived from the Vlookup formula. Thanks.


Matthew Kuo December 5, 2015 at 6:36 pm

Glad I could help Alfred.



February 9, 2016 at 7:08 am

It’s a real plaeusre to find someone who can think like that


Adam March 7, 2016 at 12:11 pm

I use this tactic often. I’m a fan of using something like “_NA_” or “_NotFound_”. The underscore prefix then puts this value at the top of your selection list if you’re also using AutoFilters (which I often am). This is extremely helpful if you’re looking for that value and your list of possible values is extremely large, and especially if the values are text values (as opposed to numerical). The underscore suffix is meaningless, I’m just a fan of the symmetry.


JOHN SHERVELL March 31, 2016 at 5:09 am

great instruction
my problem is I have two lists
one a short lists of names with (golf handicaps) in the following cell
a master list of names also with handicaps in a following cell
I am trying to check that the name in the small list where it is in the master list
and if found put the handicap from the small list in the master list.
ive tried vlookup and match and index but I cannot get it right I always finish with an error of some sort.
I can find the name in the master from the small list(using match) but the changing handicap bit is defying me.
don’t know if this helps but the names and handicaps in the short list will always change whilst the master wont.
I do it this way because the match will always find the name in the master, so no error.
please help me with the formula


July 6, 2016 at 5:07 am

Very logical approach, I have found it easyto follow. Thank you.


Robert July 11, 2016 at 2:26 pm

Thanks this article I manage to solve my problem picking up a true number, within 3 cell keeping the row order priority.
This is my little samples:


Julian August 9, 2016 at 9:05 pm

Hi Matthew

The masking of the #N/A errors is great and i use it often. However if you then wish to total or count the column it counts all cells, even those that appear blank. So it is like the blank masked cells actually are not blank.
Any ideas on how to eliminate the issue?

Thanks Julian


Lisa September 11, 2016 at 2:13 pm

This solved a BIG problem for me. I am using an indirect function to refer to a cell which is the name of the sheet I want referenced. All of the sheets do not necessarily have the same rows, so I expected some #n/a issues. Using this ISNA solved my problem! I chose to use 0 instead of “-“, which allows me to sum columns with no issues. So, THANK YOU!!!
, If you want the #n/a cells to appear blank but still be addable, you could use formatting (like accounting that shows 0 as – or maybe even a conditional format that changes all 0 values to white font.


Vivek Sinha November 24, 2016 at 8:39 pm

Superb!!!! Thanks very much, Lisa. It really helped 🙂


Emma December 20, 2016 at 2:32 pm

How do I use this formula to return a “-” if the result is #n/a or 0?


Leave a Comment

Previous post:

Next post:

Related pages

vlookup excel column index numberspark notes the goalcompare two columns in excel 2010 for differencesucla anderson owaexcel spreadsheets formulas and functionshow to use iserror with vlookupexcel convert functionexcel template graphdraw bar graph in exceltable pivot excelremoving duplicates from exceladobe pdf to excel converteropen to buy excel templateexcel spreadsheet formula cheat sheetlearn excel shortcutsremove duplicates excel 2013how to do a nested if statementexcel greater than formularandomizer in excelthe goal by eliyahu goldratt chapter summarycheckmark iconremove duplicates from excel spreadsheetucla bruinbushow to convert an excel document to pdfgoogle charts stacked bardiscount tablets for studentsmost complex excel spreadsheetsample data sets for excelbar chart in excel 2007primo pdf reviewsample excel formulasexcel shortcuts and formulas pdfsumit bansalexcel chart percentage and valuesum columns in excelwhat is the function of vlookupexcel parenthesesexcel compare two columns for matchesexcel macro generatorinside adsenseexcel two way lookupdata table excel sensitivity analysiswhat feature is used to delete a conditional formatting rulewww gpprime netvlookup definationcreate a stacked bar chart in excel 2010microsofthup legitzs associates bostonhbs casesusing offset function in excelif iserror excelexcel zero blankirs 2015 withholding calculatormultiple rows in pivot tablecool excel charts and graphshow to do multiple if statements in excelmost complex excel spreadsheethow to use nested if function in excel 2010excel delete multiple rowsucla mba andersonplotting bar graphs in excelhow to create bell curve in excel 2010sheet formula excelexcel column headershow to subtract two columns in excelrandbetween without duplicateshow vlookup works in excel 2010excel bar graph labelsadobe converter pdf to excelbookstore near uclavloopup in excelexcel charts templatesmicrosoft access 2013 free trialp&l model templatecreating a histogram in excelresource calendar template excelexcel 2013 match functionhow to make bar and line chart together in excelstacked column graph excel 2010