## Explanation

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.