XMATCH in Excel Tutorial: Überblick, Syntax und praktische Anwendungen

Bei der Datenanalyse gibt es viele Situationen, in denen du die relative Position eines Elements in einem Feld oder einem Bereich von Zellen anhand eines bestimmten Werts oder Musters bestimmen musst. 

Die effektivste Methode, um Mustervergleiche in Excel durchzuführen, ist die XMATCH-Funktion. XMATCH ist eine einfache, aber leistungsstarke Funktion mit mehreren Funktionen, die einen präzisen und flexiblen Abgleich ermöglichen. 

In diesem Lernprogramm behandeln wir die Grundlagen der Excel-Funktion XMATCH, analysieren ihre Syntax und die verschiedenen Parameter und veranschaulichen ihre Möglichkeiten anhand von praktischen Beispielen. Wir werden XMATCH auch mit MATCH vergleichen, einer anderen beliebten Funktion, die für ähnliche Aufgaben verwendet wird.  Zum Schluss gehen wir auf häufige Fallstricke bei der Verwendung der XMATCH-Funktion ein und legen Best Practices fest, um eine solide und effiziente Nutzung sicherzustellen.

Wenn du auf deiner Excel-Lernreise bist und mehr erfahren möchtest, schau dir unseren Excel-Lernpfad Grundlagen um alles Wesentliche zu lernen.

Was ist XMATCH?

XMATCH ist eine flexible und robuste Funktion für Abgleichsoperationen. Mit XMATCH() kannst du alle Arten von Abgleichsoperationen durchführen, von vertikalen und horizontalen Look-ups bis hin zu exakten, ungefähren und teilweisen Übereinstimmungen. 

Wenn du schon lange mit Excel arbeitest, wirst du wahrscheinlich mit der MATCH-Funktion vertraut sein. Vor dem Excel-Update, mit dem XMATCH eingeführt wurde, war MATCH die bevorzugte Funktion für Abgleichsaufgaben. Du kannst mehr über diese Funktionen in unserem Excel-Lookup-Funktionen-Tutorial.

Wie wir später sehen werden, kann MATCH jedoch in bestimmten Szenarien versagen. In dieser Hinsicht kann XMATCH als eine verbesserte und lang erwartete Erweiterung der traditionellen MATCH-Funktion angesehen werden, die eine Suche in jede Richtung und mehrere Übereinstimmungsarten ermöglicht, wodurch sie einfacher und für eine größere Bandbreite von Anwendungsfällen geeignet ist.

Beachte, dass die Funktion nur in Excel für Microsoft 365 und Excel 2021 verfügbar ist. Für Benutzer mit früheren Excel-Versionen ist MATCH immer noch die einzige verfügbare Funktion.

Werfen wir nun einen Blick auf die XMATCH-Syntax und die Parameter. 

XMATCH Syntax und Parameter

Hier ist die grundlegende Syntax der XMATCH-Funktion:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Die Funktion kommt mit zwei erforderlichen Argumenten (d.h., lookup_value und lookup_array) und zwei optionale Argumente (match_mode und search_mode), die genauere und flexiblere Operationen ermöglichen.

Im Folgenden findest du eine Beschreibung der vier Parameter:

• Lookup_value: Das ist der Wert, auf den du achten solltest.
• Lookup_array: Es ist das Array oder die Liste, in der gesucht werden soll. Es kann ein vertikales oder horizontales Lookup-Array sein.
• Match_modeermöglicht es dir, die Art des Spiels zu wählen. Es gibt vier Modi, darunter:
• 0: Genaue Übereinstimmung (der Standardmodus)
• -1: Genaue Übereinstimmung oder nächstkleinere Position
• 1: Genaue Übereinstimmung oder nächstgrößere Position
• 2: Ein Platzhalter, wie * und ?
• Search_modekannst du den Suchmodus festlegen, einschließlich der Suchrichtung und des Algorithmus. Es gibt vier mögliche Werte:
• 1: Suche von Anfang bis Ende (Standardwert)
• -1: Suche von letzter zu erster Stelle (Rückwärtssuche).
• 2:  Binäre Suche aufsteigend. Die Suche wird unter der Annahme durchgeführt, dass das Lookup-Array in aufsteigender Reihenfolge sortiert ist, andernfalls gibt sie ungültige Ergebnisse zurück.
• -2:  Binäre Suche absteigend. Die Suche wird unter der Annahme durchgeführt, dass das Lookup-Array in absteigender Reihenfolge sortiert ist, andernfalls werden ungültige Ergebnisse zurückgegeben.

Spielmodi: Präzision und Flexibilität

XMATCH verfügt über einen optionalen match_mode-Parameter, mit dem du die Art der Übereinstimmung festlegen kannst. Diese Optionen sind sehr praktisch, denn sie ermöglichen sowohl einen präzisen als auch einen ungefähren Abgleich. Lass uns die verfügbaren Spielmodi im Detail analysieren:

Genaue Übereinstimmung

Exakte Übereinstimmung (match_mode = 0 )ist der Standardübereinstimmungsmodus von XMATCH. Das bedeutet, dass Excel eine exakte Übereinstimmung des Nachschlagewerts im Nachschlagearray findet, andernfalls wird ein NA-Fehler zurückgegeben. Ein exakter Abgleich ist wichtig, wenn es auf Genauigkeit und Präzision ankommt, z. B. beim Auffinden bestimmter Einträge in einer Datenbank.

Hier ist ein einfaches Beispiel dafür, wie man die XMATCH-Funktion verwendet, um die genaue Position von "Excel" in einer Liste von Technologien zu finden, die im DataCamp-Kurskatalog. Wir geben den Nachschlagewert in D3 und das Nachschlagearray (A3:A14) an, und Excel gibt korrekt die Position 6 an. 

Ungefähre Übereinstimmung

Es kann Situationen geben, in denen du einen flexiblen Abgleich vornehmen musst, entweder weil du den genauen Nachschlagewert nicht kennst oder weil du nach ungefähren Ergebnissen suchst. 

Zum Glück verfügt XMATCH über zwei Abgleichsmodi, mit denen du exakte oder nächstkleinere und exakte oder nächstgrößere Werte abgleichen kannst. 

Im folgenden Beispiel verwenden wir den Näherungsabgleich, um die Kurse zu finden, die dem Nachschlagewert 15 am nächsten liegen. 

Wenn du die nächstkleinere Zahl verwendest (match_mode = -1), gibt Excel die Position 6 zurück, denn 12 Kurse sind die nächstkleinere Zahl in der Liste. Bei der nächstgrößeren Zahl gibt Excel 5 zurück, denn 16 Kurse sind die nächstgrößere Zahl.

Wir verwenden auch die exakte Übereinstimmung, um zu zeigen, was passiert, wenn Excel keine exakte Übereinstimmung findet.

Platzhalter und Regex-Übereinstimmungen

Eine innovative Funktion in XMATCH ist die Möglichkeit, einfache Wildcards zu verwenden, wenn du match_mode auf 2 setzt, sowie erweiterte reguläre Ausdrücke, wenn du match_mode auf 3 setzt. Diese Abgleichmodi funktionieren nur mit Textdaten.

Mit XMATCH kannst du zwei beliebte Platzhalter verwenden:

• ?: um ein beliebiges einzelnes Zeichen zu finden.
• *: um eine beliebige Folge von Zeichen zu finden.

Im Folgenden findest du verschiedene Beispiele für die Verwendung von Regex-Matches. Im ersten Beispiel suchen wir nach Technologien, die mit einem E beginnen. Im zweiten Beispiel suchen wir nach Technologien mit einem A im Namen. Schließlich suchen wir nach Technologien mit drei Buchstaben.

Beachte, dass XMATCH immer das erste Vorkommen im Lookup-Array zurückgibt, es sei denn, wir geben etwas anderes an (siehe nächster Abschnitt).

Wenn du komplexere reguläre Ausdrücke verwenden willst, setze match_mode auf 3. Reguläre Ausdrücke, oft abgekürzt als "regex" oder "regexp", sind eine Möglichkeit, ein Suchmuster zu definieren, das für verschiedene Textmanipulationsaufgaben wie das Suchen, Parsen und/oder Ersetzen von Text verwendet werden kann. 

Weitere Details zum Musterabgleich findest du in unserem Excel Regex Tutorial: Mastering Pattern Matching with Regular Expressions ist ein großartiger Ort, um anzufangen.

Hier ist ein recht einfaches Beispiel: Wir wollen die Position eines Kurses finden, dessen Code drei Buchstaben am Anfang, drei am Ende und zwei Zahlen in der Mitte enthält (31).

XMATCH Suchmodi: Richtung und Effizienz

XMATCH führt einen neuen Parameter ein, mit dem du die Richtung der Suche bestimmen kannst. Diese Funktion ist ideal, wenn du eher das letzte als das erste Vorkommen finden willst, zum Beispiel für bestimmte Aufgaben in der Zeitreihenanalyse. 

Schauen wir uns die verschiedenen Suchmodi im Detail an.

Rückwärtssuche

Um eine Rückwärtssuche durchzuführen, kannst du den Parameter search_mode auf -1 setzen. Unten siehst du zum Beispiel, wie du den ersten und den letzten Kurs findest, den der Benutzer Peter abgeschlossen hat.

Binäre Suche

Schließlich bietet der Parameter search_mode auch die Möglichkeit, einen binären Suchalgorithmus zu verwenden, um den Abgleich vorzunehmen. Binäre Suche ist ein Sortieralgorithmus, bei dem das Suchintervall wiederholt in zwei Hälften geteilt wird. Es ist einer der effektivsten Sortieralgorithmen, aber er setzt voraus, dass die Eingabedaten vorher sortiert wurden. 

Mit XMATCH kannst du die binäre Suche in einem auf- (search_mode = 2) und absteigend (search_mode = -2) sortierten Array nutzen. Wenn das Lookup-Array nicht sortiert ist, gibt die Funktion einen Fehler aus.

Praktische Anwendungen und Beispiele von XMATCH

XMATCH eignet sich für eine breite Palette von Excel-Aufgaben, von grundlegend bis fortgeschritten. In diesem Abschnitt stellen wir einige der interessantesten praktischen Anwendungen vor.

Zweidimensionale Lookups mit INDEX/XMATCH

Die Funktionen INDEX und XMATCH können zusammenarbeiten, um zweidimensionale Suchvorgänge durchzuführen, d.h. in Zeilen und Spalten gleichzeitig nachzuschlagen. Wenn wir diese beiden Funktionen kombinieren, können wir eine dynamische Suche erstellen, die in Bereichen wie der Finanzanalyse, in denen Variablen häufig über Kategorien hinweg verglichen werden, hervorragend funktioniert. 

In der folgenden Abbildung kombinieren wir beispielsweise INDEX und MATCH, um die Anzahl der angemeldeten Nutzer in Excel-Kursen im zweiten Quartal zu ermitteln. INDEX benötigt hier drei Argumente: das Array, in dem der Wert gefunden werden soll, die Zeilennummer und die Spaltennummer. Die Werte der letzten beiden Argumente werden von mehreren XMATCH-Funktionen geliefert.

 Wenn wir die vorliegende Technologie oder das Quartal ändern möchten, musst du nur die Nachschlagewerte in den XMATCH-Funktionen ändern, und das Ergebnis des INDEX ändert sich mit.

Der INDEX/XMATCH funktioniert ganz ähnlich wie der INDEX/MATCH. Wenn du mehr über dieses leistungsstarke Tandem mit praktischen Beispielen erfahren möchtest, schau dir unsere wie man mehrere Kriterien in Excel mit INDEX MATCH verknüpft Tutorial.

Mehrspaltige Nachschlagewerke

Eine weitere gute Möglichkeit, XMATCH zu nutzen, ist die Kombination mehrerer Kriterien, um mehrspaltige Nachschlagewerke zu erstellen. Das mag nicht intuitiv erscheinen, da XMATCH look einen Nachschlagewert und ein Nachschlagefeld benötigt, um zu funktionieren. Aber wir können boolesche Logik verwenden, um ein temporäres Array in der XMATCH-Formel zu erstellen, das Nullen und Einsen enthält, um Zeilen zu repräsentieren, die den von dir angegebenen Kriterien entsprechen. 

Angenommen, du willst die erste Technologie in der Liste finden, die weniger als 1000 angemeldete Nutzer/innen pro Quartal hat. Du kannst die Bedingungen kombinieren, indem du die Ergebnisse in jeder Spalte mit dem Symbol * zusammenfasst. Excel findet unter der Haube das Array, das alle Einsen enthält, in unserem Fall Tableau, daher die Position 5.

Dynamische Array-Filterung

XMATCH kann auch Hand in Hand mit den Funktionen FILTER und ISNUMBER arbeiten, um die dynamische Array-Filterung zu verbessern.

Unten erfährst du, wie es funktioniert. Wir werden diese Funktionen nutzen, um die Informationen über abgeschlossene Kurse von Nutzern anhand des Nutzernamens zu filtern. 

Zunächst berechnen wir mit XMATCH die relative Position jedes Nutzers in der Tabelle auf der Grundlage des Lookup-Arrays mit den Namen, die wir zum Filtern verwenden werden. So erhalten wir eine Tabelle mit der relativen Position jedes Namens, einschließlich der Nullwerte für Benutzer, die nicht in der Filterliste enthalten sind.

Dann wandeln wir die Spalte mit relativen Positionen und Nullwerten mit der Funktion ISNUMBER in TRUE oder FALSE um.

Schließlich verwenden wir FILTER, um alle Spalten auszuwählen, die wir anzeigen wollen, und die Spalte mit TRUE und FALSE, um Zeilen zu filtern.

Nullwerte mit ISNA ansprechen

Wenn XMATCH keine Übereinstimmung findet, gibt es einen NA Fehler zurück. Das ist zwar schon informativ, aber du kannst die Ergebnisse aufhübschen und Fehlinterpretationen verhindern, indem du die Funktionen ISNA und IF verwendest.

Stell dir vor, wir haben eine Tabelle mit den Kursen, die Sam und Peter absolviert haben, und wir wollen wissen, welche Kurse sie gemeinsam haben. Wenn wir nur XMATCH verwenden, gibt Excel die relative Position der gemeinsamen Kurse zurück und ansonsten Nullwerte. Wenn wir XMATCH als Argument von ISNA übergeben, wird die Funktion stattdessen TRUE oder FALSE zurückgeben. Dann können wir mit der IF-Funktion die booleschen Werte für andere Nachrichten ändern.

Binäre Suche für große Datensätze

Wir haben bereits erwähnt, dass XMATCH bei Abgleichsoperationen einen binären Suchalgorithmus verwenden kann. Diese Funktion kann beim Umgang mit großen Datensätzen eine entscheidende Rolle spielen. Wenn das Nachschlagefeld sortiert ist, kann die binäre Suchmaschine deine Abgleichsoperationen erheblich beschleunigen.

XMATCH vs MATCH: Die wichtigsten Unterschiede

Wie bereits erwähnt, wurde XMATCH entwickelt, um die traditionelle MATCH-Funktion abzulösen. Beide haben den gleichen Zweck, aber XMATCH bietet eine Reihe neuer Funktionen, die es leistungsfähiger machen als MATCH. Im Folgenden findest du eine Liste mit den wichtigsten Unterschieden zwischen XMATCH und MATCH:

Häufige Fallstricke und Lösungen

Es gibt viele Einsatzmöglichkeiten für XMATCH. Dennoch ist es wichtig, auf häufige Probleme hinzuweisen, die bei der Nutzung auftreten können. Hier ist eine Liste mit einigen regelmäßigen Fallstricken und Tipps, wie du sie umgehen kannst.

• Überprüfe die Datentypen: XMATCH zeigt ein unerwartetes Verhalten, wenn du es auf einen Bereich mit dem falschen Datentyp anwendest. Bevor du die Funktion anwendest, solltest du daher sicherstellen, dass du die richtigen Datentypen für deine Spalten eingestellt hast.
• Konsistenz der Reichweite: XMATCH erlaubt zwar horizontale und vertikale Nachschlagebereiche, aber die Vermischung dieser Bereiche kann zu unerwarteten Problemen und Leistungseinbußen führen. Wenn möglich, versuche, konsequent zu sein und wähle entweder horizontale oder vertikale Bereiche.
• Nutze die Spielmodi: XMATCH verfügt über verschiedene Spielmodi. Der Standardmodus ist die exakte Übereinstimmung, aber du kannst deine Suchvorgänge auch mit Hilfe von Näherungssuchen und Regex-Operationen verfeinern.
• Besser mit sortierten Arrays: Insgesamt wird die Leistung von XMATCH immer höher sein, wenn du es auf sortierte Arrays anwendest. Das gilt für die Standardfunktion und ist eine zwingende Voraussetzung, wenn du die binäre Suche verwenden willst. 
• Behandlung von N/A-Fehlern:  Wie bereits erwähnt, gibt XMATCH einen NA-Fehler aus, wenn es keine Übereinstimmung in deinen Lookups gibt. Die beste Möglichkeit, dieses Problem zu lösen und die Lesbarkeit zu verbessern, ist die Übergabe der Ergebnisse an eine Kombination aus WENN- UND ISNA-Funktionen oder an eine IFNA-Funktion.

Leistungsüberlegungen

XMATCH ist nicht die einzige Funktion, mit der du Nachschlageoperationen in Excel durchführen kannst. Du kannst zum Beispiel Folgendes verwenden VLOOKUP, HLOOKUPoder sogar alte Funktionen wie MATCH. Wenn es jedoch um die Suchgeschwindigkeit geht, ist XMATCH wohl am schnellsten, vor allem, wenn du es auf geordnete Arrays anwendest, wo du auch die Leistung der binären Suche nutzen kannst.

Erwähnenswert ist auch, dass XMATCH als dynamische Array-Formel konzipiert ist, d.h. sie kann je nach Eingabedaten Arrays von unterschiedlicher Größe zurückgeben. Dynamische Array-Formeln eignen sich besonders gut für die Arbeit mit großen Datensätzen oder komplexen Berechnungen. 

Fazit

Glückwunsch, dass du es bis zum Ende geschafft hast. XMATCH ist eine leistungsstarke Funktion, die du bei deinen Excel-Datenanalysen nicht missen möchtest. Ob als eigenständige Funktion oder in Kombination mit anderen Excel-Funktionen, XMATCH bringt deine Nachschlageoperationen auf ein neues Niveau. 

Aber es gibt noch viel mehr zu wissen in der wunderbaren Welt von Excel. Schau dir unsere speziellen Kurse, Blogs und Tutorials an, um ein Excel-Assistent zu werden:

• XLOOKUP() vs. VLOOKUP(): Ein Vergleich für Excel-Benutzer
• Datenbearbeitung in Excel - Spickzettel 
• Excel | Master Advanced Techniques Kurs
• Power Query in Excel für Fortgeschrittene 
• Excel Shortcuts Spickzettel 
• Excel-Formeln Spickzettel 
• Wie man mit INDEX MATCH mehrere Kriterien in Excel erfüllt 
• Datenmanipulation mit INDEX-MATCH in Tabellenkalkulationen 
• Excel Regex Tutorial: Mustervergleiche mit regulären Ausdrücken beherrschen