where “complete” is the named range B5:B15. Note: this is an array formula and must be entered with control + shift + enter. Here, the MATCH function is used to compare all “complete” values against the partial list. The named range “complete” is used for lookup values, and the partial list is used as the lookup array. Notice, however, that the partial list is entered as an expanding range that ends “one cell above” the formula cell. This allows the partial list to expand to include new values as they appear beneath the original list. The result of MATCH is an array of numbers and #N/A errors, where numbers represent values in the complete list which exist in the partial list; and errors represent missing values: The ISNA function is used to convert these results into an array of TRUE and FALSE values. In this array, TRUE corresponds to missing values and FALSE corresponds to existing values: The ISNA function returns this array to the outer MATCH as the lookup array. The MATCH function always returns the first match found, so match will return the position (row) of the first missing value found. This result is returned to INDEX as the row number, with the named range “complete” provided as the array. In cell D12, the first missing value found is “kiwi” at row 2, so we have: In D13, “kiwi” is now included in the expanding reference, so the first missing value is “pear”: And so on. Once the all missing values have been added, the formula will return the #N/A error.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
