Die Funktion XLOOKUP in Excel
XVERWEIS (Englisch: XLOOKUP) sucht einen Wert in einem Bereich und gibt den Wert an derselben Position aus einem anderen Bereich zurück. Anders als bei SVERWEIS (Englisch: VLOOKUP) können die beiden Bereiche überall liegen: Das Ergebnis darf links von der Suchspalte stehen, und du zählst keine Spaltennummern.
Die Funktion ist in Excel 2021, Excel 2024, Microsoft 365 und Excel für das Web verfügbar. In Excel 2019 oder älter gibt es sie nicht, daher brauchen Dateien, die mit diesen Versionen geteilt werden, weiterhin SVERWEIS (Englisch: VLOOKUP) oder INDEX/MATCH.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Was es bedeutet |
|---|---|
| lookup_value | Wonach gesucht wird. |
| lookup_array | Die Spalte (oder Zeile), in der gesucht wird. |
| return_array | Die Spalte (oder Zeile), aus der ein Wert zurückgegeben wird. Gleiche Größe wie lookup_array. |
| if_not_foundoptional | Was angezeigt wird, wenn nichts passt, statt #N/A. |
| match_modeoptional | 0 = genaue Übereinstimmung (Standard), -1 = genau oder nächstkleinerer Wert, 1 = genau oder nächstgrößerer Wert, 2 = Übereinstimmung mit Platzhaltern. |
| search_modeoptional | 1 = von vorne suchen (Standard), -1 = von hinten suchen, 2 = binäre Suche (Liste aufsteigend sortiert), -2 = binäre Suche (Liste absteigend sortiert). |
Beispiele
1. Einen Preis über die Produkt-ID finden
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Produkt-ID | Produkt | Preis | Bestand | Produkt-ID | P-102 | |
| 2 | P-101 | Schreibtischlampe | 24.5 | 40 | Preis | 149 | |
| 3 | P-102 | Bürostuhl | 149 | 12 | |||
| 4 | P-103 | Monitorständer | 39.9 | 25 | |||
| 5 | P-104 | Tastatur | 59 | 0 |
Durchsuche die ID-Spalte A2:A5 und gib den Wert aus der Preisspalte C2:C5 zurück. Die genaue Übereinstimmung ist der Standard, daher ist kein FALSE nötig.
2. Nach links schauen
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Produkt-ID | Produkt | Preis | Bestand | ID der Tastatur | |
| 2 | P-101 | Schreibtischlampe | 24.5 | 40 | P-104 | |
| 3 | P-102 | Bürostuhl | 149 | 12 | ||
| 4 | P-103 | Monitorständer | 39.9 | 25 | ||
| 5 | P-104 | Tastatur | 59 | 0 |
Die Suchspalte (B) steht rechts von der Ergebnisspalte (A). SVERWEIS (Englisch: VLOOKUP) kann das nicht; XVERWEIS (Englisch: XLOOKUP) schon.
3. Eine eigene Meldung, wenn nichts gefunden wird
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Produkt-ID | Produkt | Preis | Bestand | Produkt P-999 | |
| 2 | P-101 | Schreibtischlampe | 24.5 | 40 | Produkt nicht vorhanden | |
| 3 | P-102 | Bürostuhl | 149 | 12 | ||
| 4 | P-103 | Monitorständer | 39.9 | 25 | ||
| 5 | P-104 | Tastatur | 59 | 0 |
Das 4. Argument ersetzt #N/A, du brauchst also kein WENNFEHLER (Englisch: IFERROR) um die Formel.
4. Ungefähre Übereinstimmung: Gewichtsstaffeln
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Gewicht ab (kg) | Versand | Paketgewicht | 6.3 | |
| 2 | 0 | 4.99 | Versand | 12 | |
| 3 | 2 | 7.5 | |||
| 4 | 5 | 12 | |||
| 5 | 10 | 20 |
match_mode -1 bedeutet „genau, sonst der nächstkleinere Wert“. 6.3 steht nicht in der Liste, daher wird 5 → 12 verwendet. Anders als bei SVERWEIS (Englisch: VLOOKUP) funktioniert das auch, wenn die Liste nicht sortiert ist.
Häufige Fehler
Fehler #NAME?. Die Excel-Version kennt XVERWEIS (Englisch: XLOOKUP) nicht (Excel 2019 oder älter).
Korrektur: Nutze SVERWEIS (Englisch: VLOOKUP) oder INDEX/MATCH für Dateien, die in älteren Versionen geöffnet werden müssen.
Fehler #VALUE!. Die Such- und Rückgabebereiche haben unterschiedliche Größen, z. B. A2:A5 und C2:C6.
Korrektur: Lass beide Bereiche in denselben Zeilen beginnen und enden.
Bereiche verschieben sich beim Kopieren der Formel nach unten.
Korrektur: Fixiere sie mit $-Zeichen:
=XVERWEIS(G1;$A$2:$A$5;$C$2:$C$5).
Häufig gestellte Fragen
Was ist der Unterschied zwischen XLOOKUP und VLOOKUP?
XVERWEIS (Englisch: XLOOKUP) nutzt getrennte Such- und Ergebnisbereiche (daher kann es nach links schauen), sucht standardmäßig genau und hat einen eingebauten Wert für „nicht gefunden“. SVERWEIS (Englisch: VLOOKUP) funktioniert in jeder Excel-Version.
Kann XLOOKUP mehrere Spalten auf einmal zurückgeben?
Ja. Wenn return_array mehrere Spalten breit ist, z. B. B2:D5, läuft das Ergebnis in Excel 2021 und neuer in die Nachbarzellen über.
Wie finde ich den letzten Treffer statt des ersten?
Setze search_mode auf -1: =XVERWEIS(G1;A2:A5;C2:C5;"";0;-1).
Verwandte Funktionen
- VLOOKUPVLOOKUP sucht einen Wert in der ersten Spalte einer Tabelle und gibt einen Wert aus derselben Zeile in einer anderen Spalte zurück.
- INDEXINDEX gibt den Wert an einer bestimmten Zeile und Spalte eines Bereichs zurück.
- MATCHMATCH gibt die Position eines Wertes in einer Liste zurück, z.
- IFERRORIFERROR gibt einen Wert deiner Wahl zurück, wenn eine Formel einen Fehler wie #N/A oder #DIV/0! erzeugt, und sonst das normale Ergebnis.