How to Use IF ISNA to Hide VLOOKUP Errors

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.

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

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