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

excel match function examplesexcel currency formathighlight duplicates in excel 2007offset excel exampleremove duplicate cells in excelmicrosoft excel vlookup exampleshort cut in excelnormal distribution graph on excelconverting adobe to excelinsert numbers in excelexcel vlookup formulas with examplesformula for excel spreadsheetsmultiple if excel formulahot to use vlookupvlookup trueucla kinross gymtrend chart in excelhow to create bin range in excelhow to do a random number generator in excelisna formula excelshortcut for symbols in excelexcel graph columnpricing model template in excelprobability distribution graph excelvlookup sample fileformulas on excel spreadsheetmicrosoft excel if statementsdefine formula in excelvlookup simplifiedvlookup example excel 2010removing duplicates in excel 2007nested formulas excelcopy formatting excelhow to do hlookup in excelucla anderson gmatexcel find matching values in two columnsvlookup vs index matchucla gshipexcel dedupe formulatesting normality in excelucla ticket office universal studiosexcel shortcutshortcut keys excelashe center uclaexcel password templateexcel v lookupzs associates careers indiahow to combine duplicate rows in excelif isna formulaexcel cell rangesstacked bar chart in excel 2010using v lookupexcel formula cheat sheet pdfthe goal eliyahu goldrattgreater than formula in excelhow to calculate tax in excelcreate a histogram in excelexcel label chartexcel lookup not workingnumbers vlookupucla business cardsexcel deduplicationhow to make stacked bar graph in excelhlookupsrandom partner generatorhow to graph a histogram in excelhow to title columns in excelcopy duplicates in excelhow to remove duplicates in excel 2007how to highlight duplicate values in excelrandom name chooser generatorvlookup with concatenatecombine vlookup and hlookuphow to subtract multiple numbers in excelexcel sort remove duplicateshow do you write a formula in excel