So verwenden Sie SVERWEIS und XVERWEIS in Excel: Vollständige Anleitung und praktische Beispiele

Haben Sie sich bei der Suche nach bestimmten Daten schon einmal in den Tausenden von Zeilen und Spalten einer Tabelle verirrt? Wenn Sie regelmäßig mit Excel arbeiten, haben Sie wahrscheinlich schon von SVERWEIS und XVERWEIS gehört, zwei Funktionen, die einen Unterschied machen, wenn es um die Automatisierung von Suchvorgängen und die Rationalisierung von Aufgaben geht. Die Beherrschung dieser Funktionen stellt einen Qualitätssprung im Alltag eines jeden Benutzers dar, vom Verwalten einfacher Kontaktlisten bis zum Analysieren großer Datenbanken in Unternehmen.

Dieser Artikel ist Ihr ultimativer Leitfaden zum Verständnis und zur professionellen Verwendung von SVERWEIS und XVERWEIS in Excel. Hier brechen wir alle Geheimnisse, Vorteile, Einschränkungen und Tricks praxisnah, beginnend mit den Grundlagen und vertiefend in Beispiele, Syntax und Möglichkeiten, das Beste daraus zu machen, sodass Sie nie wieder durch eine Suche in Excel aufgehalten werden.

Was sind SVERWEIS und XVERWEIS in Excel?

SVERWEIS und XVERWEIS gehören zur Familie der Nachschlage- und Referenzfunktionen in Excel. Dabei handelt es sich um Tools, die das Auffinden bestimmter Daten in großen Listen oder Tabellen erleichtern und die zugehörigen Informationen schnell und fehlerfrei zurückgeben. Die Einsatzmöglichkeiten sind so vielfältig wie notwendig: Rechnungsstellung, Inventarisierung, Berichte, Personalwesen, CRM, Buchhaltung … Wenn Sie in einem Meer von Daten etwas finden müssen, sind diese Funktionen Ihr bester Freund.

SVERWEIS ist die klassische Formel für die vertikale Datensuche., also innerhalb einer Spalte. Es ist seit Jahren eine tragende Säule in Excel. Mit den neuen Versionen kommt jedoch XLOOKUP, eine noch leistungsfähigere und flexiblere Funktion, die viele der Einschränkungen von VLOOKUP behebt.

Warum ist es so wichtig, beides zu lernen? Weil viele Unternehmen immer noch mit älteren Excel-Versionen arbeiten, in denen XLOOKUP nicht verfügbar ist, und weil SVERWEIS immer noch nützlich und hochkompatibel ist. Wenn Sie über die richtige Version verfügen, eröffnet Ihnen SEARCHX eine Welt voller neuer Möglichkeiten.

Wofür ist jeder einzelne? Grundlegende Anwendungen und reale Situationen

Mit der Funktion SVERWEIS können Sie Daten suchen, indem Sie auf einen Wert in der äußersten linken Spalte einer Tabelle verweisen., wodurch etwas Ähnliches zurückgegeben wird, das sich in einer anderen Spalte in derselben Zeile befindet. Beispiel: Sie haben eine Liste von Produkten mit Codes, Beschreibungen und Preisen und möchten, dass Beschreibung und Preis automatisch angezeigt werden, wenn Sie den Code eingeben. SVERWEIS macht es Ihnen leicht.

SEARCHX bringt dieses Konzept auf die nächste Ebene: ermöglicht Ihnen die vertikale und horizontale Suche in jeder Spalte oder Zeile und gibt einen oder mehrere Daten ohne SVERWEIS-Einschränkungen zurück. Darüber hinaus können Sie das Suchverhalten, die Suchergebnisse im Fehlerfall, die Suche mit Platzhaltern und andere erweiterte Funktionen anpassen.

Schauen wir uns das anhand eines sehr gängigen Praxisbeispiels an: dem Rechnungsmanagement. Stellen Sie sich vor, Sie sind ein KMU, das medizinisches Zubehör verkauft. Sie verfügen über kein fortschrittliches Abrechnungssystem und müssen bei jeder eingehenden Bestellung Daten aus einer Liste mit über 3.000 Artikeln extrahieren. Das manuelle Eingeben der Daten wäre zeitaufwändig und fehleranfällig. Mit SVERWEIS oder XLOOKUP können Sie den Prozess automatisieren sodass allein durch die Eingabe des Produktcodes Beschreibung, Präsentation und Preis automatisch ausgefüllt werden.

Wichtige Unterschiede: Welche Einschränkungen gibt es bei SVERWEIS und warum ist XVERWEIS die Weiterentwicklung?

SVERWEIS ist eine tolle Funktion, hat aber einige Einschränkungen. die in komplexen Blättern unangenehm sind:

• Nur von links nach rechts suchen. Die Referenzdaten müssen immer in der ersten Spalte des angegebenen Bereichs stehen. Wenn die gesuchten Daten nicht vorhanden sind, müssen Sie die Tabelle neu organisieren oder zusätzliche Kombinationen vornehmen.
• Gibt einen einzelnen Wert zurück. Wenn Sie mehrere zusammengehörende Datenelemente extrahieren müssen (z. B. Vorname, Nachname und Abteilung), müssen Sie mehrere Formeln verwenden.
• Sie müssen die Nummer der Spalte angeben, die zurückgegeben werden soll. Wenn die Spalten in der Tabelle später neu angeordnet werden, kann die Formel beschädigt werden oder falsche Ergebnisse zurückgeben.
• Das Standardergebnis für nicht gefundene Daten ist der Fehler #N/A.. Es muss kombiniert werden mit JA.FEHLER um eine personalisierte Nachricht anzuzeigen.
• Unterstützt keine Platzhalter oder umgekehrte Suche (Bottom-Up) noch erweiterte binäre Suche.

XLOOKUP beseitigt praktisch alle dieser Einschränkungen und bringt entscheidende Verbesserungen:

• Ermöglicht die Suche in jeder Spalte oder Zeile, unabhängig von der Position. Sie können beispielsweise in der mittleren Spalte suchen und etwas aus der ersten Spalte zurückgeben.
• Geben Sie mehrere Werte zurück, wenn Sie mehrspaltige Bereiche verwenden, wodurch die Suche in komplexeren Datenbanken erleichtert wird.
• Sie müssen keine Spalten zählen. Den Ergebnisbereich wählen Sie direkt aus.
• Sie können die Nicht-gefunden-Meldung ohne zusätzliche Formeln anpassen.
• Exakte, ungefähre und Platzhaltersuche verfügbar um ein Teil oder genau das zu finden, was Sie suchen.
• Möglichkeit der umgekehrten (Bottom-Up) und binären Suche (ideal für geordnete Daten).
• Durchsuchen mehrerer Tabellen gleichzeitig und Verwenden von verschachteltem XLOOKUP, wodurch die Möglichkeiten der Automatisierung erweitert werden.

Welche Excel-Versionen erlauben welche Funktion?

Die SVERWEIS-Funktion ist in allen modernen Excel-Versionen verfügbar., vom ältesten bis zum aktuellsten. Sie finden es sowohl in Microsoft 365 wie in den unbefristeten Versionen von Excel für den Desktop.

XLOOKUP hingegen ist erst ab der Excel-Version 1910 verfügbar.. Wenn Sie Excel 2016 oder eine der frühen Versionen aus dem Jahr 2019 haben, ist es wahrscheinlich nicht der Fall. Um herauszufinden, ob Sie XLOOKUP verwenden können, geben Sie einfach =XLOOKUP in die Bearbeitungsleiste ein und prüfen Sie, ob es in der Liste der Vorschläge angezeigt wird.

Wenn Ihre Version es noch nicht unterstützt, verzweifeln Sie nicht. Meistern Sie SVERWEIS, solange Sie können, und wechseln Sie zu gegebener Zeit zu XVERWEIS, um von den Verbesserungen zu profitieren.

SVERWEIS-Syntax: Aufschlüsselung der einzelnen Argumente

Lassen Sie uns die SVERWEIS-Formel aufschlüsseln, um herauszufinden, wie sie genau funktioniert.:

=SVERWEIS(Suchwert; Tabellenarray; Spaltenindikator; )

• Lookup-Wert: Die Daten, die Sie suchen möchten (können direkt oder in eine Referenzzelle geschrieben werden).
• Matrixtabelle: Der vollständige Suchbereich, einschließlich der Spalte, in der sich die Referenzdaten befinden, und der Spalten mit den abzurufenden Informationen.
• Indikatorspalten: Die Nummer (beginnend mit 1) der Spalte, aus der Sie das Ergebnis innerhalb des ausgewählten Bereichs extrahieren möchten. Wenn Sie die Daten aus der zweiten Spalte des Bereichs möchten, geben Sie hier eine 2 ein.
• : Optional. Wenn TRUE oder weggelassen, wird eine ungefähre Übereinstimmung gefunden; Wenn FALSE, werden nur exakte Übereinstimmungen zurückgegeben.

Typisches Beispiel: Sie haben eine Kundentabelle im Bereich A1:D16. Sie möchten die Telefonnummer (die sich in Spalte 4, also Spalte D, befindet) für den Kunden mit der ID 8 finden. Die Formel wäre:

=SVERWEIS(8;A1:D16;4;FALSCH)

Wenn die Daten nicht vorhanden sind und Sie eine genaue Übereinstimmung anfordern, wird der Fehler #N/A angezeigt. Wenn Sie eine ungefähre Übereinstimmung zulassen, wird das nächstgelegene Ergebnis unten zurückgegeben.

Erweiterte SVERWEIS-Tricks und -Varianten

SVERWEIS ist nicht auf die Suche nach der ersten Spalte beschränkt: Sie können den Bereich neu definieren, um nach jeder Spalte zu suchen. Wenn Sie beispielsweise Namen in Spalte B haben und nach Namen statt nach ID suchen müssen, definieren Sie einfach den Bereich ab Spalte B und setzen Sie den Spaltenindikator:

=VLOOKUP("Miguel Perea Ramos";B1:D16;3;FALSE)

Dadurch wird nach Namen gesucht und beispielsweise die entsprechende Telefonnummer zurückgegeben.

Eine weitere nützliche Kombination ist SVERWEIS mit Dropdown-Listen: Sie können den Benutzer einen Wert aus einer Liste auswählen lassen und mithilfe von SVERWEIS automatisch in einer anderen Zelle relevante Informationen anzeigen lassen. Es ist ideal für Dateien, automatische Berichte, Kataloge usw.

Und wenn Sie hässliche Fehler vermeiden möchten, kombinieren Sie SVERWEIS mit JA.FEHLER um benutzerdefinierte Nachrichten anzuzeigen, wenn die Daten nicht vorhanden sind:

=WENN(SVERWEIS(8;A1:D16;4;FALSCH);»Kunde nicht gefunden«)

XLOOKUP-Syntax: Argumente und ihre Macht

Die XLOOKUP-Formel ist wie folgt aufgebaut:

=XLOOKUP(Suchwert; Sucharray; zurückgegebenes Array; ; ; )

• Lookup-Wert: Die Daten, die Sie suchen möchten (Zelle oder direkter Wert).
• Sucharray: Bereich, in dem Excel nach diesen Daten suchen soll.
• zurückgegebenes_Array: Bereich, aus dem das Ergebnis extrahiert werden soll. Es kann sich um eine oder mehrere Spalten handeln, wenn Sie im Gegensatz zu SVERWEIS mehrere Daten gleichzeitig benötigen.
• : Optional. Wenn der Wert nicht gefunden wird, können Sie die Nachricht oder die Daten angeben, die angezeigt werden sollen (z. B. „Nicht gefunden“).
• : Optional. Definiert, wie nach dem Wert gesucht wird: 0 für exakt, -1 für exakt oder den nächstniedrigeren Wert, 1 für exakt oder den nächsthöheren Wert, 2 für Platzhalterübereinstimmung.
• : Optional. Ermöglicht Ihnen die Auswahl der Datendurchlaufmethode: 1 von oben nach unten, -1 von unten nach oben, 2 binäre Suche aufsteigend, -2 binäre Suche absteigend.

Typisches Anwendungsbeispiel mit XLOOKUP: Sie möchten in einer Liste nach einem Produkt suchen (Codes in Spalte A und Preise in Spalte E), den Preis anzeigen und die Nachricht anpassen, wenn das Produkt nicht vorhanden ist. Die Formel wäre:

=XLOOKUP(B2;Preise!$A$1:$A$7000;Preise!$E$1:$E$7000; "Nicht gefunden"; 0)

Wenn das Element nicht existiert, wird Ihnen direkt „Nicht gefunden“ angezeigt, ohne dass Sie JA.FEHLER.

Aufschlüsselung aller optionalen XLOOKUP-Argumente

Einer der großen Vorteile von XLOOKUP ist die Flexibilität seiner optionalen Argumente. Sehen wir sie uns einzeln an:

• : Sie können Text, eine Zahl oder sogar eine leere Zelle zurückgeben, indem Sie „“ eingeben. Vergessen Sie den visuellen Fehler #N/A und personalisieren Sie das Erlebnis.
• :
  • 0: Genaue Übereinstimmung (Standard).
  • -1: Genaue Übereinstimmung oder nächstniedrigerer Wert.
  • 1: Genaue Übereinstimmung oder nächsthöherer Wert.
  • 2: Platzhalterübereinstimmung (* für mehrere Zeichen, ? für ein einzelnes Zeichen, ~, um das Sonderzeichen zu ignorieren).
• :
  • 1: Von der ersten bis zur letzten Zeile (Standard).
  • -1: Vom letzten zum ersten (Rückwärtssuche).
  • 2: Aufsteigende binäre Suche (erfordert vom niedrigsten zum höchsten Wert sortierte Daten).
  • -2: Absteigende binäre Suche (erfordert Daten, die von hoch nach niedrig sortiert sind).

Praktische Beispiele für XLOOKUP in Aktion

Wir zeigen Ihnen, wie LOOKUPX in verschiedenen realen Szenarien Vorteile bringt:

• Suchen nach einem einzelnen Datenelement: Sucht nach einem Ländernamen und gibt dessen Ländercode zurück.
• Suche nach mehreren Daten: Im Gegensatz zu SVERWEIS können Sie mit einer einzigen Formel eine ganze Informationszeile (Name, Abteilung usw.) zurückgeben.
• Fehlerfreie Anpassung: Wenn Sie das typische #N/A vermeiden möchten, wenn die Daten nicht vorhanden sind, müssen Sie nur das Argument verwenden.
• Kombinierte Suche (vertikal und horizontal): Sie können XLOOKUP verschachteln, um Werte in einer Tabelle in beiden Dimensionen zu kreuzen, ähnlich wie beim Kombinieren von INDEX und MATCH.
• Dynamikbereiche: Wenn Sie zwei Datenbereiche (mehrere Tabellen) haben, können Sie beide in der Formel verketten und XLOOKUP durchsucht beide gleichzeitig.
• Partielle Platzhaltersuche: Daten durch teilweise Übereinstimmung mit *, ? finden oder ~ in Ihrem Suchwert.

Schneller Argumentvergleich: SVERWEIS vs. XVERWEIS

SVERWEIS erfordert immer die Spaltennummer wobei es sich um die zurückzugebenden Daten handelt und nur die Suche in der ersten Spalte des Bereichs möglich ist. Stattdessen, XLOOKUP hängt nicht von der Reihenfolge der Spalten oder ihrer Position ab, geben Sie einfach die genauen Bereiche an, in denen gesucht und zurückgegeben werden soll.

Darüber hinaus unterstützt XLOOKUP dynamische Arrays.: Wenn das returned_array mehrere Spalten gruppiert, können Sie eine Reihe zusammenhängender Daten in mehreren Zellen gleichzeitig zurückgeben, ohne die Formel wiederholen zu müssen.

Beispielargumente und gelöste Fälle

Stellen Sie sich eine Ländertabelle mit Daten zu Bevölkerung, Lebenserwartung und Codes vor. Wenn Sie nur die Bevölkerung möchten, definieren Sie die zurückgegebene Matrix als Bevölkerungsspalte. Wenn Sie mehrere Daten wünschen, definieren Sie das returned_array als mehrere Spalten. Durch Platzieren der Formel in Zelle G6 werden die Daten in den Spalten H6 und I6 ebenfalls dynamisch ausgefüllt.

Was passiert, wenn Sie nach Daten suchen, die nicht vorhanden sind? Mit SVERWEIS, wenn Sie nach Uruguay suchen (und es ist nicht in der Liste) und Sie nicht verwenden JA.FEHLER, wird der Fehler #N/A angezeigt. Mit XLOOKUP entscheiden Sie, welche Nachricht angezeigt wird, welche Nummer oder welches Ergebnis Sie möchten.

Mehr als nur exakte Suche: Der Übereinstimmungsmodus und seine Verwendung

• Um den nächstkleineren Wert zu finden, verwenden Sie -1. Beispiel: Wenn Sie in einer Rabatttabelle nach 46.520 suchen und diese Zahl nicht existiert, wird der Rabatt für den nächstniedrigeren Betrag zurückgegeben.
• Verwenden Sie für den nächsthöheren Wert 1. Nützlich, wenn Sie nach progressiven Bereichen suchen.
• Um Platzhalter zu verwenden, verwenden Sie 2. Suchen Sie beispielsweise nach „*Süden*“ und Sie erhalten „Süd“, „Südosten“, „Nord-Süd“ …

Wie funktionieren Platzhalter in XLOOKUP?

Das Sternchen (*) ersetzt beliebig viele Zeichen. Wenn Sie nach „*Ost“ suchen, werden Ihnen „Ost“, „Südost“, „Nordost“ usw. angezeigt.

Das Fragezeichen (?) ersetzt nur ein Zeichen. Sie haben Fragen zu „María“ oder „Mario“? Versuchen Sie es mit „María“ und Sie werden beides finden.

Die Tilde (~) überschreibt den Platzhalterwert. Wenn Sie nach „Wie~?“ suchen, wird Ihnen nicht „Wie“ finden, sondern nur „Wie?“.

Was ist die binäre Suche, die XLOOKUP bietet?

Wenn Sie über große geordnete Listen verfügen, können Sie mit der binären Suche Daten schnell finden.. Stellen Sie sich ein 100-seitiges Buch vor: Mit dem Binärsystem teilen Sie es in zwei Hälften, wählen den Block aus, in dem es stehen kann, und reduzieren die Anzahl der Prüfungen. Natürlich muss der Bereich gut sortiert sein (aufsteigend oder absteigend, je nach gewähltem Modus), sonst haben die Ergebnisse nicht Zuverlässigkeit.

Kann ich mehrere Tabellen oder nach mehr als einem Kriterium gleichzeitig durchsuchen?

Mit XLOOKUP können Sie mehrere verknüpfte Bereiche gleichzeitig mit der entsprechenden Syntax durchsuchen. Wenn Sie beispielsweise zwei Ländertabellen haben, kann das Lookup-Array aus beiden durch einen Doppelpunkt getrennten Bereichen bestehen, genau wie das Return-Array. Alles mit der gleichen Formel und ohne zusätzlichen Aufwand.

Was ist, wenn ich mehrere Kriterien miteinander vergleichen möchte? Hier kommt das verschachtelte XLOOKUP ins Spiel: Sie können eine anfängliche Suche verwenden, um den Spaltenbereich zu bestimmen, und eine weitere für die Zeile, wobei die genauen Daten an der Schnittstelle zurückgegeben werden, wie bei einer erweiterten Version von INDEX + MATCH.

Wie stark kann ich XLOOKUP-Ergebnisse anpassen?

Ein wenig bekannter Vorteil von XLOOKUP besteht darin, dass Sie im Fehlerfall mehrere Ergebnistypen zurückgeben können.. Sie können je nach Bedarf benutzerdefinierten Text oder eine Zahl anzeigen oder die Zelle leer lassen. Wenn wir mehrere Spalten zurückgeben, belegt das Ergebnis außerdem benachbarte Zellen, die in Excel nicht einzeln bearbeitet werden können, aber durch Löschen der Hauptformel geändert werden können.