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 most popular Excel books on Amazon
 

IF ISNA 00

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.

IF ISNA 01

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”.

IF ISNA 02

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.

Using IF ISNA 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

IF ISNA 03

Step 2:  Start your ISNA Statement

IF ISNA 04

Step 3:  Write your original intended formula

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

IF ISNA 05

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.

IF ISNA 06

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.

IF ISNA 07

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.

IF ISNA 08

Step 7: Close out your IF Statement

Finish your IF Statement with a final parenthesis.

IF ISNA 09

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.

IF ISNA 10

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 INDEX MATCH formula as well.

Conclusion

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.

{ 9 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.

Reply

Matthew Kuo December 5, 2015 at 6:36 pm

Glad I could help Alfred.

Best,
Matthew

Reply

Estella February 9, 2016 at 7:08 am

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

Reply

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.

Reply

JOHN SHERVELL March 31, 2016 at 5:09 am

hi
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

Reply

Jong July 6, 2016 at 5:07 am

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

Reply

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:
=IF(ISNA(Q6),IF(ISNA(Q7),(B1-Q8),(B1-Q7)),(B1-Q6))
=IF(ISNA(Q6),IF(ISNA(Q7),(D11/Q8),(D11/Q7)),(D11/Q6))

Reply

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

Reply

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!!!
@Julian, 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.

Reply

Leave a Comment

Previous post:

Next post: