Kurs
Wie man ein VLOOKUP() mit mehreren Kriterien durchführt
VLOOKUP()
ist eine der am häufigsten genutzten Funktionen von Excel. Damit kannst du Daten in einer bestimmten Spalte einer Tabelle nachschlagen und abrufen. Außerdem hilft es, Daten aus verschiedenen Blättern zusammenzuführen oder verwandte Informationen zu finden.
Aber wusstest du, dass die Funktionalität von VLOOKUP()
erweitert werden kann, um mehrere Kriterien zu berücksichtigen? Das hilft Nutzern, die bei der Suche nach Daten oft mehrere Bedingungen erfüllen müssen, was ein häufiges Problem bei der Arbeit mit Tabellenkalkulationen ist.
In diesem Artikel zeigen wir dir, wie du VLOOKUP()
mit mehreren Kriterien nutzen kannst, um deine Arbeit zu verbessern. Wenn du neu in Excel bist, solltest du zuerst unseren Kurs Einführung in Excel besuchen, um zu lernen, wie man Tabellen verwaltet und Berechnungen durchführt.
VLOOKUP()-Grundlagen
Bevor wir uns mit VLOOKUP()
mit mehreren Kriterien befassen, schauen wir uns zunächst die grundlegende Syntax von VLOOKUP()
an, die wie folgt lautet
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In der obigen Formel:
-
lookup_value
ist der Wert, nach dem du suchen willst. Dies kann eine Zahl, ein Text oder ein Verweis auf eine Zelle sein, die den Suchwert enthält. -
table_array
ist der Bereich der Zellen, der die Daten enthält. Die erste Spalte in diesem Bereich sollte den Suchschlüssel enthalten. -
col_index_num
ist die Spaltennummer in dem Bereich, aus dem du den Wert abrufen möchtest. -
range_lookup
ist die Stelle, an der du einen logischen Wert eingibst (TRUE
oderFALSE
). WennTRUE
verwendet wird, gehtVLOOKUP()
davon aus, dass die erste Spalte des Bereichs in aufsteigender Reihenfolge sortiert ist und gibt die nächstliegende Übereinstimmung zurück.
Lass uns das anhand eines Beispiels verstehen. Hier habe ich ein Blatt voller Mitarbeiterdaten und möchte den Namen eines bestimmten Mitarbeiters anhand seiner ID-Nummer schnell finden. Dazu verwende ich eine VLOOKUP()
.
Eine Tabelle mit Angaben zum Arbeitnehmer. Quelle: Bild vom Autor.
Ich gebe zunächst einen Wert in Zelle F6 ein, der mit einem Wert in der Tabelle übereinstimmt. In meinem Fall schaue ich auf die EMP ID
Referenzspalte, wenn ich 4032
wähle.
Den VLOOKUP()-Wert in eine Zelle eingeben. Quelle: Bild vom Autor.
Dann wähle ich Zelle G6 aus und beginne, meine Formel einzugeben.
Eingabe der VLOOKUP()-Formel in eine Zelle. Quelle: Bild vom Autor.
Als nächstes wähle ich die Zelle aus, in die ich 4032
eingegeben habe. Das ist der Wert, den ich aus der Tabelle entnehme.
Eingabe der Zellennummer, in der der VLOOKUP()-Wert gespeichert ist. Quelle: Bild vom Autor.
Dann wähle ich den Bereich der Tabelle aus (A2:D11
).
Auswahl des Bereichs der Tabelle, die Daten enthält. Quelle: Bild vom Autor.
Ich zähle von links und wähle die Spalte 2
, weil ich nach FIRST NAME
suche und FIRST NAME
die zweite Spalte darüber ist.
Gib die Spaltennummer ein, für die du Ergebnisse möchtest. Quelle: Bild vom Autor.
Nachdem ich FALSE
für eine genaue Übereinstimmung eingegeben habe, drücke ich Enter. Du kannst sehen, wie die Funktion VLOOKUP()
die FIRST NAME
des Arbeitnehmers mit EMP ID
gleich 4032
findet.
Abrufen der gewünschten Ausgabe. Quelle: Bild vom Autor.
Wie man VLOOKUP() mit mehreren Kriterien verwendet
VLOOKUP()
funktioniert in seiner Grundform gut für einfache Suchabfragen, aber es kann schwierig werden, wenn du mehrere Kriterien kombinierst. Aber du kannst diese Einschränkungen mit ein paar Techniken umgehen. Lass uns verstehen, wie das geht.
VLOOKUP() mit einer Hilfsspalte
Wenn du eine VLOOKUP()
mit mehreren Kriterien verwenden musst, kannst du eine Hilfsspalte verwenden, um mehrere Kriterien zu einem einzigen Kriterium zu kombinieren. Veranschaulichen wir uns das anhand des folgenden Beispiels, in dem ich den Umsatz für jede Person anhand eines Datums und eines Produkts ermitteln möchte.
Eine Tabelle mit Kundendaten. Quelle: Bild vom Autor.
Zu Beginn erstelle ich eine neue Spalte, indem ich die verschiedenen Kriterien zu einem eindeutigen Bezeichner verkette. In meinem Fall werden drei Spalten verkettet: Customer Name
, Product
, und Date
.
Erstellen einer "Hilfsspalte". Quelle: Bild vom Autor.
Hier ist die Formel, mit der ich die Spalten zu einer kombiniere:
=B2&"|"&C2&"|"&D2
Wie du siehst, befindet sich nach dem Spaltennamen ein Pipe-Symbol. Dies dient als Trennzeichen zwischen den Daten der einzelnen Spalten und gilt allgemein als gute Praxis bei der Verkettung.
Verkettung von drei Spalten in einer Zelle. Quelle: Bild vom Autor.
Wenn du dich fragst, warum hier eine Zahl statt eines Datums steht, obwohl wir eine Datumsspalte ausgewählt haben, liegt das daran, dass das Datumsformat in ein Zahlenformat geändert wurde. Das Nachschlagen funktioniert trotzdem, aber wenn du die Daten in einer besser erkennbaren Form sehen willst, benutze die Funktion TEXT()
. Die Funktion TEXT()
benötigt zwei Parameter: value
ist der Wert, den du ändern möchtest, und format_text
ist das Format, in das du den Wert ändern möchtest.
Textformel. Quelle: Bild vom Autor.
Um also zu verketten und gleichzeitig das Datum zu formatieren, gebe ich Folgendes ein:
=B2&"|"&C2&"|"&TEXT(D2, "DD-MM-YY")
Formatierung des Datums mit der Formel TEXT(). Quelle: Bild vom Autor.
Dann ziehe ich die Formel nach unten, um die neue Spalte für alle Zeilen zu füllen.
Kopieren der Formel durch Ziehen. Quelle: Bild vom Autor.
Und schon bist du fertig. Alle drei Spalten werden erfolgreich verkettet. Als Nächstes bereite ich die Tabelle vor, die du in der Abbildung unten sehen kannst.
Ich bereite meine Tabelle vor. Quelle: Bild vom Autor.
Dann gebe ich die folgende Formel ein und drücke die Eingabetaste. Du kannst die Ergebnisse unten sehen.
=VLOOKUP(G6&"|"&H6&"|"&TEXT(I6, "DD-MM-YY"),$A$2:$E$11, 5,0)
Abrufen der Umsätze mithilfe von Vlookup mit mehreren Kriterien. Quelle: Bild vom Autor.
VLOOKUP() mit der Funktion CHOOSE()
Die Funktion CHOOSE()
wählt einen Wert aus einer Liste anhand eines bestimmten Index oder einer bestimmten Position aus und gibt ihn zurück. Dies ist die Syntax der Funktion CHOOSE()
:
CHOOSE(index_num, value1, [value2], ...)
In dieser Formel:
-
index_num
ist eine Zahl, die angibt, welcher Wert zu wählen ist. -
value1, value2, …
ist die Liste der Werte, aus der du wählen kannst.
Die Funktion CHOOSE()
kann dir helfen, wenn du Werte anhand mehrerer Kriterien nachschlagen musst oder wenn die Datenstruktur nicht in eine einzige Tabelle passt. In solchen Fällen erstellt CHOOSE()
eine virtuelle Tabelle, um Spalten aus verschiedenen Quellen zu kombinieren oder um Spalten neu zu ordnen, damit sie deinen Nachschlageanforderungen entsprechen. Lass uns das anhand eines Beispiels verstehen. Ich habe den folgenden Datensatz:
Eine Tabelle mit den Noten der Schüler/innen in drei Quartalen. Quelle: Bild vom Autor.
Jetzt möchte ich die Noten der Schüler für das erste Quartal abrufen. Ich gebe also die folgende Formel in Zelle F2 ein:
=VLOOKUP($E2&"|"&F$1, CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15), 2, FALSE)
Hol dir die Noten der Schüler aus dem ersten Quartal. Quelle: Bild vom Autor.
So funktioniert die Formel:
-
CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15)
erstellt eine virtuelle Hilfsspalte, indem sie mehrere Kriterien zu eindeutigen Bezeichnern zusammenfasst. -
=VLOOKUP($E2&"|"&F$1, ..., 2, FALSE)
sucht nach dem angegebenen Wert in dieser virtuellen ersten Spalte und holt sich die zugehörige Punktzahl aus der zweiten Spalte.
Auf diese Weise kannst du auch finden, was du brauchst, ohne zusätzliche Spalten auf deinem Arbeitsblatt zu erstellen.
Eine Alternative zu VLOOKUP() mit mehreren Kriterien
Obwohl VLOOKUP()
eine weit verbreitete Funktion in Excel ist, ist sie nicht die einzige Funktion zum Nachschlagen von Daten. Du kannst Alternativen wie INDEX()
und MATCH()
erkunden. Schauen wir uns an, wie sie zusammenarbeiten, um denselben Zweck zu erfüllen: VLOOKUP()
mit mehreren Kriterien.
INDEX() und MATCH() als VLOOKUP()
Die Funktionen INDEX()
und MATCH()
ermöglichen die dynamische Suche nach Spalten. Du kannst sie verwenden, um mit Daten zu arbeiten, die nicht aufsteigend sortiert sind, und sie können auch horizontale und vertikale Suchen durchführen. Lass uns das anhand des folgenden Datensatzes zeigen:
Eine Tabelle mit Arbeitnehmerdaten. Quelle: Bild vom Autor.
Aus diesen Daten möchte ich die Position von John ermitteln, der 25 Jahre alt ist und in Chicago lebt.
Erstellen einer Referenztabelle. Quelle: Bild vom Autor.
Jetzt geben wir die folgende Formel in G5 ein:
=INDEX(C2:C5,MATCH(1,(G3=A2:A5)*(G4=B2:B5)*(G6=D2:D5),0))
INDEX() und MATCH() zusammen verwenden. Quelle: Bild vom Autor.
Schlussgedanken
Ich habe einige der wichtigsten Techniken behandelt, die mit VLOOKUP()
verwendet werden können - Hilfsspalten, Array-Formeln und sogar ausgefallene Funktionen wie INDEX()
und MATCH()
. Diese Methoden erleichtern den Umgang mit großen Datensätzen und geben dir mehr Vertrauen in deine Daten.
Wenn du mehr über Datenanalyse und -visualisierung in Excel erfahren möchtest, schau dir diese Kurse an: Datenanalyse in Excel und Datenvisualisierung in Excel. Wir haben auch eine kürzere Lektüre, nämlich unser Tutorial zur Visualisierung von Daten in Excel.
Ich bin ein Inhaltsstratege, der es liebt, komplexe Themen zu vereinfachen. Ich habe Unternehmen wie Splunk, Hackernoon und Tiiny Host geholfen, ansprechende und informative Inhalte für ihr Publikum zu erstellen.
Häufig gestellte Fragen
Kann VLOOKUP() mehrere Werte zurückgeben?
VLOOKUP()
kann nicht mehrere Werte zurückgeben, aber du kannst Array-Formeln oder andere Funktionen wie INDEX()
, SMALL()
und ROW()
verwenden, um mehrere Werte zu erhalten.
Wie erstellst du mit VLOOKUP() eine Zwei-Wege-Vergleichstabelle?
Du kannst eine Kombination aus VLOOKUP()
und MATCH()
oder eine Kombination aus INDEX()
und MATCH()
verwenden, um einen zweiseitigen Nachschlag zu erstellen.
Wie führt man in Excel eine Suche ohne Berücksichtigung der Groß-/Kleinschreibung durch?
Excel VLOOKUP()
unterscheidet nicht zwischen Groß- und Kleinschreibung, aber du kannst Funktionen wie EXACT()
für Vergleiche zwischen Groß- und Kleinschreibung verwenden.
Welche verwandten Excel-Funktionen gibt es zu VLOOKUP mit mehreren Kriterien?
Es gibt mehrere verwandte Excel-Funktionen:
- INDEX und MATCH. Diese leistungsstarke Kombination kann verwendet werden, um Abfragen mit mehreren Kriterien durchzuführen. INDEX gibt den Wert einer Zelle in einem bestimmten Bereich zurück, und MATCH findet die relative Position eines Wertes in einem Bereich. Zusammen bieten sie mehr Flexibilität als VLOOKUP, insbesondere bei komplexen Kriterien.
- FILTER. In neueren Versionen von Excel kannst du mit FILTER eine Reihe von Werten zurückgeben, die mehrere Kriterien erfüllen. Diese Funktion kann eine einfachere und dynamischere Alternative zu VLOOKUP sein, wenn es um mehrere Bedingungen geht.
- SUMPRODUKT. Obwohl SUMPRODUCT in erster Linie für mathematische Operationen verwendet wird, kann es angepasst werden, um Nachschlageoperationen mit mehreren Kriterien durchzuführen, indem Arrays, die den Kriterien entsprechen, multipliziert werden, so dass du bestimmte Datenpunkte abrufen kannst.
- XLOOKUP. Dieser moderne Ersatz für VLOOKUP kann mehrere Kriterien effektiver handhaben und bietet mehr Flexibilität, einschließlich der Möglichkeit, in jede Richtung zu suchen und eine Reihe von Ergebnissen zurückzugeben.
- AGGREGATE. Diese Funktion kann mit Array-Operationen umgehen und Fehler ignorieren. Das macht sie nützlich für fortgeschrittene Suchvorgänge, bei denen mehrere Kriterien beteiligt sind und du Fehler effektiv verwalten willst.
Lerne Excel mit DataCamp
Lernpfad
Excel-Grundlagen
Kurs