Vergleichen Sie Listen mit VLOOKUP
Eine häufige Anforderung in Excel ist die Notwendigkeit, zwei Listen zu vergleichen. In diesem Tutorial vergleichen wir Listen mit VLOOKUP.
Möglicherweise müssen Sie zwei Listen vergleichen, um fehlende Datensätze hervorzuheben, übereinstimmende Datensätze hervorzuheben oder einen Wert zurückzugeben.
Mit der Funktion VLOOKUP können Sie Listen in Excel vergleichen, indem Sie nach einem Wert aus einer Liste in einer anderen suchen. Sie können dann die erforderlichen Maßnahmen ergreifen.
Dieses Tutorial zeigt, wie man Listen mit VLOOKUP vergleicht. Die VLOOKUP-Funktion kann mit anderen Formeln oder Excel-Funktionen verwendet werden, um unterschiedliche Ergebnisse beim Vergleich zweier Listen zu erzielen.
Kombinieren Sie mit der IF-Funktion, um einen Wert zurückzugeben
Angenommen, wir haben zwei Kundenlisten wie das Bild unten. Beide Listen sind identisch mit der Ausnahme, dass Liste 1 weniger Kunden hat als Liste 2.
Wir wollen die beiden Listen vergleichen und die Unterschiede identifizieren. Das ID-Feld wird verwendet, um beide Listen zu überprüfen, da es sich um ein eindeutiges Feld handelt.
In diesem ersten Beispiel suchen wir die Kunden aus Liste 1 in Liste 2 und geben den Text „Nicht gefunden“ zurück, wenn sie fehlen. Wenn sie in Liste 2 gefunden werden, halten wir die Zelle leer.
Die folgende Formel verwendet die Funktion VLOOKUP zusammen mit den Funktionen IF und ISNA, um dieses Ziel zu erreichen.
Werfen wir einen Blick auf die Rolle, die jede Funktion in dieser Formel spielt.
NACHSCHLAGEN: Die Funktion VLOOKUP wurde verwendet, um nach dem Kunden in der zweiten Liste zu suchen und die Kunden-ID zurückzugeben (in der Formel als 1 angegeben).
Wenn die VLOOKUP-Funktion den Kunden nicht finden kann, wird die Fehlermeldung #N / A zurückgegeben. Dies ist, was uns interessiert, da es bedeutet, dass der Kunde auf Liste 1, aber nicht auf Liste 2 ist.
ISNA: Die ISNA-Funktion wird verwendet, um die von VLOOKUP zurückgegebene #N / A-Nachricht zu erkennen und in True zu übersetzen. Wenn eine ID zurückgegeben wird, gibt sie False zurück.
WENN: Die IF-Funktion zeigt den Text „Nicht gefunden“ an, wenn ISNA True zurückgibt, und hält die Zelle leer, wenn ISNA False zurückgibt. Diese Funktion wird verwendet, um basierend auf den Ergebnissen von VLOOKUP beliebige Aktionen auszuführen.
Vergleichen Sie Listen mit VLOOKUP, um fehlende Datensätze hervorzuheben
Diese Formel kann auch mit bedingter Formatierung verwendet werden, um die Unterschiede zwischen den beiden Listen hervorzuheben, anstatt einen Wert zurückzugeben.
Dieses Beispiel zeigt, wie die Zellenfarbe der gesamten Zeile der Tabelle geändert wird, wenn ein Kunde in der zweiten Liste fehlt. Wenn Sie die gesamte Zeile und nicht nur eine einzelne Zelle formatieren, können Sie die fehlenden Datensätze klarer identifizieren, insbesondere in einer Tabelle mit vielen Spalten.
- Wählen Sie alle Zellen der Tabelle mit Ausnahme der Kopfzeile aus.
- Klicken Sie auf der Registerkarte Start des Menübands auf Bedingte Formatierung, und wählen Sie Neue Regel aus.
- Wählen Sie Formel verwenden aus, um zu bestimmen, welche Zellen in der oberen Hälfte des Fensters formatiert werden sollen. Geben Sie in der unteren Hälfte die folgende Formel in das dafür vorgesehene Feld ein.
- Klicken Sie auf die Schaltfläche Formatieren und wählen Sie die Formatierung aus, die Sie auf die Zeile der fehlenden Datensätze anwenden möchten
Vergleichen Sie zwei Listen und markieren Sie übereinstimmende Datensätze
Um die beiden Listen zu vergleichen und die übereinstimmenden Datensätze hervorzuheben, muss der vorherigen Formel die Funktion NOT hinzugefügt werden.
=NOT(ISNA(VLOOKUP($A2,'Customers 2'!$A:$F,1,FALSE)))
Die NOT-Funktion wird verwendet, um das Ergebnis des VLOOKUPS umzukehren. Anstatt die fehlenden Datensätze hervorzuheben, werden die Übereinstimmungen hervorgehoben.