A while back we talked about handling errors in lookups. One problem that can occur is when there is not a match. For example, the customer did not place an order during the time period that a lookup covers. The lookup formula is valid and would return values for customers that did have sales.
With ISNA, you can screen out the errors in an otherwise good lookup and convert those errors to zero, so that your lookup results can be added.
Here is a lookup error that we created in a smaller version of a table from a prior lesson on lookups. There is no product code 47, so the product name lookup returns N/A. This is the type of error where the input should be fixed, but for this exercise, we will show you how to zero out the N/A error.
The formula in the lookup is =VLOOKUP(F5,product,2,FALSE)
To adjust the formula so that an N/A value is returned as a blank, we will wrap the ISNA function around the formula.
We use an IF statement, based on the ISNA formula. If there is an N/A error, ISNA returns a value of TRUE. We use ISNA as the test in the IF statement.
The updated formula is =IF(ISNA(VLOOKUP(F5,product,2,FALSE)),"",VLOOKUP(F5,product,2,FALSE)).
Let's look at the parts:
ISNA(VLOOKUP(F5,product,2,FALSE))- This tests the lookup to see if it returns the N/A error
""- When there is an error, then we return a blank value
VLOOKUP(F5,product,2,FALSE)- If there is no error, then we can use the lookup as before
Here is the updated result. Notice that the other cells in the Name column did not change even though we modified the formula, since they all passed the test.
Now to do the real fix. Change the product to 49 and you see the updated result.
The ISNA function is just one of several error trapping functions that you can use to convert your errors and keep things like lookups working smooth.