Wie vergleicht man zwei Listen in separaten Arbeitsblättern mithilfe von VLOOKUP?
Angenommen, Sie haben zwei Arbeitsblätter mit jeweils einer Namensliste – so wie in den obigen Screenshots dargestellt. Möglicherweise möchten Sie herausfinden, welche Namen aus Names-1 auch in Names-2 enthalten sind. Ein manueller Vergleich ist – besonders bei langen Listen – nicht nur mühsam, sondern auch äußerst fehleranfällig. In diesem Artikel stellen wir Ihnen mehrere effiziente Methoden vor, mit denen Sie die beiden Listen schnell und präzise vergleichen und übereinstimmende Werte über verschiedene Arbeitsblätter hinweg zuverlässig ermitteln können.
VLOOKUP zum Vergleich zweier Listen in separaten Arbeitsblättern mithilfe von Formeln
VLOOKUP zum Vergleich zweier Listen in separaten Arbeitsblättern mit Kutools für Excel
Bedingte Formatierung verwenden mit Formel über mehrere Blätter hinweg
VBA-Code – Automatischer Listenvergleich mit Hervorhebung oder Extraktion von Übereinstimmungen
VLOOKUP zum Vergleich zweier Listen in separaten Arbeitsblättern mithilfe von Formeln
Ein praktischer und direkter Ansatz, um Listen aus verschiedenen Excel-Arbeitsblättern zu vergleichen, ist die Verwendung der VLOOKUP-Funktion. Mit dieser Methode können Sie effizient alle Namen extrahieren oder kennzeichnen, die sowohl in Names-1 als auch in Names-2 enthalten sind:
1. Wechseln Sie zum Blatt Names-1und wählen Sie eine Zelle neben Ihren Listendaten aus (z. B. Zelle)B2). Geben Sie dort folgende Formel ein:
=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)Drücken Sie anschließend Enter. Wenn der Name in der aktuellen Zeile in Names-2 vorhanden ist, gibt die Formel den Namen zurück; andernfalls wird ein #NV-Fehler angezeigt. Sehen Sie sich das folgende Beispiel an:
2. Kopieren Sie die Formel nach unten, indem Sie am Ausfüllkästchen ziehen, um jeden Namen in Names-1 mit allen Namen in Names-2 zu vergleichen. Übereinstimmende Einträge zeigen den Namen an, während nicht gefundene Einträge einen Fehlerwert anzeigen:
Hinweise:
1. Für bessere Lesbarkeit können Sie alternativ diese Formel nutzen, um „Ja“ oder „Nein“ als klare Indikatoren für Übereinstimmungen zurückzugeben:
=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")Diese Formel zeigt „Ja“ für Namen an, die in beiden Blättern vorhanden sind, und „Nein“ für Namen, die nur in Names-1vorkommen:
2. Ersetzen Sie beim Einsatz dieser Formeln A2 durch die erste Zelle Ihrer Liste, Names-2 durch den Namen des Referenzblatts und passen Sie $A$2:$A$19 an den tatsächlichen Datenbereich in Ihrem Arbeitsblatt an. Achten Sie dabei darauf, Bereiche mit der korrekten Anzahl von Zeilen festzulegen – sowohl am Anfang als auch am Ende –, um sicherzustellen, dass alle Ihre Daten berücksichtigt werden.
3. Tipps zur Anwendung: Falls #NV-Fehler auftreten, obwohl Übereinstimmungen vorliegen sollten, prüfen Sie sorgfältig auf mögliche Probleme wie zusätzliche Leerzeichen, Unterschiede in der Datenformatierung (Text vs. Zahl) oder Tippfehler in Ihren Listen. Verwenden Sie gegebenenfalls in einer Hilfsspalte GLÄTTEN oder SÄUBERN, um die Daten zu bereinigen.
4. Um versehentliche Überschreibungen zu vermeiden, sollten Sie vor dem Anwenden von Massenformeln unbedingt eine Datensicherung erstellen. Danach können Sie zudem Filter auf Ihre Formelergebnisspalte anwenden, um schnell alle Übereinstimmungen oder eindeutigen Elemente anzuzeigen.
VLOOKUP zum Vergleich zweier Listen in separaten Arbeitsblättern
Wenn Sie Kutools für Excel besitzen, können Sie mithilfe der Funktion Gleiche/Unterschiedliche Zellen auswählen dieselben oder unterschiedlichen Werte aus zwei separaten Arbeitsblättern mit nur wenigen Klicks finden und hervorheben. Diese Funktion reduziert das Risiko manueller Fehler erheblich und spart besonders bei großen Datensätzen wertvolle Zeit. Zum Herunterladen von Kutools für Excel klicken!
Kutools für Excel: Mit mehr als 300 praktischen Excel-Add-Ins – kostenlos testen ohne Einschränkung für 30 Tage.Jetzt herunterladen und kostenlos testen!
VLOOKUP zum Vergleich zweier Listen in separaten Arbeitsblättern mit Kutools für Excel
Wenn Sie Kutools für Excel besitzen, hilft Ihnen die Funktion Gleiche/Unterschiedliche Zellen auswählen, zwei Listen aus unterschiedlichen Arbeitsblättern blitzschnell zu vergleichen und gemeinsame Namen zwischen diesen beiden Blättern auszuwählen oder hervorzuheben – ganz ohne komplexe Formeln eingeben zu müssen. Diese Methode eignet sich besonders gut bei großen Datenmengen oder wenn Sie ein visuelles, farbcodiertes Ergebnis wünschen, das sich auf einen Blick leicht interpretieren lässt.
Nach der Installation von Kutools für Excelführen Sie die folgenden Schritte aus, um Ihre Listen einfach zu vergleichen:
1. Wechseln Sie zur Registerkarte Kutools und klicken Sie dann auf Auswählen > Gleiche/Unterschiedliche Zellen auswählen, wie unten gezeigt:
2. Im geöffneten Dialogfeld Gleiche/Unterschiedliche Zellen auswählen:
(1.) Wählen Sie unter Werte suchen inden Bereich aus Names-1, den Sie vergleichen möchten;
(2.) Wählen Sie unter Entsprechendden Bereich aus Names-2, gegen den verglichen werden soll;
(3.) Wählen Sie im Abschnitt Basierend aufdie Option Jede Zeile, um die Zeilen jeweils einzeln zu vergleichen;
(4.) Wählen Sie im Abschnitt Suchendie Option Gleiche Werte, um übereinstimmende Namen zu identifizieren und hervorzuheben;
(5.) Optional können Sie einen Hintergrund oder eine Schriftfarbe festlegen, um die Ergebnisse hervorzuheben und Übereinstimmungen visuell klar erkennbar zu machen.
3. Klicken Sie auf OK, und es erscheint eine Meldungsbox, die anzeigt, wie viele übereinstimmende Zellen gefunden und hervorgehoben wurden. Alle Namen, die in beiden Listen enthalten sind, werden markiert und visuell hervorgehoben – für eine noch einfachere Überprüfung oder Bearbeitung:
Jetzt Kutools für Excel herunterladen und kostenlos testen!
Praktische Tipps: Enthalten Ihre Arbeitsblätter große Datensätze, empfiehlt sich nach der Hervorhebung die Verwendung der Filterfunktion, um schnell nur die Übereinstimmungen anzuzeigen. Prüfen Sie zudem vor dem Vergleich sorgfältig, ob Ihre Bereichsauswahl korrekt ausgerichtet ist und – sofern nicht beabsichtigt – keine Überschriftenzeilen enthält, da Abweichungen die Ergebnisse beeinträchtigen können.
In seltenen Fällen, in denen die Funktion nicht die erwarteten Ergebnisse liefert, stellen Sie sicher, dass beide Listen identisch formatiert sind – beispielsweise beide als Text und ohne versteckte führende oder abschließende Leerzeichen –, da Formatierungsunterschiede dazu führen können, dass Übereinstimmungen übersehen werden.
Bedingte Formatierung verwenden mit Formel über mehrere Blätter hinweg
Wenn Sie weder Formeln in Spalten schreiben noch Add-Ins verwenden möchten, können Sie die bedingte Formatierung mit einer benutzerdefinierten Formel nutzen, um übereinstimmende Namen visuell in einem Blatt basierend auf den Daten eines anderen Blatts hervorzuheben. Diese Methode ist unkompliziert, benötigt kein VBA und liefert zwar keine separate Ergebnisliste – markiert aber Übereinstimmungen sofort für eine schnelle visuelle Prüfung.
Anwendungsszenarien: Diese Lösung eignet sich ideal für Benutzer, die unaufdringliche, visuelle Hinweise auf übereinstimmende Werte wünschen, ohne die Struktur des Arbeitsblatts zu verändern. Die Einschränkung: Regeln zur bedingten Formatierung können nicht direkt auf eine andere Arbeitsmappe verweisen – Formelverweise über mehrere Blätter hinweg funktionieren nur innerhalb derselben Datei.
Schritte:
1. Wählen Sie in Names-1den Bereich aus, auf den Sie die Hervorhebung anwenden möchten (z. B.)A2:A19).
2. Gehen Sie zu Start > Bedingte Formatierung verwenden > Neue Regel > Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
3. Geben Sie im Formelfeld folgende Formel ein:
=COUNTIF('Names-2'!$A$2:$A$19,A2)>0Dies prüft, ob der Wert in A2 von Namen-1 an einer beliebigen Stelle in Namen-2!A2:A19 vorhanden ist.
4. Klicken Sie auf Format, um eine Hervorhebungsfarbe auszuwählen, und anschließend auf OK, um die Regel anzuwenden. Alle Übereinstimmungen werden automatisch in Ihrem ausgewählten Bereich hervorgehoben.
Praktische Tipps: Passen Sie die Bereiche einfach an Ihre tatsächlichen Daten an. Der COUNTIF-Schritt lässt sich zudem mit Filtern kombinieren, um sich ausschließlich auf hervorgehobene Zellen zu konzentrieren. Achten Sie darauf, dass sich beide Arbeitsblätter innerhalb derselben Arbeitsmappe befinden, wenn Sie referenzübergreifende Bezüge einrichten – denn Excel unterstützt keine Regeln für bedingte Formatierung, die externe Dateien referenzieren.
Fehlerhinweise: Falls Hervorhebungen nicht wie erwartet angezeigt werden, überprüfen Sie Ihre Zellbereichsauswahl und die referenzübergreifenden Bezüge auf Fehler. Achten Sie darauf, dass weder führende noch abschließende Leerzeichen sowie Formatierungsinkonsistenzen zu fehlenden Übereinstimmungen führen. Verwenden Sie gegebenenfalls in einer Hilfsspalte die Funktion GLÄTTEN, um Ihre Listen für einen präzisen Vergleich zu bereinigen.
VBA-Code – Automatischer Listenvergleich mit Hervorhebung oder Extraktion von Übereinstimmungen
Für Benutzer, die mit Makros vertraut sind, bietet VBA-Code eine äußerst flexible und automatisierte Möglichkeit, zwei Listen in separaten Arbeitsblättern zu vergleichen. So können Sie übereinstimmende Namen hervorheben oder die entsprechenden Werte an einen neuen Ort extrahieren – ideal für große Datenmengen oder wenn schnelle Aktualisierungen erforderlich sind, sobald sich Ihre Listen ändern.
Anwendungsszenarien: Diese Lösung ist besonders effektiv, wenn Sie Vergleiche regelmäßig durchführen, sehr große Datensätze verarbeiten, Berichte automatisieren oder die Verarbeitung und Darstellung von Übereinstimmungen weiter anpassen möchten. Obwohl VBA-Kenntnisse erforderlich sind, erhalten Sie volle Automatisierung und Kontrolle. Ein Nachteil: Makros müssen in der Arbeitsmappe aktiviert sein – was aufgrund strenger Sicherheitseinstellungen in manchen Umgebungen nicht erlaubt ist.
So führen Sie das Makro aus, um Übereinstimmungen in Namen-1 hervorzuheben, sofern sie in Namen-2 enthalten sind:
1. Klicken Sie auf Entwicklertools > Visual Basic, um das Fenster Microsoft Visual Basic for Applications zu öffnen. Klicken Sie im Fenster auf Einfügen > Modul, und fügen Sie den folgenden Code in das neue Modul ein:
Sub HighlightMatchingNames()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim cell As Range
Dim matchFound As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws1 = Worksheets("Names-1")
Set ws2 = Worksheets("Names-2")
Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
For Each cell In rng1
Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not matchFound Is Nothing And cell.Value <> "" Then
cell.Interior.Color = vbYellow
End If
Next cell
End Sub2. Klicken Sie im VBA-Editor auf die Schaltfläche 
, um den Code auszuführen. Dieses Makro durchsucht die Namen in Spalte A des Arbeitsblatts „Namen-1“. Findet es einen Namen, der auch in Spalte A des Arbeitsblatts „Namen-2“ vorkommt, hebt es die entsprechende Zelle in „Namen-1“ mit einer gelben Füllfarbe hervor. Vor dem neuen Vergleich werden alle vorherigen Hervorhebungen im Bereich entfernt.
Fehlerbehebung: Falls keine Zellen hervorgehoben werden, überprüfen Sie, ob beide Arbeitsblätter exakt „Namen-1“ und „Namen-2“ heißen und ob Ihr Bereich bei A2 beginnt. Stellen Sie sicher, dass Makros aktiviert sind und dass keines der Arbeitsblätter geschützt oder gefiltert ist. Dieser Ansatz lässt sich leicht anpassen – beispielsweise können Sie die Hervorhebungsfarbe ändern oder den Code so modifizieren, dass übereinstimmende Ergebnisse in ein anderes Blatt oder eine andere Spalte kopiert werden.
Zusammenfassung und Empfehlungen: Abhängig von Ihren Anforderungen und Ihrem technischen Kenntnisstand können Sie zwischen integrierten Formellösungen, Makroautomatisierung, intelligenten Add-Ins wie Kutools oder einer einfachen Visualisierung mit bedingter Formatierung wählen. Prüfen Sie bei der Verwendung von Formeln oder VBA stets Ihre Daten auf zusätzliche Leerzeichen oder inkonsistente Formatierungen – häufige Fehlerquellen! Sichern Sie Ihre Daten, bevor Sie stapelweise Änderungen vornehmen, insbesondere beim ersten Einsatz von Makros oder Add-Ins. Sollten Probleme wie nicht aktualisierte Formeln oder falsche Übereinstimmungen auftreten, überprüfen Sie relative und absolute Bereichsangaben und stellen Sie sicher, dass die Arbeitsblattnamen korrekt sind. Wählen Sie die Methode, die am besten zu Ihrem Arbeitsablauf passt, und vergleichen Sie Listen effektiv und effizient über verschiedene Blätter hinweg in Excel.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins