Exceljet

Quick, clean, and to the point

Find and retrieve missing values

Excel formula: Find and retrieve missing values
Generic formula 
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Explanation 

To compare two lists and pull missing values from one list to the other, you can use an array formula based on INDEX and MATCH. In the example shown, the last value in list B is in cell D11. The formula in D12, copied down, is:

=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))

where "complete" is the named range B5:B15.

Note: this is an array formula and must be entered with control + shift + enter.

How this formula works

Working from the inside out, the core of this formula is the inner MATCH expression:

ISNA(MATCH(complete,$D$5:D11,0)

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:

{1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A}

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:

{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE}

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:

=INDEX(complete,2) // returns "kiwi"

In D13, "kiwi" is now included in the expanding reference, so the first missing value is "pear":

=INDEX(complete,5) // returns "pear"

And so on. Once the all missing values have been added, the formula will return the #N/A error.

Author 
Dave Bruns

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.