INDEX mit XMATCH in Excel für Zwei‑Wege‑Suchen

Schnellzugriff

• Wie INDEX und XMATCH in Excel funktionieren
• INDEX und XMATCH für eindimensionale Suchen
• INDEX und XMATCH für Zwei‑Wege‑Suchen
• Fehlerbehebung und typische Fallstricke
• Praktische Tipps, Varianten und Checklisten

Kurzdefinitionen

• INDEX: Gibt den Wert einer Zelle zurück, die durch Zeilen‑ und Spaltennummer in einem Bereich bestimmt wird. Kurz: “Hole Wert an Position”.
• XMATCH: Sucht ein Element in einem Bereich und gibt die Position (Index) zurück. Kurz: “Finde Position”.

Wie INDEX und XMATCH einzeln funktionieren

Bevor wir die Kombination behandeln, schauen wir uns die beiden Funktionen separat an. Das hilft, die Logik hinter der Zwei‑Wege‑Suche zu verstehen.

INDEX‑Funktion

Die INDEX‑Funktion liefert einen Wert aus einem Bereich anhand von Zeilen‑ und Spaltennummer:

=INDEX(a,b,c)

• a ist der Bereich (z. B. eine Tabellen‑ oder Bereichsreferenz),
• b ist die Zeilennummer innerhalb dieses Bereichs,
• c ist die Spaltennummer innerhalb dieses Bereichs.

Beispiel:

=INDEX(T_Profit,3,6)

Diese Formel gibt den Wert zurück, der an der Schnittstelle von Zeile 3 und Spalte 6 in der Tabelle T_Profit steht.

Statt feste Zahlen zu verwenden, referenzieren Sie üblicherweise Zellen, die die gewünschte Zeilen‑ und Spaltennummer enthalten. Beispiel:

=INDEX(T_Profit,I2,I3)

Hier liefert INDEX die Zelle in T_Profit, deren Zeile in I2 und deren Spalte in I3 steht.

XMATCH‑Funktion

XMATCH sucht einen Wert in einem Bereich und gibt seine Position als Index zurück. Syntax:

=XMATCH(a,b,c,d)

• a ist das Suchkriterium,
• b ist der Suchbereich,
• c ist der Match‑Typ (0 = exakte Übereinstimmung (Standard); -1 = exakte Übereinstimmung oder nächstkleiner; 1 = exakte Übereinstimmung oder nächstgrößer; 2 = Platzhalter),
• d ist die Suchrichtung (1 = vorwärts, Standard; -1 = rückwärts; 2 oder -2 = binäre Suche bei sortierten Bereichen).

Hinweis: XMATCH ist moderner als MATCH. Es bevorzugt standardmäßig die exakte Übereinstimmung und unterstützt Suchrichtungen und Platzhalter.

Kompatibilität: XMATCH ist in Excel 2021 und Microsoft 365 sowie in der Web‑ und Mobilversion verfügbar. Wenn Sie eine ältere Excel‑Version verwenden, steht XMATCH eventuell nicht zur Verfügung.

Beispiel:

=XMATCH(1927,T_Profit[Employee])

Gibt 3 zurück, wenn 1927 die dritte Zeile der Spalte Employee in T_Profit ist.

Sie können das Suchkriterium auch als Zellreferenz angeben:

=XMATCH(I2,T_Profit[Employee])

INDEX und XMATCH für eindimensionale Suchen

Wenn Sie nur eine Spalte nach einem Wert durchsuchen und einen zugehörigen Wert aus derselben Zeile zurückgeben möchten (z. B. Gesamtgewinn eines Mitarbeiters), kombinieren Sie XMATCH (für die Zeile) mit INDEX (für den Rückgabebereich).

Beispiel: Gesamtgewinn eines Mitarbeiters, dessen ID Sie in I2 eintippen:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),6)

• T_Profit ist die Tabelle mit den Daten,
• XMATCH(I2,T_Profit[Employee]) liefert die Zeilennummer der gesuchten Mitarbeiter‑ID,
• 6 ist die Spaltennummer, aus der der Wert zurückgegeben wird (z. B. “Gesamtgewinn”).

Tipp: Klicken Sie auf den Tabellenkopf, um strukturierte Referenzen leichter auszuwählen.

INDEX und XMATCH für Zwei‑Wege‑Suchen

Der Hauptvorteil der Kombination ist die Two‑Way‑Lookup: Sie bestimmen Zeile und Spalte durch XMATCH‑Aufrufe, sodass die Formel selbst unverändert bleibt, während Sie die Suchkriterien ändern.

Beispiel: Gewinn von Mitarbeiter 1191 im Jahr 2021. Angenommen, I2 enthält die Mitarbeiter‑ID und I3 das Jahr (Spaltenkopf):

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))

• Der erste XMATCH liefert die Zeilennummer für die Mitarbeiter‑ID,
• Der zweite XMATCH ermittelt die Spaltennummer, indem er die Tabellenüberschriften (Headers) durchsucht.

Häufiges Problem: #N/A wegen Zahlen in Überschriften

Wenn Ihre Spaltenüberschriften numerisch aussehen (z. B. Jahreszahlen), speichert Excel Tabellenköpfe manchmal als Text. Ein Lookup mit einem echten Zahlentyp führt dann zu #N/A.

Lösungsschritte:

1. Markieren Sie die Zelle mit dem Lookup‑Wert (z. B. I3).
2. Auf der Registerkarte Start in der Dropdown‑Liste für Zahlenformate “Text” wählen.
3. Mit F2 die Zelle in den Bearbeitungsmodus versetzen und Enter drücken (Excel erkennt jetzt das Textformat korrekt).

Anschließend sollte die Zwei‑Wege‑Formel den korrekten Wert anzeigen:

Sie können jetzt verschiedene Kombinationen in I2 (Mitarbeiter) und I3 (Jahr) ausprobieren, die Formel bleibt gleich und liefert stets das passende Ergebnis:

Erweiterung: Drop‑Downs zur schnellen Auswahl

Um Fehleingaben zu vermeiden und die Bedienbarkeit zu verbessern, richten Sie Datenüberprüfung (Data Validation) mit Dropdown‑Listen ein. Hinweis: Sie können die Tabellenspaltenköpfe nicht direkt als Quelle für Data Validation nutzen. Verwenden Sie stattdessen direkte Zellreferenzen oder benannte Bereiche.

Kurzer Workflow:

1. Bereich auswählen (z. B. Zelle I2).
2. Registerkarte Daten > Datenüberprüfung.
3. Zulassen: Liste. Quelle: Bereichsreferenz oder benannter Bereich (z. B. =MitarbeiterIDs).

Wichtig: Wenn die Quelldaten dynamisch sind (Tabelle wächst), verwenden Sie strukturierte Tabellen oder dynamische benannte Bereiche.

Alternative Ansätze und wann sie sinnvoll sind

• XLOOKUP: Moderne Ein‑Spalten‑Suche, einfacher für ein‑zeilige Lookups. Vorteil: einfacher Syntax bei Einzel‑Lookup. Nachteil: Für Zwei‑Wege‑Suche braucht es eine Kombination mit XMATCH oder INDEX.
• VLOOKUP/HLOOKUP: Ältere Funktionen, kompatibel mit sehr alten Arbeitsblättern, aber anfällig für Fehler bei Einfügen/Entfernen von Spalten und nur ein Suchrichtungsmodell.
• Kombinationen mit FILTER: In dynamischen Tabellen können FILTER und INDEX zusammen komplexere Slices liefern (z. B. alle Treffer für Filterkriterien).

Kurzheuristik: Verwenden Sie INDEX + XMATCH für robuste Zwei‑Wege‑Suchen in Tabellen, XLOOKUP für einfache ein‑dimensionale Abfragen.

Mini‑Methodik: Vorgehen beim Aufbau einer Zwei‑Wege‑Suche

1. Tabelle als strukturierte Tabelle (T_Profit) formatieren.
2. Sicherstellen, dass Kopfzeilen und Lookup‑Werte denselben Datentyp haben (Text vs. Zahl).
3. Zeilenindex mit XMATCH ermitteln: XMATCH(LookupValueRow, T_Profit[KeyColumn]).
4. Spaltenindex mit XMATCH ermitteln: XMATCH(LookupValueCol, T_Profit[#Headers]).
5. INDEX mit beiden Indizes verwenden: INDEX(T_Profit, rowIndex, colIndex).
6. Optional: Data Validation und Fehlerabfanglogik (IFERROR) hinzufügen.

Beispiel mit IFERROR:

=IFERROR(INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers])) , "Nicht gefunden")

Fehlerbehebung und typische Fallstricke

• #N/A: Üblicherweise unterschiedliche Datentypen (Text vs. Zahl) oder Tippfehler. Prüfen Sie Datenformat und verwenden Sie TRIM/VALUE wenn nötig.
• Falscher Index: XMATCH liefert relative Positionen. Achten Sie darauf, dass der Suchbereich exakt die Spalte/Zeile ist, die Sie meinen.
• Strukturierte Referenzen falsch verwendet: Tabellen‑Headers werden mit Tabelle[#Headers] referenziert.
• Sortierung: XMATCH standardmäßig für exakte Übereinstimmung; wenn Sie binäre Suche wählen, muss der Bereich sortiert sein.

Schnelle Prüfungen:

• Verwenden Sie =ISTFEHLER(XMATCH(…)) um zu testen, ob ein Suchwert gefunden wird.
• Testen Sie XMATCH separat in einer Hilfszelle, um zu sehen, welche Position zurückgegeben wird.

Beispiel: Komplettes Praxis‑Beispiel mit Datenvalidierung und Fehlertext

1. Tabelle als “T_Profit” formatieren (Registerkarte Einfügen > Tabelle).
2. Mitarbeiter‑IDs in Spalte “Employee” sicherstellen (keine führenden oder nachgestellten Leerzeichen).
3. I2: Data Validation » Liste » Quelle: =T_Profit[Employee]
4. I3: Data Validation » Liste » Quelle: =T_Profit[#Headers]
5. Ergebniszelle I4:

=IFERROR(INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers])) , "Kein Datensatz")

Dieses Setup liefert immer eine lesbare Meldung statt einen Fehlerwert.

Entscheidungshilfe (Mermaid‑Diagramm)

flowchart TD
  A[Start: Suche ein Feld in der Tabelle] --> B{Ist es eine einzeilige Suche?}
  B -- Ja --> C[Verwende XLOOKUP oder INDEX+XMATCH 'Zeile']
  B -- Nein --> D{Benötigst du Zeile und Spalte?}
  D -- Ja --> E[Verwende INDEX mit zwei XMATCH]
  D -- Nein --> F[Verwende FILTER oder PivotTable]
  C --> G[Prüfe Datentypen]
  E --> G
  F --> G
  G --> H[Ergebnis prüfen und IFERROR hinzufügen]

Rollenbasierte Checkliste

Analyst:

• Tabelle als strukturierte Tabelle anlegen.
• Benannte Bereiche für häufig genutzte Spalten erstellen.
• Validierung und IFERROR einbauen.

Power‑User / Entwickler:

• Komplexe INDEX/XMATCH‑Kombinationen in benutzerdefinierte Funktionen übertragen.
• Edge‑Cases mit TRIM, VALUE und SUBSTITUTE bereinigen.

Gelegenheitsanwender:

• Data Validation für Eingabezellen setzen.
• Testfälle mit Beispiel‑IDs anlegen.

Wann INDEX + XMATCH scheitert und Alternativen

• Sehr große Datensätze mit Millionen von Zeilen: Performance kann leiden; verwenden Sie Power Query, Power Pivot oder Datenbankabfragen.
• Mehrere identische Schlüsselwerte: XMATCH liefert die erste (oder letzte) Übereinstimmung; für alle Treffer verwenden Sie FILTER.
• Komplexe Berechnungen über viele Blätter: Ziehen Sie PivotTables oder Power BI in Betracht.

Sicherheits‑ und Datenschutzhinweis

Behandeln Sie personenbezogene Daten (z. B. Mitarbeiter‑IDs, Gehälter) vertraulich. Speichern Sie Arbeitsmappen mit sensiblen Daten nur in sicheren Bereichen (z. B. firmeneigene OneDrive/SharePoint‑Konten mit Zugriffsbeschränkungen) und beachten Sie gegebenenfalls Datenschutzvorschriften wie DSGVO.

Kurze Zusammenfassung

INDEX + XMATCH sind ein flexibles Paar für robuste Lookups in Excel. XMATCH findet Positionen, INDEX liefert Werte aus Tabellenbereichen. Für Zwei‑Wege‑Suchen verwenden Sie zwei XMATCH‑Aufrufe (Zeile und Spalte). Achten Sie auf Datentypen, nutzen Sie Data Validation und fangen Sie Fehler mit IFERROR ab.

Wichtig: Wenn Sie komplexe Datenmengen oder Performance‑Probleme haben, prüfen Sie Power Tools wie Power Query oder Power Pivot.

Checkliste für die Umsetzung

• Tabelle als strukturierte Tabelle formatieren.
• Lookup‑Zellen per Datenüberprüfung absichern.
• Datentypen Angleichen (Text vs. Zahl).
• IFERROR für benutzerfreundliche Meldungen verwenden.
• Bei langsamer Performance Power Query / Power Pivot prüfen.

Quellen und weiterführende Lektüre:

• Microsoft Docs: XMATCH und INDEX (offizielle Funktionsreferenzen)
• Tutorials zu XLOOKUP, FILTER und Power Query