Generische Formel
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Zusammenfassung
Um zwei Listen zu vergleichen und fehlende Werte von einer Liste zur anderen zu ziehen, können Sie eine Array-Formel verwenden, die auf INDEX und MATCH basiert. In dem gezeigten Beispiel befindet sich der letzte Wert in Liste B in Zelle D11. Die kopierte Formel in D12 lautet:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
Dabei ist "vollständig" der benannte Bereich B5: B15.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Erläuterung
Der Kern dieser Formel ist der innere MATCH-Ausdruck:
ISNA(MATCH(complete,$D$5:D11,0)
Hier wird die MATCH-Funktion verwendet, um alle "vollständigen" Werte mit der Teilliste zu vergleichen. Der benannte Bereich "complete" wird für Suchwerte verwendet, und die Teilliste wird als Sucharray verwendet. Beachten Sie jedoch, dass die Teilliste als erweiterter Bereich eingegeben wird, der "eine Zelle über" der Formelzelle endet. Dadurch kann die Teilliste um neue Werte erweitert werden, die unter der ursprünglichen Liste angezeigt werden.
Das Ergebnis von MATCH ist ein Array von Zahlen und # N / A-Fehlern, wobei Zahlen Werte in der vollständigen Liste darstellen, die in der Teilliste vorhanden sind. und Fehler stellen fehlende Werte dar:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Die ISNA-Funktion wird verwendet, um diese Ergebnisse in ein Array von TRUE- und FALSE-Werten zu konvertieren. In diesem Array entspricht TRUE fehlenden Werten und FALSE vorhandenen Werten:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Die ISNA-Funktion gibt dieses Array als Lookup-Array an das äußere MATCH zurück. Die MATCH-Funktion gibt immer die erste gefundene Übereinstimmung zurück, sodass match die Position (Zeile) des ersten gefundenen fehlenden Werts zurückgibt. Dieses Ergebnis wird als Zeilennummer an INDEX zurückgegeben, wobei der benannte Bereich "complete" als Array bereitgestellt wird.
In Zelle D12 ist der erste fehlende Wert "Kiwi" in Zeile 2, also haben wir:
=INDEX(complete,2) // returns "kiwi"
In D13 ist "Kiwi" jetzt in der expandierenden Referenz enthalten, sodass der erste fehlende Wert "Birne" ist:
=INDEX(complete,5) // returns "pear"
Usw. Sobald alle fehlenden Werte hinzugefügt wurden, gibt die Formel den Fehler # N / A zurück.
