XVERWEIS statt SVERWEIS: Zeilen und Spalten mit einer Funktion durchsuchen

Mit der neuen XVERWEIS Funktion lässt du dir, wie mit der alten SVERWEIS-Funktion auch den Inhalt einer Zelle anzeigen. Allerdings spielt es keine Rolle, wo das Suchkriterium in der Matrix steht, du musst keine Spalten mehr für den Spaltenindex zählen und du kannst die Meldung #NV direkt unterdrücken. Also sparst du dir beim Einsatz der Funktion XVERWEIS eine Menge Zeit und Nerven.

Passende Schulungen

Excel Aufbau

Excel Aufbau Seminar: Fortgeschrittene Excel Funktionen optimal nutzen

Excel Grundlagen

Excel Grundlagen: Der Excel Grundkurs für deinen Einstieg in MS Excel

Der XVERWEIS ist eine relativ neue Funktion in Microsoft Excel (eingeführt in Excel 365 und neueren Versionen), die das Suchen nach Werten in Tabellen und Bereichen erheblich vereinfacht und flexibler gestaltet. Er gilt als Nachfolger von SVERWEIS und WVERWEIS und behebt viele deren Einschränkungen. Im Englischen heißt die Funktion XLOOKUP.

In unseren Excel Kursen und Schulungen zeigen wir dir diese und weitere nützliche Funktionen. Wenn du eine komplette Einführung benötigst, empfehlen wir dir unseren Excel Kompakt Kurs.

Syntax des Excel XVERWEIS

=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [Wenn_nicht_gefunden]; [Übereinstimmungsmodus]; [Suchmodus])

Die Argumente im Detail:

1. Suchkriterium (erforderlich): Der Wert, nach dem gesucht werden soll.
2. Suchmatrix (erforderlich): Der Bereich oder die Matrix, in dem/der nach dem Suchkriterium gesucht werden soll.
3. Rückgabematrix (erforderlich): Der Bereich oder die Matrix, aus dem/der der entsprechende Wert zurückgegeben werden soll.
4. Wenn_nicht_gefunden (optional): Der Wert, der zurückgegeben werden soll, wenn keine Übereinstimmung gefunden wird. Wenn dieses Argument weggelassen wird und keine Übereinstimmung gefunden wird, gibt der XVERWEIS den Fehlerwert #NV zurück.
5. Übereinstimmungsmodus (optional): Gibt den Typ der Übereinstimmung an:
   • 0 (Standard): Genaue Übereinstimmung.
   • -1: Genaue Übereinstimmung oder nächstkleinerer Wert.
   • 1: Genaue Übereinstimmung oder nächstgrößerer Wert.
   • 2: Platzhalterübereinstimmung (z.B. “*”, “?”).
6. Suchmodus (optional): Gibt die Suchrichtung an:
   • 1 (Standard): Suche vom ersten zum letzten Element.
   • -1: Suche vom letzten zum ersten Element.
   • 2: Binäre Suche, Suchmatrix muss aufsteigend sortiert sein.
   • -2: Binäre Suche, Suchmatrix muss absteigend sortiert sein.

Bonus: Im Bonusteil erfährst du, wie Ddu in einem Schritt mehrere Ergebnisse z. B. Anrede, Vorname und Nachname anzeigen lassen kannst.

Passende Schulungen

Excel Kompakt

Der Excel Kurs: Werden zum Profi in fünf Tagen - auch online

XVERWEIS Beispiel: So nutzt du ihn wie ein Profi

Du brauchst zunächst eine Liste z. B. mit Namen oder Artikeln und einem eindeutigen Kennzeichen pro Name oder Artikel z. B. eine Nummer. In diesem Beispiel hat jede Adresszeile in der Spalte H eine eindeutige Nummer. Über diese Nummer soll der passende Nachname aus der Spalte G gezeigt werden. Die Nummer wird von dir in die Zelle B7 eingegeben, der XVERWEIS zeigt dir in C7 den passenden Nachnamen.

• Gib in die Zelle B7 die Nummer ein, zu der du den Nachnamen sehen möchtest, z. B. 105
• Klick in die Zelle C7 und drücke [ª] + [F3].
• Suche im Fenster Funktion einfügen in der Kategorie Nachschlagen und Verweisen die Funktion XVERWEIS.
• Im ersten Argument Suchkriterium gibst du die Zelle ein, in die du die gesuchte Nummer eingegeben hast. In diesem Beispiel B7.
• Im Argument Suchmatrix markiere jetzt die Zellen, die das Suchkriterium enthalten können. Dazu klickst du am besten auf den Spaltentitel, in diesem Beispiel H.
• Im Argument Rückgabematrix markierst du die Zellen, die den Wert enthalten, der angezeigt werden soll. In diesem Beispiel stehen die Nachnamen in der Spalte G.

Eigentlich bist du jetzt schon fertig, denn du siehst unten links im Fenster bereits den Nachnamen, der zur Nummer 105 passt. Aber sieh dir bitte unbedingt die folgenden Argumente Wenn_nicht_gefunden, Vergleichsmodus und Suchmodus auch an.

• Im Argument Wenn_nicht_gefunden kannst du den Text eingeben, der erscheinen soll, wenn das Suchkriterium nicht gefunden wird, z. B. Nummer nicht vorhanden. Wenn du das Feld leer lässt und du suchst nach einer Nummer, die nicht vorhanden ist, erscheint standardmäßig die Meldung #NV.
• Im Argument Vergleichsmodus gibst du eine 0 ein, wenn du nur hundertprozentige Treffer sehen möchtest.
• Blättere mit der Bildlaufleiste nach unten und gib im Feld Suchmodus eine 1 ein, damit die Suche von oben nach unten stattfindet.

• Klicke auf OK.

Wenn du jetzt nach einer Nummer suchst, die nicht in der Suchmatrix zu finden ist, erscheint die Meldung, die du im Argument Wenn_nicht_gefunden erfasst hast.

Der XVERWEIS zeigt dir in einem Schritt mehrere Ergebnisse z. B. Anrede, Vorname und Nachname

Wenn du im Feld Rückgabematrix mehrere Zellen markierst, werden diese dann auch ausgegeben. In diesem Beispiel soll die Anrede, der Vor- und der Nachname bei der Suche angezeigt werden.

Nun werden zwei leere Spalten benötigt. Markiere die Spalte D mit einem Klick auf den Spatentitel und drücke 2-mal die Tastenkombination [Strg] + [+].

WICHTIG: Die Zellen neben der Zelle, in die du die Formel eingibst, müssen leer sein, sonst erhältst du als Ergebnis nur #-Zeichen.

Ändere die Formel in der Zelle C7 ab:

=XVERWEIS(B7;J5:J15;G5:I15;”Nummer nicht vorhanden”;0)

Die Inhalte der Zellen D7 und E 7 wurden automatisch von der ersten Formel ab Zelle C7 übertragen.

Hier reicht also eine XVERWEIS-Funktion, um direkt drei Werte auf einmal abzurufen.

Vorteile des XVERWEIS gegenüber dem SVERWEIS:

• Flexibilität: Der XVERWEIS ist viel flexibler in Bezug auf die Suchrichtung und die Position des Rückgabebereichs.
• Einfachere Verwendung: Die Syntax ist einfacher und intuitiver.
• Standardmäßig genaue Übereinstimmung: Vermeidet Fehler durch falsche Sortierung.
• Bessere Fehlerbehandlung: Die Möglichkeit, einen Wert für den Fall anzugeben, dass keine Übereinstimmung gefunden wird, verbessert die Benutzerfreundlichkeit.
• Keine Spaltenindex-Nummerierung mehr nötig: Man muss nicht mehr die Spalten innerhalb der Matrix zählen, was fehleranfälliger war.