WVERWEIS-Funktion – Beispiele in Excel, VBA und Google Sheets

Dieses Tutorial zeigt Ihnen, wie Sie die WVERWEIS-Funktion in Excel und Google Sheets verwenden können, um einen Wert nachzuschlagen.

 

Was ist die WVERWEIS-Funktion?

Die WVERWEIS-Funktion wird für horizontale Suchabfragen verwendet (im Gegensatz zu SVERWEIS, die vertikale Suchabfragen durchführt). Sie sucht nach einem Wert in der obersten Zeile einer Tabelle. Anschließend gibt sie einen Wert zurück, der eine bestimmte Anzahl von Zeilen unterhalb des gefundenen Wertes liegt.

Sie hat einige Einschränkungen, die oft mit anderen Funktionen wie INDEX / VERGLEICH oder XVERWEIS überwunden werden können.

WVERWEIS – Grundlegendes Beispiel

Schauen wir uns ein Datenbeispiel aus einem Notenregister an. Wir werden mehrere Beispiele für die Extraktion von Informationen für bestimmte Studenten behandeln.

Wenn wir herausfinden wollen, in welcher Klasse Bob ist, würden wir die folgende Formel schreiben:

=WVERWEIS("Bob"; A1:E3; 2; FALSCH)

Wichtige Punkte zu beachten:

• Das gesuchte Element (Bob) muss sich in der ersten Zeile unseres Suchbereichs (A1:E3) befinden.
• Wir haben der Funktion mitgeteilt, dass wir einen Wert aus der 2. Zeile des Suchbereichs zurückgeben wollen, was in diesem Fall Zeile 2 ist.
• Wir haben angegeben, dass wir eine genaue Übereinstimmung erzielen wollen, indem wir FALSCH als letztes Argument angegeben haben. Hier wird die Antwort „Lesen“ lauten.

Tipp am Rande: Sie können auch die Zahl 0 anstelle von FALSCH als letztes Argument verwenden, da sie den gleichen Wert haben. Manche Leute bevorzugen dies, da es schneller zu schreiben ist. Sie sollten nur wissen, dass beides akzeptabel ist. Schauen wir uns die Syntax an:

WVERWEIS-Funktion – Syntax und Eingabe:

=WVERWEIS(Suchkriterium; Matrix; Zeilenindex; Bereich_Verweis)

Suchkriterium – Der Wert, nach dem Sie suchen möchten.

Matrix – Die Tabelle, aus der Daten abgerufen werden sollen.

Zeilenindex – Die Zeilennummer, aus der die Daten abgerufen werden sollen.

Bereich_Verweis – [optional] Ein Boolescher Wert, der die exakte oder die ungefähre Übereinstimmung angibt. Voreinstellung = WAHR = ungefähre Übereinstimmung.

WVERWEIS – verschobene Daten

Um unser erstes Beispiel zu verdeutlichen, muss das Nachschlagelement nicht in Zeile 1 Ihres Arbeitsblatts stehen, sondern nur in der ersten Zeile Ihres Suchbereichs. Lassen Sie uns denselben Datensatz verwenden:

Lassen Sie uns die Note für die Klasse „Wissenschaft“ ermitteln. Unsere Formel würde lauten:

=WVERWEIS("Wissenschaft"; A2:E3; 2; FALSCH)

Dies ist immer noch eine gültige Formel, da die erste Zeile unseres Suchbereichs Zeile 2 ist, welche diejenige ist, in der wir unseren Suchbegriff „Wissenschaft“ finden werden. Wir geben einen Wert aus der 2. Zeile des Suchbereichs zurück, die in diesem Fall Zeile 3 ist. Die Antwort lautet also „A-„.

Platzhalter

Die WVERWEIS-Funktion unterstützt die Verwendung der Platzhalter „*“ und „?“ bei der Suche. Nehmen wir zum Beispiel an, wir hätten vergessen, wie man den Namen von Frank schreibt und wollten nur nach einem Namen suchen, der mit „F“ beginnt. Wir könnten die folgende Formel schreiben

=WVERWEIS("F*"; A1:E3; 2; FALSCH)

Diese würde den Namen Frank in Spalte E finden und dann den Wert aus der 2. entsprechenden Zeile zurückgeben. In diesem Fall lautet die Antwort „Wissenschaft“.

Ungefähre Übereinstimmung

In den meisten Fällen sollten Sie darauf achten, dass das letzte Argument in WVERWEIS FALSCH (oder 0) entspricht, damit Sie eine genaue Übereinstimmung erhalten. Es gibt jedoch ein paar Fälle, in denen Sie nach einer ungefähren Übereinstimmung suchen. Wenn Sie eine Liste mit sortierten Daten haben, können Sie WVERWEIS auch verwenden, um das Ergebnis für entweder das gleiche oder das nächstkleinere Element zurückzugeben. Dies wird häufig verwendet, wenn es sich um aufsteigende Zahlenbereiche wie z. B. in einer Steuertabelle oder bei Provisionsboni handelt.

Nehmen wir an, Sie möchten den Steuersatz für ein in Zelle H2 eingegebenes Einkommen ermitteln. Die Formel in H4 kann lauten:

=WVERWEIS(H2; B1:F2; 2; WAHR)

Der Unterschied in dieser Formel besteht darin, dass unser letztes Argument WAHR ist. In unserem konkreten Beispiel sehen wir, dass unsere Person bei einem Einkommen von 45.000 $ einen Steuersatz von 15 % hat.

Hinweis: Obwohl wir normalerweise eine genau Übereinstimmung mit FALSCH als Argument anstreben, ist der Standardwert WAHR, wenn Sie vergessen, das vierte Argument in einem WVERWEIS anzugeben. Dies kann zu unerwarteten Ergebnissen führen, wenn Sie vor allem mit Textwerten arbeiten.

Dynamische Zeile

Bei WVERWEIS müssen Sie ein Argument, das besagt, aus welcher Zeile ein Wert zurückgegeben werden soll, angeben. Es kann jedoch vorkommen, dass Sie nicht wissen, wo sich die Zeile befindet, oder dass Sie dem Benutzer erlauben wollen, die Zeile, aus der zurückgegeben werden soll, zu ändern. In diesen Fällen kann es hilfreich sein, die Zeilennummer mit der VERGLEICH-Funktion zu ermitteln.

Betrachten wir noch einmal unser Notenregister-Beispiel mit einigen Eingaben in G2 und G4. Um die Spaltennummer zu erhalten, könnten wir die folgende Formel schreiben:

=VERGLEICH(G2; A1:A3; 0)

Damit wird versucht, die genaue Position von „Note“ innerhalb des Bereichs A1:A3 zu finden. Die Antwort wird 3 sein. Mit diesem Wissen können wir sie in eine WVERWEIS-Funktion einfügen und eine Formel in G6 wie folgt schreiben:

=WVERWEIS(G4; A1:E3; VERGLEICH(G2; A1:A3; 0); 0)

Die VERGLEICH-Funktion ergibt also den Wert 3 und das sagt der WVERWEIS-Funktion, dass sie ein Ergebnis aus der dritten Zeile im Bereich A1:E3 zurückgeben soll. Insgesamt erhalten wir dann unser gewünschtes Ergebnis „C“. Unsere Formel ist nun so dynamisch, dass wir entweder die zu betrachtende Zeile oder den Namen, nach dem gesucht werden soll, ändern können.

WVERWEIS-Einschränkungen 

Wie bereits zu Beginn des Artikels erwähnt, besteht der größte Nachteil von WVERWEIS darin, dass der Suchbegriff in der Spalte ganz links im Suchbereich stehen muss. Es gibt zwar einige raffinierte Tricks, um dies zu umgehen (z. B. die WAHL-Funktion), aber die gängige Alternative ist die Verwendung von INDEX und VERGLEICH. Diese Kombination gibt Ihnen mehr Flexibilität und kann manchmal sogar eine schnellere Berechnung ermöglichen.

WVERWEIS in Google Sheets

Die WVERWEIS-Funktion funktioniert in Google Sheets genau so wie in Excel:

 

WVERWEIS-Beispiele in VBA

Sie können die WVERWEIS-Funktion auch in VBA verwenden. Tippen Sie:

application.worksheetfunction.hlookup(Suchkriterium,Matrix,Zeilenindex,Bereich_Verweis)

Ausführen der folgenden VBA-Anweisungen:

Range("G2")=application.worksheetfunction.hlookup(Range("C1"),Range("A1:E3"),1)
Range("H2")=application.worksheetfunction.hlookup(Range("C1"),Range("A1:E3"),2)
Range("I2")=application.worksheetfunction.hlookup(Range("C1"),Range("A1:E3"),3)
Range("G3")=application.worksheetfunction.hlookup(Range("D1"),Range("A1:E3"),1)
Range("H3")=application.worksheetfunction.hlookup(Range("D1"),Range("A1:E3"),2)
Range("I3")=application.worksheetfunction.hlookup(Range("D1"),Range("A1:E3"),3)

wird die folgenden Ergebnisse liefern:

Die Funktionsargumente (Suchkriterium usw.) können Sie entweder in die Funktion direkt eingeben oder Variablen definieren, die Sie stattdessen verwenden können.