SVERWEIS – Doppelte Werte – Excel & Google Sheets
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial zeigen wir Ihnen, wie Sie doppelte Werte mit SVERWEIS in Excel und Google Sheets suchen können. Wenn Ihre Excel-Version XVERWEIS unterstützt, empfehlen wir, stattdessen XVERWEIS zu verwenden, da dies eine viel einfachere Lösung ermöglicht…
SVERWEIS – Die N-te Übereinstimmung
Die SVERWEIS-Funktion gibt immer die erste Übereinstimmung zurück. Um doppelte Werte (oder die n-te Übereinstimmung) zurückzugeben, benötigen wir:
- Einen neuen eindeutigen Bezeichner zur Unterscheidung aller doppelten Werte.
- Eine Hilfsspalte, die eine Liste eindeutiger IDs enthält, die als neue Suchspalte (erste Spalte) des Tabellen-Arrays dienen wird.
- Einen geänderten Suchwert, der dem Format der eindeutigen IDs entspricht.
So sieht die endgültige Lösung aus:
=SVERWEIS(F3&"-"&G3;B3:D7;3;FALSCH)
Gehen wir nun die Lösung durch:
Eindeutige IDs mit der ZÄHLENWENN-Funktion
Nehmen wir an, wir möchten die zweite Punktzahl für Schüler-ID = 2021-A in diesem Datensatz nachschlagen:
Zunächst erstellen wir eindeutige IDs in Spalte B, indem wir die ursprüngliche Studenten-ID (Spalte C) mit einer ZÄHLENWENN-Funktion verketten:
=C3&"-"&ZÄHLENWENN($C$3:C3;C3)
Die ZÄHLENWENN-Funktion zählt die Anzahl der einzelnen Schüler-IDs. Indem wir einen Zellenbezug in der ZÄHLENWENN-Funktion sperren, den anderen aber nicht, kennzeichnen wir jede Instanz einer doppelten Schüler-ID mit einer eindeutigen Nummer:
=ZÄHLENWENN($C$3:C;C3)
Dann verwenden wir einfach den &-Operator, um alles wie in der vorherigen Formel miteinander zu verknüpfen.
SVERWEIS – Die n-te Übereinstimmung
Nun können wir die zweite Übereinstimmung mit dieser SVERWEIS-Formel suchen, wobei der Suchwert dem Format der neuen eindeutigen ID entspricht:
=SVERWEIS(F3&"-"&G3;B3:D7;3;FALSCH)
SVERWEIS – Alle Übereinstimmungen
Was ist, wenn Sie stattdessen alle Übereinstimmungen nachschlagen wollen?
Sie könnten jede Übereinstimmung, die Sie finden möchten, manuell auflisten:
Das könnte eine akzeptable Lösung sein, aber was ist, wenn Sie nicht wissen, wie viele Übereinstimmungen es gibt?
Diese Formel gibt alle Übereinstimmungen für „2021-A“ aus:
=WENNNV(SVERWEIS($F$2&"-"&ZEILE(1:1);$B$3:$D$7;3;FALSCH);"")
Gehen wir die obige Formel durch:
Die Funktion ZEILE
Wir verwenden die Funktion ZEILE, um die Zeilennummer einer Zelle oder eines Bereichs zurückzugeben. Wenn wir eine Formel wollen, die eine bei 1 beginnende Liste von aufeinanderfolgenden Zählnummern erstellen kann, können wir eine ganze Zeilenreferenz in die ZEILE-Funktion eingeben, diese kopieren und in die Spalte einfügen (nach unten ziehen).
=ZEILE(1:1)
Neuer SVERWEIS-Wert
Als Nächstes wird das Ergebnis der ZEILE-Funktion mit dem Suchwert (Überschrift der Spalte G) kombiniert, um den eindeutigen Nachschlagewert zu generieren.
=$G$2&"-"&F3
Hinweis: Achten Sie darauf, dass der ursprüngliche Suchwert in einer absoluten Referenz steht (z. B. $G$2).
Die Funktion SVERWEIS
Führen Sie dann den SVERWEIS durch:
=SVERWEIS(G3;$B$3:$D$7;3;FALSCH)
Die Funktion WENNNV
Beachten Sie die #NV-Fehler für Werte, die nicht vorhanden sind. Verwenden wir nun die WENNNV-Funktion, um stattdessen ein Leerzeichen auszugeben:
=WENNNV(H3;"")
Die Kombination aller Funktionen führt zu unserer ursprünglichen Formel:
=WENNNV(SVERWEIS($F$2&"-"&ZEILE(1:1);$B$3:$D$7;3;FALSCH);"")Es gibt eine bequemere und einfachere Lösung als die obige Formel, die darin besteht, die neue FILTER-Funktion zu verwenden. Mit der FILTER-Funktion können wir Daten filtern und die Duplikate in einem Durchgang extrahieren, aber diese Funktion ist derzeit nur für die Microsoft 365-Version verfügbar. Wenn Sie Microsoft 365 haben, sollten Sie stattdessen die XVERWEIS-Funktion verwenden.
SVERWEIS – Doppelte Suchwerte
Was ist nun, wenn wir doppelte Suchwerte mit den entsprechenden Instanzen aus der Suchspalte abgleichen wollen? In diesem Fall wenden wir die gleiche Methode wie in den vorherigen Abschnitten auf den Suchwert an:
=SVERWEIS(F3&"-"&ZÄHLENWENN($F$3:F3;F3);$B$3:$D$7;3;FALSCH)
SVERWEIS – Die n-te Übereinstimmung in Google Sheets
Alle SVERWEIS-Formeln, die wir oben besprochen haben, funktionieren in Google Sheets auf die gleiche Weise.
=SVERWEIS(F3&"-"&G3;B3:D7;3;FALSCH)
