In diesem Lernprogramm werden Zweck und Syntax der Excel-Funktion XLOOKUP, ihre Vorteile gegenüber ihren Vorgängern, die beiden wichtigsten Verwendungsmöglichkeiten von XLOOKUP mit mehreren Kriterien einschließlich ihrer Vor- und Nachteile, die Erweiterung der Funktionalität durch optionale Parameter und die Möglichkeit zum Ausführen einer ähnlichen Suche in älteren Excel-Versionen erläutert.
XLOOKUP in Excel ist die aktuell neuste Funktion der Gruppe der Nachschlagefunktionen (LOOKUP, VLOOKUP, HLOOKUP und XLOOKUP) und bietet viele Vorteile, erweiterte Funktionalität und mehr Flexibilität.
In diesem Tutorial besprechen wir zunächst, wozu die Excel-Funktion XLOOKUP dient und inwiefern sie besser ist als die älteren Nachschlagefunktionen. Als Nächstes werfen wir einen Blick auf ihre grundlegende Syntax und kommen dann zur Sache – der Verwendung der XLOOKUP-Funktion mit mehreren Kriterien.
Wenn Sie die Grundlagen von Excel erlernen möchten, ist der anfängerfreundliche Einführungskurs in Excel der richtige Ausgangspunkt.
Warum XLOOKUP verwenden?
Die Funktion XLOOKUP durchsucht einen Bereich oder ein Array von Daten und gibt das Element zurück, das der ersten Übereinstimmung entspricht. Wenn keine Übereinstimmung gefunden wird, kann XLOOKUP eine ungefähre Übereinstimmung zurückgeben, wenn ein bestimmter Übereinstimmungstyp angegeben wird. In vielen Aspekten übertrifft die Funktion XLOOKUP ihre Vorgänger in Excel (VLOOKUP, HLOOKUP und LOOKUP).
Insbesondere ermöglicht es:
- Suche nach Daten sowohl horizontal als auch vertikal und in jede Richtung
- Mehrere Suchkriterien
- eine ungefähre Übereinstimmung, während standardmäßig die genaue Übereinstimmung verwendet wird
- eine teilweise Übereinstimmung
- Rückgabe mehrerer Spalten und Zeilen
- Rückgabe eines benutzerdefinierten Textes, wenn keine Übereinstimmung gefunden wird.
Darüber hinaus ist die XLOOKUP-Funktion schneller als die älteren Nachschlagefunktionen in Excel, was bei der Suche in großen Datenmengen von Bedeutung ist.
Grundlegende XLOOKUP-Syntax
Lassen Sie uns kurz die grundlegende Syntax der XLOOKUP-Funktion betrachten:
Dabei sind die erforderlichen Parameter:
- lookup_value– der zu suchende Wert
- lookup_array– das zu durchsuchende Array oder der zu durchsuchende Bereich
- return_array– das zurückzugebende Array oder der zurückzugebende Bereich
und die optionalen Parameter sind:
- [if_not_found]—der angegebene Text, der zurückgegeben werden soll, wenn keine Übereinstimmung gefunden wird
- [match_mode]– der Übereinstimmungstyp (genau oder ungefähr) und was zurückgegeben werden soll, wenn keine Übereinstimmung gefunden wird.
- [search_mode]– der zu verwendende Suchmodus (direkte oder umgekehrte Suche, binäre Suche im aufsteigend oder absteigend sortierten Nachschlagearray).
Später in diesem Tutorial sehen wir Beispiele zur Verwendung der optionalen Parameter.
Um einige andere hilfreiche Funktionen und Formeln in Excel zu erlernen oder Ihre Kenntnisse aufzufrischen, sehen Sie sich das Lernprogramm „Die 15 grundlegenden Excel-Formeln, die jeder kennen sollte“ und das Spickzettel für Excel-Formeln an .
XLOOKUP mit mehreren Kriterien
Wie aus der grundlegenden Syntax hervorgeht, ist die Excel-Funktion XLOOKUP standardmäßig darauf ausgelegt, mit einem einzelnen Nachschlagewert einer angegebenen Variablen zu arbeiten.
Wir können diese Funktion jedoch so anpassen, dass sie gleichzeitig auf mehrere Variablen angewendet werden kann, wobei in jeder Variable nach einem anderen Nachschlagewert gesucht wird. Mit anderen Worten: Wir können unsere Suche mit XLOOKUP durchführen, indem wir mehrere Kriterien anwenden. Dafür gibt es zwei Hauptansätze: Verkettung und Boolesche Ausdrücke, und wir werden beide bald besprechen.
Bevor wir uns mit den technischen Details befassen, schauen wir uns eine Excel-Tabelle an, mit der wir arbeiten werden. Diese Tabelle enthält Informationen zu 10 Katzen, einschließlich ihrer Namen, Farben und ihres Alters:
In unseren Experimenten werden wir XLOOKUP verwenden, um mithilfe der anderen beiden eines der drei Merkmale einer Katze herauszufinden.
Es ist in Ordnung, dass unsere Tabelle sehr einfach ist und wir in jedem Fall ohne Funktion eindeutig finden können, was wir wollen. Hier müssen wir die Grundprinzipien der Verwendung von XLOOKUP mit mehreren Kriterien verstehen. Wenn wir diese Prinzipien kennen, können wir sie problemlos auf komplexere reale Szenarien ausweiten, z. B. wenn wir mehr als zwei Suchkriterien haben oder bestimmte Anforderungen erfüllen müssen.
XLOOKUP mit mehreren Kriterien durch Verkettung
Der Verkettungsansatz ist ziemlich unkompliziert: Wir müssen die entsprechenden Nachschlagewerte und Arrays miteinander verketten. Sehen wir uns an, wie das funktioniert.
Angenommen, wir möchten das Alter einer weiß-grauen Katze namens Nala herausfinden. Der Einfachheit halber haben wir diese beiden Kriterien auf demselben Blatt hinzugefügt, auf dem wir unsere Tabelle speichern:
In diesem Fall lautet die Formel für XLOOKUP wie folgt:
Es wird 2,5 zurückgegeben, das Alter der ersten weiß-grauen Nala (meine Katze 😺). Denken Sie daran, dass XLOOKUP das Element zurückgibt, das der ersten Übereinstimmung entspricht.
Um zu verstehen, was die ersten beiden Komponenten der obigen Formel darstellen – diejenigen mit einem Et-Zeichen – können wir einfach jede von ihnen in einer separaten Excel-Zelle ausführen:
Das Ergebnis ist NalaWhiteund Grey.
Das Ergebnis ist:
Ja, so einfach ist das: Wir haben einfach die entsprechenden Nachschlagewerte und Nachschlage-Arrays miteinander verkettet und dabei die grundlegende XLOOKUP-Syntax befolgt, die wir zuvor besprochen haben.
XLOOKUP mit mehreren Kriterien unter Verwendung Boolescher Ausdrücke
Lassen Sie uns nun dieselbe Aufgabe ausführen – das Alter einer weiß-grauen Katze namens Nala ermitteln – mit dem zweiten Ansatz: Boolesche Ausdrücke. In diesem Fall lautet die Formel für XLOOKUP wie folgt:
Wie erwartet wird auch 2,5 zurückgegeben, was dem Alter der ersten weiß-grauen Nala entspricht.
Sehen wir uns die Komponenten der obigen Formel an. Hier bedeutet 1 , TRUEd. h. wir suchen nach dem TRUEWert im Nachschlagearray, der durch die zweite Komponente der Formel dargestellt wird – (B2:B11=G2)*(C2:C11=G3).
Diese Komponente enthält wiederum zwei Multiplikatoren, die jeweils eine bestimmte Bedingung überprüfen: Der erste überprüft, ob der Name dem gesuchten Namen entspricht, während der zweite überprüft, ob die Farbe der gesuchten Farbe entspricht. Lassen Sie uns jeden Multiplikator in einer separaten Excel-Zelle ausführen:
Lassen Sie uns nun die gesamte zweite Komponente der Formel ausführen:
Während wir im obigen Ergebnis zwei Werte von 1 sehen (also zwei TRUEWerte), gibt die XLOOKUP-Funktion das Element zurück, das der ersten Übereinstimmung entspricht.
Der Ansatz, boolesche Ausdrücke zu verwenden, wenn die XLOOKUP-Funktion für mehrere Kriterien ausgeführt wird, sieht viel komplizierter und weniger intuitiv aus als der Ansatz, der auf Verkettung basiert. Dies würde noch schlimmer werden, wenn wir mehr als zwei Suchkriterien verwenden würden. Warum können wir dann nicht immer Verkettung verwenden?
Der Boolesche Ausdrucksansatz bietet uns viel mehr Flexibilität bei der Verwendung von XLOOKUP mit mehreren Kriterien. Sehen wir uns seine Vorteile gegenüber dem Verkettungsansatz an.
Überprüfen des FALSEWertes
In der obigen Formel haben wir überprüft, ob der Boolesche Ausdruck ist TRUE, und den Wert 1 übergeben:
In manchen Fällen müssen wir prüfen, ob der Boolesche Ausdruck tatsächlich ist FALSE. Wir möchten beispielsweise das Alter der ersten Katze in unserer Tabelle ermitteln, die keine weiß-graue Nala ist. Wir sollten unsere Formel wie folgt ändern:
Das Ergebnis ist 1. Dies ist das Alter der ersten Katze, die keine weiß-graue Nala ist (genauer gesagt ist es eine dreifarbige Nala, aber keine weiß-graue).
Verwenden logischer Operatoren
Bei booleschen Ausdrücken sind wir nicht darauf beschränkt, nur die Eigenkapitalquote zu prüfen. Nehmen wir an, wir möchten die Farbe einer Katze namens Nala herausfinden, die weniger als 2 Jahre alt ist:
In diesem Fall lautet die Formel für XLOOKUP wie folgt:
Das Ergebnis wird sein Tricolor.
Mindestens ein Kriterium erfüllen
Bisher haben wir nur die Übereinstimmung mit den allangegebenen Kriterien überprüft. In anderen Szenarien müssen wir möglicherweise mindestens ein Kriterium erfüllen.
Um zu sehen, wie es funktioniert, kehren wir zu unserer ursprünglichen Aufgabe zurück – das Alter einer weiß-grauen Katze namens Nala herauszufinden:
Dieses Mal möchten wir jedoch das Alter einer Katze herausfinden, die entweder Nala oder weiß-grau heißt. Die XLOOKUP-Formel lautet in diesem Fall:
Es gibt 1 zurück, was dem Alter einer dreifarbigen Nala entspricht.
Zur Erinnerung: Als wir nach dem Alter einer Katze suchten, die sowohl Nala heißt als auch weiß-grau ist, lautete die Formel wie folgt:
Das Ersetzen *durch +machte den Unterschied.
Wenn Sie das volle Potenzial von Excel entdecken möchten, sollten Sie einen umfassenden, praxisorientierten und vielseitigen Kurs zu den Excel-Grundlagen in Betracht ziehen.
Optionale Parameter für XLOOKUP mit mehreren Kriterien
Wie bei der regulären Verwendung von XLOOKUP mit einem einzelnen Kriterium können wir zusätzliche Optionen dieser Funktion verwenden, wenn wir sie mit mehreren Kriterien ausführen. Hier kommen die optionalen Parameter [if_not_found], [match_mode], und [search_mode]ins Spiel.
Werfen wir einen kurzen Blick auf die Beispiele für die Verwendung jedes dieser Parameter mit XLOOKUP und mehreren Kriterien. Der Einfachheit halber wenden wir in jedem Beispiel den Verkettungsansatz an.
Zurückgeben eines bereitgestellten Textes, wenn keine Übereinstimmung gefunden wird
Hier suchen wir nach dem Alter einer schwarzen Nala – einer in unserer Tabelle nicht vorhandenen Katze:
In diesem Fall haben wir der XLOOKUP-Formel den optionalen Parameter hinzugefügt [if_not_found], dem wir den Text zugewiesen haben, der zurückgegeben werden soll, wenn keine Übereinstimmung gefunden wird:
Tatsächlich hat die Funktion den angegebenen Text zurückgegeben: No cat is found. Hätten wir keinen Text angegeben, hätte die Funktion zurückgegeben #N/A.
Zurückgeben einer ungefähren Übereinstimmung
Nehmen wir an, wir suchen nach dem Alter einer dreifarbigen Nala, sind uns aber nicht sicher, ob ihre Farbe in der Tabelle als „Tricolor“ oder „Tricolour“ angegeben ist. Hier müssen wir eine ungefähre Übereinstimmung mit Platzhaltern verwenden und der XLOOKUP-Funktion das [match_mode]Argument übergeben, das 2 ergibt.
In der obigen Tabelle haben wir ein Sternchen (*) verwendet, das eine beliebige Anzahl von Zeichen darstellt, einschließlich 0. Weitere Platzhalter finden Sie in der Microsoft Office- Dokumentation .
Die XLOOKUP-Formel lautet hier:
Und es wird 1 zurückgegeben, das Alter der ersten (und einzigen) dreifarbigen Nala in unserer Tabelle.
Beachten Sie, dass Argument 2 in der obigen Formel bedeutet, dass wir an einer ungefähren Übereinstimmung interessiert sind und nicht nach dem Alter einer Katze Nala mit der Farbe „Tricolo*r“ suchen. In unserem Fall hat das Sternchen das Wort „Tricolor“ erfasst, aber es würde dasselbe Ergebnis bei den Wörtern „Tricolour“ oder beispielsweise „Tricolooor“ erzielen voice over.
Durchführen einer Rückwärtssuche
Machen wir die gleiche Übung wie am Anfang – das Alter einer weiß-grauen Nala herausfinden – aber dieses Mal beginnen wir unsere Suche beim letzten Gegenstand.
Dazu müssen wir der XLOOKUP-Funktion den optionalen Parameter hinzufügen [search_mode], der wie folgt auf -1 gesetzt ist:
Die Funktion gibt 7 zurück, das Alter der ersten weiß-grauen Nala vom Ende des Nachschlage-Arrays.
Alter Ansatz für die Suche nach mehreren Kriterien: INDEX und MATCH
Um in älteren Excel-Versionen eine ähnliche Funktionalität wie bei der Verwendung der XLOOKUP-Funktion mit mehreren Kriterien zu reproduzieren, müssen wir die Funktionen INDEX und MATCH kombinieren. Ohne uns näher mit der Syntax beider Funktionen zu befassen, wollen wir uns ansehen, wie wir das Alter der ersten weiß-grauen Nala mithilfe der alten Methode INDEX + MATCH und der neuen XLOOKUP-Funktion ermitteln können (die Zellbezüge finden Sie in der vorherigen Tabelle):
Auch wenn die Argumente ziemlich ähnlich aussehen, wollen wir hier die Hauptvorteile der Ausführung der XLOOKUP-Funktion gegenüber der alten Methode erwähnen:
- Für diesen Vorgang reicht eine Funktion aus – das Kombinieren von Funktionen ist nicht erforderlich.
- Die Möglichkeit, den Verkettungsansatz anzuwenden, wenn möglich.
- Die Möglichkeit, optionale Parameter zu verwenden (in der MATCH-Funktion fehlen sie).
Fazit
In diesem Tutorial haben wir die Syntax der XLOOKUP-Funktion in Excel untersucht, ihre Vorteile gegenüber älteren Analoga und wie man XLOOKUP mit mehreren Kriterien verwendet.
Genauer gesagt haben wir die beiden wichtigsten Suchmethoden betrachtet, wann es besser wäre, welche Methode anzuwenden, und wie die Verwendung des Booleschen Ausdrucksansatzes uns viel mehr Flexibilität geben könnte. Darüber hinaus haben wir gelernt, wie man die Funktionalität von XLOOKUP mit mehreren Kriterien erweitert, indem man die optionalen Parameter verwendet, und wie man eine Suche ähnlich wie XLOOKUP in älteren Excel-Versionen ausführt.
Zur Vorbereitung auf ein Vorstellungsgespräch in Excel können Sie sich mit dem Leitfaden „Die 25 wichtigsten Excel-Vorstellungsgesprächsfragen für alle Niveaus“ vertraut machen . Darin werden die häufigsten technischen Excel-Vorstellungsgesprächsfragen für Anfänger, Fortgeschrittene und Experten vorgestellt.