Google Sheets-Kochbuch: VERWEIS - LOOKUP

Auf dieser Seite geht es um die Verweis-Funktionen, auf englisch LOOKUP-Funktionen:

SVERWEIS - VLOOKUP
WVERWEIS - HLOOKUP
XVERWEIS - XLOOKUP

Mit VLOOKUP und HLOOKUP werden wir keine besonders aufregenden Dinge anstellen. Das liegt daran, dass viele der Techniken die man früher verwenden musste, nicht mehr notwendig sind. Entweder verwendet man XLOOKUP, oder eine Kombination von INDEX und MATCH, oder gleich QUERY, das die ganzen Probleme sehr elegant und unaufwändig löst.

VLOOKUP - SVERWEIS

VLOOKUP sucht von oben nach unten in der ersten Spalte eines Bereiches nach einem Wert. Man kann dabei einen exakten Wert suchen (x=5), oder einen Wert größer gleich (x>=5). Für die zweite Funktion müssen die Werte aufsteigend sortiert sein.

Die Funktion kann dann einen Wert aus der gleichen Zeile aber anderen Spalte des Bereiches zurückgeben. VLOOKUP liefert einen Wert zurück, der rechts des Suchwertes liegt.

Im ersten Beispiel verwenden wir einen nicht exakten Match, ein Beispiel für eine exakte Übereinstimmung finden Sie unter HLOOKUP.

Beispiel Rabatte

In dieser Anwendung suchen wir für einen bestimmten Einkaufswert einen zugehörigen Rabatt in %.

A1:B7 - Suchbereich
Einkaufswert	Rabatt in %
0	0
100	2
200	5
500	10
1000	15
5000	20

=VLOOKUP(E2;$A$2:$B$7;2;TRUE)
- Wir suchen den Wert der Zelle E2 in der Tabelle $A$2:$B$8. Dabei soll als Ergebnis die 2. Spalte des Suchbereiches zurückgegeben werden, hier der Rabatt in %. Das TRUE sagt, dass die Werte aufsteigend sortiert sind, dann wird die höchste Übereinstimmung für den gesuchten Wert zurückgegeben. Also bei einem Einkaufswert von bis 99,99 € ist die höchste Übereinstimmung 0, bis 199,99 € 100 usw.

D1:F5 - Ergebnisbereich
Name	Einkaufswert	Rabatt in %
Müller	890	`=VLOOKUP(E2;$A$2:$B$7;2;TRUE)`
Hinz	23	0
Kunz	2341	15
Schmitz	5102	20

In der ersten Zeile der Tabelle ist die einzufügende Formel angegeben, in den anderen Zeilen das Ergebnis, wenn Sie die Formel herunterkopieren.

HLOOKUP - WVERWEIS

HLOOKUP sucht von links nach rechts in der ersten Zeile eines Bereiches nach einem Wert. Man kann dabei einen exakten Wert suchen (x=5), oder einen Wert größer gleich (x>=5). Für die zweite Funktion müssen die Werte aufsteigend sortiert sein.

Die Funktion kann dann einen Wert aus der gleichen Spalte, aber anderen Zeile des Bereiches zurückgeben. HLOOKUP liefert einen Wert zurück, der unterhalb des Suchwertes liegt.

A1:E3 - Suchbereich
Kunde	Müller	Hinz	Kunz	Schmitz
Rabatt in %	10	0	15	20
Anschreiben	nein	nein	ja	ja

=HLOOKUP(A7;$A$1:$E$3;3;FALSE)
- Wir suchen nach dem Inhalt von Zelle A7 (das wird der Name sein) im Bereich A1:E3. Als Ergebnis wollen wir die 3. Zeile zurückbekommen. Die Werte sind nicht sortiert, wir wollen eine exakte Übereinstimmung.

A6:B10 - Ergebnisbereich
Name	Erhält Anschreiben
Müller	`=HLOOKUP(A7;$A$1:$E$3;3;FALSE)`
Hinz	nein
Kunz	ja
Schmitz	ja

VLOOKUP nach links (oder rechts)

Anmerkung: Der VLOOKUP nach links oder rechts kann jetzt mit XLOOKUP gelöst werden.

Um den VLOOKUP nach links (oder rechts) zu erhalten, gibt es mehrere Möglichkeiten:

Anwendung von Arrays
Kombination von INDEX und MATCH

Wir nehmen der Einfachheit halber unser Beispiel von oben, der Rabatt möge aber links stehen, der Einkaufswert rechts. Verwenden wir die Methode mit INDEX und MATCH, könnten wir auch eine absteigend sortierte Liste nehmen.

A1:B7 - Suchbereich
Rabatt in %	Einkaufswert
0	0
2	100
5	200
10	500
15	1000
20	5000

Arrays

Bei der Anwendung von Arrays sortieren wir die Tabelle um, so dass die Spalten in der gewünschten Reihenfolge stehen.

=VLOOKUP(E2;{$B$2:$B$7\$A$2:$A$7};2;TRUE)
- Wir machen also im Prinzip einen normalen VLOOKUP.
- Die Tabelle sortieren wir um, so dass die Spalte B als erstes steht, die Spalte A als zweites.
- Der Ausgabewert soll die neue 2. Spalte sein.
- Die Werte sind sortiert.

INDEX und MATCH

=INDEX($A$2:$B$7;MATCH(E2;$B$2:$B$7;1);MATCH("Rabatt in %";$A$1:$B$1;0))
- INDEX: Es werden Werte aus dem Zellbereich $A$2:$B$7 zurückgegeben.
- Gesucht wird im Bereich $B$2:$B$7, also mit dem ersten MATCH.
- Die Rückgabespalte steht im Bereich $A$1:$B$1. Wir könnten hier die Spalte auch direkt angeben, hier mit der Zahl 1. Das würde dann so aussehen:
  - =INDEX($A$2:$B$7;MATCH(E2;$B$2:$B$7;1);1)
- Die Zeile wird über MATCH(E2;$B$2:$B$7;1) gefunden, beim Kopieren der Werte nach unten ändert sich der relative Zellbezug, und es wird nach dem entsprechenden Einkaufswert gesucht. Mit dem Suchtyp 1 geben wir an, dass die Spalte aufteigend sortiert ist, mit 0 suchen wir eine exakte Übereinstimmung, mit -1 ist die Spalte absteigend sortiert.
- Der Suchbereich kann also hier in einer beliebigen Spalte stehen.
- Wir können uns eine beliebige andere Spalte ausgeben lassen.

D1:F5 - Ergebnisbereich
Name	Einkaufswert	Rabatt in %
Müller	890	`=INDEX($A$2:$D$7;MATCH(E2;$B$2:$B$7;1);MATCH("Rabatt in %";$A$1:$B$1;0))`
Hinz	23	0
Kunz	2341	15
Schmitz	5102	20

VLOOKUP mit mehreren Ergebnisspalten

VLOOKUP kann nicht nur ein Ergebnis zurückgeben. Übergeben wir ein Zeilenarray als Index und verwenden eine ARRAYFORMULA oder INDEX, können wir die Ausgabespalten wählen.

=ARRAYFORMULA(VLOOKUP(F2;$A$2:$D$7;{2\4};TRUE))
- =INDEX(VLOOKUP(F2;$A$2:$D$7;{2\4\1};TRUE)) macht genau das gleiche.
- Wir suchen den Wert aus der Zelle "F2" im Bereich "A2:D7".
- Als Ergebnis wollen wir aber nicht nur die 2. Spalte haben, sondern die 2., die 4. und die 1. Spalte (die 1. Spalte ist die, in der wir gesucht haben). Dazu übergeben wir VLOOKUP ein Zeilenarray mit den Ergebnisspalten.
- Ohne ARRAYFORMULA oder INDEX würden wir nur die erste Spalte als Ergebnis erhalten, mit ARRAYFORMULA erhalten wir alle gewünschten Spalten.

XLOOKUP - XVERWEIS

XLOOKUP ist eine Kombination aus VLOOKUP und HLOOKUP. Es ermöglicht das Suchen nach Links, von oben nach unten oder von unten nach oben und unterstützt die Wildcards * (beliebig viele Zeichen) und ? (ein Zeichen).

XLOOKUP kann eine ganze Zeile oder eine ganze Spalte als Ergebnis zurückgeben, wenn der Ergebnisbereich entsprechend angegeben wird.

Es kann immer noch nicht mehrere Suchkriterien verwenden.

A1:D8 - Suchbereich
Rabatt in %	Einkaufswert	Kunde 1	Kunde 2
0	0	Müller
2	100	Peters
5	200	Sabri	Kirikova
10	500	Shahín
15	1000	Al-Maziani	Mac an Rìgh
17	2000	Meier
20	5000	Gonzales

=XLOOKUP(F1;C:C;A:A;"nicht gefunden";2)
- Der Suchwert steht in Zelle F1.
- Wir suchen in Spalte C.
- Zurückgegeben wird der Ergebniswert in Spalte A.
- Wird kein Wert gefunden, wird der String "nicht gefunden" ausgegeben.
- Man kann mit Wildcards suchen (2). Sucht man z.B. nach "Sab*", wird der Wert 5 ausgegeben.

=XLOOKUP(F1;C:C;A:D;"nicht gefunden";2)
- Das gleiche Beispiel wie oben, als Ergebnis werden aber die Spaltenwerte A-D in der Ergebniszeile ausgegeben.

Der Wildcard lässt sich auch schon in die Abfrage einbauen.

=XLOOKUP(F1&"*";C:C;A:D;"nicht gefunden";2)
- Hier genügt die Eingabe von "Sa" in das Suchfeld F1, um das Ergebnis zu finden.

Kompliziertere Dinge

XLOOKUP mit 2 kriterien
XLOOKUP mit mehreren Ergebnissen

löst man mit QUERY oder mit FILTER.

XLOOKUP case-sensitiv

Die Lookup-Funktionen unterscheiden leider nicht zwischen Groß- und Kleinbuchstaben. Daher muss man die Funktion EXACT anwenden, um case-sensitiv zu suchen.

Nehmen wir an, wir haben in Spalte A die Vornamen, in Spalte B die Nachnamen von Personen. (siehe z.B. Beispieltabellen)

Nicht case-sensitiv:

=XLOOKUP("Inès";A:A;B:B;"Name nicht gefunden")
- Findet Inès und inès.

Case-sensitiv:

=ARRAYFORMULA(XLOOKUP(TRUE; EXACT("Inès";A:A); B:B; "Name nicht gefunden"))
- Findet nur Inès, nicht inès.
- A:A Suchspalte
- B:B Ergebnisspalte.
- Die Arrayformula wendet den XLOOKUP auf die ganze Spalte an.
- Die Funktion EXACT wird für jeden Vergleich einen Wahrheitswert (TRUE oder FALSE) zurückgeben. XLOOKUP sucht dann nach dem Wert TRUE.

Die Beispiele auf dieser Seite wurden getestet: 01/2024

↑ Inhaltsverzeichnis ↑

Index →