This is an array formula, and must be entered using Control + Shift + Enter (CSE).
How this formula works
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:
If you want to find out what values in one list are missing from another list, you can use a simple formula based on the COUNTIF function. The COUNTIF function counts cells that meet supplied criteria, returning the number of occurrences found. If...
To count cells that contain errors, you can use the ISERR function, wrapped in the SUMPRODUCT function. In the example shown, E5 cell contains this formula: = SUMPRODUCT ( -- ISERROR ( B5:B9 )) How this formula works SUMPRODUCT accepts one or more...
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, the INDEX...
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.