Die 11 besten Alternativen zu SVERWEIS (Update 2022!) – Excel & GS
Die Beispielarbeitsmappe herunterladen
In diesem Tutorial werden die besten Alternativen zu SVERWEIS in Excel und Google Sheets vorgestellt.
1. Die XVERWEIS-Funktion
Wenn Sie eine neue Version von Excel haben, dann ist die XVERWEIS-Funktion wahrscheinlich die beste Alternative zur SVERWEIS-Funktion.
XVERWEIS: Links suchen
Das Nachschlage-Array und das Rückgabe-Array für die XVERWEIS-Funktion sind separate Argumente. Dies führt zu den folgenden Vorteilen:
- Die Formel muss nicht angepasst werden, wenn Spalten eingefügt oder gelöscht werden.
- Keine Notwendigkeit, mehrere Spaltenbereiche/Arrays einzugeben, wenn die Nachschlage- und Rückgabespalten nicht nebeneinander liegen. Dadurch wird die zu verarbeitende Datenmenge reduziert.
- Die Möglichkeit, Suchen links durchzuführen (siehe Beispiel):
=XVERWEIS(F3;D3:D7;C3:C7)
XVERWEIS: Mehr Spalten zurückgeben
Ein Merkmal der XVERWEIS-Funktion ist die Möglichkeit, mehr als eine Spalte zurückzugeben.
=XVERWEIS(F3;B3:B7;C3:D7)
Hinweis: Das Rückgabe-Array (Spalten) muss fortlaufend sein.
XVERWEIS: Behandlung des #NV-Fehlers
Im Gegensatz zur SVERWEIS-Funktion, bei der wir die WENNFEHLER- oder WENNNV-Funktionen verwenden müssen, um den #NV-Fehler zu behandeln, hat die XVERWEIS-Funktion einen eingebauten #NV-Fehlerbehandler, der dem vierten Argument (wenn_nicht_gefunden) ist.
=XVERWEIS(E3;B3:B7;C3:C7; "Nicht gefunden!")
XVERWEIS: Vergleichsmodus-Optionen
Ein weiteres großartiges Merkmal der XLOOKUP-Funktion ist die Möglichkeit, den Vergleichsmodus (5. Argument) auszuwählen: 0 -> Genaue Übereinstimmung, -1 -> Genaue Übereinstimmung oder nächstkleinere Position, 1 -> Genaue Übereinstimmung oder nächstgrößere Position und 2 ->Übereinstimmung mit Platzhalterzeichen.
=XVERWEIS(E3;B3:B7;C3:C7; "Kein Rabatt!";-1)
Hinweis: Im Gegensatz zu den annähernden Übereinstimmungen der Funktionen SVERWEIS, VERWEIS und VERGLEICH, bei denen die Daten sortiert werden müssen, ist beim Vergleichsmodus der XVERWEIS-Funktion standardmäßig keine Datensortierung erforderlich (siehe Beispiel).
XVERWEIS: Letzte Suche
Das Vergleichsmodus-Beispiel aus dem vorherigen Abschnitt erfordert keinen sortierten Datensatz, da der Standardwert vom Suchmodus (6. Argument) eine lineare Suche ist (vom ersten zum letzten). Jetzt können wir den Suchprozess unserer Suche auswählen (d.h. lineare Suche und binäre Suche) und eine davon ist die Option „Suche vom letzten zum ersten“ (d.h. -1), die es uns ermöglicht, die letzte Übereinstimmung einfach zurückzugeben.
=XVERWEIS(E3;B3:B7;C3:C7;;;-1)
XVERWEIS: Binär-exakte Übereinstimmung und dynamische Arrays
Die wichtigste Verbesserung gegenüber der SVERWEIS-Funktion oder sogar der INDEX-VERGLEICH-Formel ist die Möglichkeit, eine exakte Übereinstimmung mit einem binären Suchprozess durchzuführen. Wir können jetzt die Geschwindigkeit der binären Suche nutzen, ohne die exakte Übereinstimmung zu opfern, indem wir einfach das letzte Argument (Suchmodus), entweder auf 2 (aufsteigende Reihenfolge) oder -2 (absteigende Reihenfolge) setzen.
Betrachten wir ein Szenario mit einer Million Suchanfragen:
=XVERWEIS(C3:C1000002;B3:B1000002;B3:B1000002;;;2)
Hinweis: Standardmäßig ist der Vergleichsmodus auf 0, also Exakte Übereinstimmung.
Mit der binären Suche können wir eine solche Suchanfrage in weniger als 5 Sekunden durchführen. Außerdem können wir die XVERWEIS-Funktion in eine dynamische Array-Formel umwandeln. Eine Möglichkeit besteht darin, eine Array-Eingabe in den Suchwert einzugeben (z. B. C3:C1000002).
2. INDEX-VERGLEICH
Die INDEX-VERGLEICH-Formel ist die bekannteste Alternative zur SVERWEIS-Funktion in früheren Versionen von Excel.
INDEX-VERGLEICH: Links-Suchen
Genau wie bei der XVERWEIS-Funktion sind die Suchspalte und die Rückgabespalte in der INDEX-VERGLEICH-Formel getrennt. Die Suchspalte wird in der VERGLEICH-Funktion eingegeben, die auch den Suchvorgang durchführt, während die Rückgabespalte in der INDEX-Funktion eingegeben wird, die den Wert zurückgibt, der dem Ergebnis der VERGLEICH-Funktion entspricht. Diese Anordnung bietet die gleichen Vorteile wie die XVERWEIS-Funktion: automatische Anpassung an Spalteneinfügungen und -löschungen, schnellere und effizientere Formeln aufgrund geringerer Spalten-Array-Eingaben und die Möglichkeit, Links-Suchanfragen durchzuführen (siehe Beispiel unten).
=INDEX(C3:C7;VERGLEICH(F3;D3:D7;0))
Lassen Sie uns die Formel durchgehen:
Die VERGLEICH-Funktion
Beginnen wir mit der Suche nach der relativen Zeilenkoordinate des Suchwertes (z. B. Studenten-ID) aus dem Nachschlage-Array (z. B. D3:D7) unter Verwendung der VERGLEICH-Funktion.
=VERGLEICH(F3;D3:D7;0)
Hinweis: Der Vergleichstyp (3. Argument) der VERGLEICH Funktion definiert den Vergleichsmodus. Null bedeutet exakte Übereinstimmung, während 1 und -1 beide ungefähre Übereinstimmungen bedeuten.
Die INDEX-Funktion
Sobald wir die Zeilenkoordinate haben, können wir diese verwenden, um einen Wert aus dem entsprechenden Rückgabe-Array (z. B. C3:C7) mit der INDEX-Funktion zurückzugeben.
=INDEX(C3:C7;H3)
Hinweis: Die INDEX-Funktion gibt einen Wert aus einem Array zurück, wenn die relativen Zeilen- und Spaltenkoordinaten (bei 2D-Arrays) angegeben sind.
Die Kombination der beiden Formeln ergibt unsere ursprüngliche Formel:
=INDEX(C3:C7;VERGLEICH(F3;D3:D7;0))INDEX-MATCH: Horizontales Nachschlage-Array
Ein weiteres Beispiel, das die Flexibilität der INDEX-MATCH-Formel verdeutlicht, ist es, wenn das Nachschlage-Array und das Rückgabe-Array entgegengesetzt ausgerichtet sind:
=INDEX(B3:B7;VERGLEICH(D7;E2:I2;0))
3. Die WVERWEIS-Funktion
Da wir nun über horizontale Ausrichtungen sprechen, gibt es auch eine Suchfunktion, die für horizontale Suchanfragen entwickelt wurde, und zwar die WVERWEIS-Funktion.
=WVERWEIS(I3;C2:G3;2;FALSCH)
Hinweis: Die WVERWEIS-Funktion funktioniert genauso wie die SVERWEIS-Funktion, jedoch in umgekehrter Richtung. Sie sucht die exakte Übereinstimmung in der ersten Zeile und gibt den entsprechenden Wert ab einer gegebenen Zeilenindexnummer zurück.
4. BEREICH.VERSCHIEBEN-VERGLEICH: Dynamische Spaltenreferenz
Eine weitere Alternative zu SVERWEIS ist die BEREICH.VERSCHIEBEN-VERGLEICH-Formel, die ähnlich wie die INDEX-VERGLEICH-Formel funktioniert.
Schauen wir uns ein Nachschlage-Szenario mit dynamischem Spaltenbezug an und sehen wir, wie sich BEREICH.VERSCHIEBEN leicht von INDEX-VERGLEICH unterscheidet.
=BEREICH.VERSCHIEBEN(B2;VERGLEICH(F3;B3:B7;0);VERGLEICH(G3;C2:D2;0))
Gehen wir die Formel einmal durch:
Zeilen- und Spaltenkoordinaten
Zunächst müssen wir die relativen Zeilen- und Spaltenkoordinaten mithilfe der VERGLEICH-Funktion bestimmen.
Die BEREICH.VERSCHIEBEN-Funktion
Anstatt die INDEX-Funktion zu verwenden, um einen Wert zurückzugeben, werden wir sie durch die BEREICH.VERSCHIEBEN-Funktion ersetzen. Standardmäßig gibt die BEREICH.VERSCHIEBEN-Funktion einen Bereich zurück, indem sie die relativen Zeilen- und Spaltenkoordinaten von einem Referenzbereich (z. B. B2) definiert.
=BEREICH.VERSCHIEBEN(B2;I3;J3)
Hinweis: Die Funktion BEREICH.VERSCHIEBEN ist eine volatile Funktion, d. h. sie wird immer neu berechnet, wenn das Blatt neu berechnet wird. Je nach Szenario kann dies die Geschwindigkeit Ihres Blattes beeinträchtigen.
Wenn Sie alle Funktionen kombinieren, erhalten Sie unsere ursprüngliche Formel:
=BEREICH.VERSCHIEBEN(B2;VERGLEICH(F3;B3:B7;0);VERGLEICH(G3;C2:D2;0))5. INDIREKT-ADRESSE-VERGLEICH: Dynamische Spaltenreferenz
Eine weitere flexible Alternative zum SVERWEIS ist die INDIREKT-ADRESSE-VERGLEICH-Formel. Wenden wir sie auf das Szenario der dynamischen Spaltenreferenz an:
=INDIREKT(ADRESSE(VERGLEICH(F3;B1:B7;0);VERGLEICH(G3;A2:D2;0))
Gehen wir die Formel einmal durch:
Zeilenkoordinate
In dieser Formel müssen wir die Zeilenkoordinate der Zelle selbst bestimmen, im Gegensatz zu den Formeln INDEX und BEREICH.VERSCHIEBEN, bei den wir relative Koordinaten verwendet haben.
=VERGLEICH(F3;B1:B7;0)
Spaltenkoordinate
Als nächstes müssen wir auch die Spaltenkoordinate der Zelle bestimmen.
=VERGLEICH(G3;A2:D2;0)
Die ADRESSE-Funktion
Als Nächstes verwenden wir die Zeilen- und Spaltenkoordinaten, um einen Zellenbezug mit der ADRESSE-Funktion im Textformat zurückzugeben. Standardmäßig wird der Text-Zellenbezug in absoluter Form angegeben (z.B. $C$4).
=ADRESSE(I3;J3)
Die INDIREKT-Funktion
Zuletzt wandeln wir den Text-Zellenbezug in einen echten Zellenbezug um, indem wir die INDIREKT-Funktion verwenden.
=INDIREKT(K3)
Hinweis: Die INDIREKT-Funktion ist eine volatile Funktion, d. h. sie wird immer neu berechnet, wenn das Blatt neu berechnet wird. Je nach Szenario kann dies die Geschwindigkeit Ihres Blattes beeinträchtigen.
Wenn Sie alle Funktionen miteinander kombinieren, erhalten Sie unsere ursprüngliche Formel:
=INDIREKT(ADRESSE(VERGLEICH(F3;B1:B7;0);VERGLEICH(G3;A2:D2;0))
6. Die VERWEIS-Funktion: Letzte Übereinstimmung
Wenn es doppelte Einträge innerhalb des Nachschlage-Arrays gibt, ist es für die SVERWEIS-Funktion schwierig, die letzte Übereinstimmung zu finden. Die XVERWEIS-Funktion ist die beste Lösung, aber wenn das nicht möglich ist, ist die VERWEIS-Funktion die beste Alternative.
=VERWEIS(2;1/(B3:B7=E3);C3:C7)
Gehen wir die Formel einmal durch:
Suchbedingung
Zunächst vergleichen wir die Werte aus der Suchspalte (z. B. B3:B7) mit dem Suchwert (z. B. E3).
=B3=$E$3
Kehrwerte von booleschen Werten
Als Nächstes nehmen wir die Kehrwerte der booleschen Werte, wobei WAHR gleich 1 und FALSCH gleich 0 ist.
=1/G3
Das Kehrwert-Array wird als Nachschlage-Array für die VERWEIS-Funktion verwendet, die Fehler ignoriert. Daher haben wir technisch gesehen ein Array mit 1en.
Die VERWEIS-Funktion
mit der Annahme, dass die Daten in aufsteigender Reihenfolge sortiert sind, kann die VERWEIS-Funktion kann nur eine ungefähre Übereinstimmung erzielen, was bedeutet, dass die VERWEIS-Funktion den größten Wert aus dem Nachschlage-Array finden wird, der kleiner oder gleich dem Suchwert ist.
Wir haben 2 als Suchwert verwendet, um die ungefähre Übereinstimmung im Array der 1en auszunutzen. Wenn es wie im Beispiel Duplikate gibt, wird stattdessen die Position der letzten Instanz zurückgegeben.
=VERWEIS(2;H3:H7;C3:C7)
Kombiniert wir alle Formeln, erhalten wir unsere ursprüngliche Formel:
=VERWEIS(2;1/(B3:B7=E3);C3:C7)7. Die FILTER-Funktion: Suche nach allen Duplikaten
Wenn wir alle Duplikate suchen wollen, bieten neuere Versionen von Excel eine bessere Alternative, und zwar die FILTER-Funktion. Sie ist einfach und erfordert nicht so viele Schritte wie das Hinzufügen von Hilfsspalten.
=FILTER(C3:C7;B3:B7=E2)
Hinweis: Das erste Argument ist das Array (z. B. C3:C7), das gefiltert werden soll, und das zweite Argument sind die Filterkriterien (z. B. B3:B7=E2).
8. FILTER-INDEX: Suche nach der n-ten Übereinstimmung
Anstatt alle Duplikate zurückzugeben, können wir die n-te Übereinstimmung mit der FILTER-INDEX-Formel auswählen. Dies ist eine bequemere Alternative zur SVERWEIS-Methode mit den eindeutigen IDs (siehe Artikel SVERWEIS – Doppelte Werte).
=INDEX(FILTER(C3:C7;B3:B7=E3);F3)
Gehen wir die Formel einmal durch:
Die FILTER-Funktion
Zunächst wollen wir mit der FILTER-Funktion alle Duplikate zurückgeben. Wir haben das Nachschlage-Array = Suchwert als Filterbedingung verwendet.
=FILTER(C3:C7;B3:B7=E3)
Die INDEX-Funktion und n-te Übereinstimmung
Als Nächstes geben wir die n-te Übereinstimmung aus dem Ergebnis der Funktion FILTER mit Hilfe der Funktion INDEX zurück.
=INDEX(H3:H5;F3)
Die Kombination beider Funktionen ergibt unsere ursprüngliche Formel:
=INDEX(FILTER(C3:C7;B3:B7=E3);F3)9. SUMMENPRODUKT: Suchen von Zahlen
Wenn wir nur nach Zahlen suchen, können wir die SUMMENPRODUKT-Funktion als Alternative zur SVERWEIS-Funktion verwenden. Ein Vorteil der SUMMENPRODUKT-Funktion ist die Möglichkeit, mehrere Kriterien anzuwenden. Excel-Benutzer, die keinen Zugriff auf die neuen Array-Formeln haben, müssen nicht STRG+SHIFT+ENTER verwenden, um die Arrays zu verarbeiten. Schauen wir uns das folgende Beispiel an:
=SUMMENPRODUKT((B3:B7=F3)*(C3:C7=G3)*D3:D7)
Gehen wir die Formel einmal durch:
Bedingung 1
Wenden wir zunächst die entsprechenden Bedingungen auf die entsprechenden Spalten an. Hier ist die erste Bedingung:
=B3=$E$3
Bedingung 2
Hier ist die zweite Bedingung:
=C3=$G$3
Das UND-Array
Anschließend wird geprüft, ob beide Bedingungen erfüllt sind, indem die beiden booleschen Felder multipliziert werden (TRUE = 1 und FALSE = 0).
=I3*J3
Hinweis: Die Multiplikation boolescher Arrays entspricht der UND-Funktion. Wenn beide Bedingungen erfüllt sind, ist das Ergebnis 1. Wenn eine der Bedingungen FALSCH ist, ist das Produkt 0.
Das Rückgabe-Array
Wenn es keine Duplikate gibt, dann enthält die Liste einen Wert von 1 und der Rest sind 0s. Wir wandeln dieses Array in das Rückgabe-Array um, indem wir es mit dem Rückgabe-Array selbst multiplizieren.
=K3*D3
Die SUMMENPRODUKT-Funktion
Die SUMMENPRODUKT-Funktion führt eine Array-Multiplikation durch und nimmt die Summe des Produkt-Arrays. Da es nur einen Wert größer als 0 gibt und der Rest 0 entspricht, wird die Summe den gesuchten Wert zurückgeben.
=SUMMENPRODUKT(L3:L7)
Hinweis: Wir haben die Arrays vor der SUMMENPRODUKT-Funktion multipliziert, um die booleschen Arrays in Zahlen umzuwandeln. Die SUMMENPRODUKT-Funktion führt nur Berechnungen mit Zahlen durch und schließt andere Datentypen (z. B. Boolesche, Zeichenketten) aus. Das Kombinieren aller Formeln ergibt unsere ursprüngliche Formel:
=SUMMENPRODUKT((B3:B7=F3)*(C3:C7=G3)*D3:D7)10. Die SUMMEWENNS-Funktion: Suchen von Zahlen
Anstelle der SUMMENPRODUKT-Funktion kann auch die SUMMEWENNS-Funktion verwendet werden, um mehrere Kriterien für Zahlen zu ermitteln. Sie ist einfacher und bequemer zu verwenden als die SUMMENPRODUKT-Funktion, aber im Gegensatz zur SUMMENPRODUKT-Funktion kann sie keine Array-Eingaben wie Array-Ergebnisse anderer Funktionen akzeptieren. Die Bereiche Summen_Bereich und Kriterien_Bereich sind streng genommen Bereiche.
=SUMMEWENNS(D3:D7;B3:B7;F3;C3:C7;G3)
Hinweis: Das erste Argument ist der Summen_Bereich, also das Feld, das summiert wird. Die folgenden Argumente sind Paare von Kriterien_Bereichen, gegen die die Kriterien geprüft werden, und die Kriterien.
11. Google Sheets: Die QUERY-Funktion
Abgesehen von der XVERWEIS-Funktion, die es in Google Sheets nicht gibt, sind alle zuvor erwähnten Funktionen in Google Sheets verfügbar und funktionieren auf die gleiche Weise, aber es gibt eine leistungsfähigere Alternative, die wir in Google Sheets verwenden können, und zwar die QUERY-Funktion.
=QUERY(B3:C7; "SELECT C WHERE B='"&E3&"'")
Hinweis: Das zweite Argument der QUERY-Funktion ist die Abfragesyntax (Textformat), mit der wir Datenmanipulationen wie Nachschlagen, Sortieren, Filtern und Formatieren durchführen können.
Lassen Sie uns die Formel durchgehen:
SELECT
Die SELECT-Klausel filtert die Spalte, die wir zurückgeben wollen (z. B. Spalte C).
=QUERY(B3:C7; "SELECT C")
WHERE
Die WHERE-Klausel filtert die Zeile. Da der Suchwert ein Text ist, müssen wir ihn in einfache Anführungszeichen setzen.
Hinweis: Wenn wir die SELECT-Klausel entfernen, werden stattdessen alle Spalten zurückgegeben.
Die Kombination der beiden Klauseln ergibt unsere ursprüngliche Formel:
=QUERY(B3:C7; "SELECT C WHERE B='"&E3&"'")
