This is an array formula, and must be entered using Control + Shift + Enter (CSE).
Working from the inside out, the ISERRROR function returns TRUE when a value is a recognized error, and FALSE if not.
When given a range of cells (an array of cells) ISERROR function will return an array of TRUE/FALSE results. In the example, this resulting array looks like this:
Note that the 6th value (which corresponds to the 6th cell in the range) is TRUE, since cell B9 contains #N/A.
The MATCH function is configured to match TRUE in exact match mode. It finds the first TRUE in the array created by ISERROR and returns the position. If no match is found, the MATCH function itself returns #N/A.
Finding the first NA error
The formula above will match any error. If you want to match the first #N/A error, just substitute ISNA for ISERROR:
The COUNTIF function counts cells that meet criteria, returning the number of occurrences found. If no cells meet criteria, COUNTIF returns zero. You can use behavior directly inside an IF statement to mark values that have a zero count (i.e. values...
The SUMPRODUCT function accepts one or more arrays, multiplies the arrays together, and returns the "sum of products" as a final result. If only one array is supplied, SUMPRODUCT simply returns the sum of items in the array. In the example shown,...
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined...
The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! You can use ISERROR together with the IF function to test for errors and display a custom message, or...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.