Excel Funktion
XVERWEIS
für Excel 2007 bis 2019

  

Download der Exceldatei mit dem VBA-Code der Funktionen
SORTIEREN, SORTIERENNACH, XVERWEIS, XVERGLEICH, FILTER, EINDEUTIG, SEQUENZ, ZUFALLSMATRIX
und allen auf diesen Seiten dargestellten Beispielen:
▸Download der Exceldatei
(Version 3.7 - Letzte Änderung: 06.01.2023 - XVerweis und XVergleich nun fit für den Einsatz in Matrixformeln ▸mehr dazu)

Worum geht es?

In Excel 365 und ab Excel 2021 hat Microsoft die lange vermissten Zellfunktionen SORTIEREN, SORTIERENNACH, FILTER, XVERWEIS und weitere implementiert. Allerdings wurden sie für ältere Excel-Versionen nicht nachgerüstet, so dass alle Benutzer von Excel 2007 bis einschließlich Excel 2019 darauf verzichten mussten.

Die Funktion XVERWEIS

Aber zunächst zur Funktion XVERWEIS:

Die Funktion XVERWEIS ist eine Verbesserung der bisherigen Funktionen SVERWEIS und WVERWEIS. Sie erfüllt die Aufgaben dieser beiden und bietet darüber hinaus noch wesentlich mehr Möglichkeiten.

Syntax

= XVerweis ( Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden];
    [Vergleichsmodus]; [Suchmodus] )

Der Parameter Suchmatrix muss eine einzelne Zeile oder eine einzelne Spalte oder ein eindimensionales Array sein.

Wenn der Parameter Rückgabematrix aus mehreren Zeilen und mehreren Spalten besteht, werden mehrere Werte zurückgegeben. Die Eingabe der Formel muss in diesem Fall mit STRG-UMSCHALT-EINGABE abgeschlossen werden.

Die Funktion XVerweis hat das gleiche Verhalten wie die Excel 365-Funktion XVERWEIS.
Vergleiche dazu auch die Funktionsbeschreibung von Microsoft:
▸https://support.microsoft.com/de-de/office/xverweis-funktion-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Die neue Funktion XVERWEIS hat gegenüber der Funktion SVERWEIS wesentlich mehr Möglichkeiten. Allein die letzten beiden Parameter ermöglichen theoretisch 4 ✕ 4 = 16 verschiedene Kombinationen. Praktisch sind es nur 14 verschiedene Möglichkeiten. Denn wenn der Parameter Vergleichsmodus gleich 2 ist (Suche mit Wildcards), haben sowohl die Suchmodi 1 und 2 als auch die Suchmodi -1 und -2 dieselbe Wirkung.

Beispiel 1:

In der Liste wird nach dem Mitarbeiter 'Müller' gesucht.

Die verwendete Formel lautet:
= XVerweis ($C$28; $C$5:$C$24; $B$5:$G$24; "- -"; ; -1)

Es wird nach genauer Übereinstimmung gesucht, denn für den fehlenden Parameter Vergleichsmodus wird der Standardwert 0 verwendet.
Beginnend mit dem letzten Element der Liste wird in umgekehrter Reihenfolge gesucht, da der Parameter Suchmodus gleich -1 ist. Somit wird der letzte Eintrag des Mitarbeiters 'Müller' in Zeile 18 gefunden.

Dieses Beispiel zeigt fünf der neuen Möglichkeiten:

1. Es können mehrere Werte zurückgegeben werden, nicht nur einer (hier die sechs Werte in den Zellen B31:G31).
2. An Stelle einer Spaltennummer wird die Adresse einer Spalte angegeben. Somit können auch Werte links vom Suchbereich zurückgegeben werden.
3. Die Liste kann auch in umgekehrter Reihenfolge durchsucht werden, so dass das letzte Vorkommen des Suchbegriffs gefunden wird.
4. Man kann einen String angeben, der bei erfolgloser Suche an Stelle des bisherigen Error-Codes zurückgegeben wird.
5. Standard für die Suche ist jetzt die genaue Überstimmung.

Weitere Neuerungen:

• Man kann wählen, ob man eine unsortierte Liste durchsuchen lässt (Suchmodi 1 und -1) oder eine sortierte Liste (Suchmodi 2 und -2). Auch bei einer unsortierten Liste wird das nächstgrößere bzw. das nächstkleinere Element gefunden.
• Man kann jetzt auch nach leeren Feldern suchen (für den ersten Parameter wird nichts eingetragen).
• Wenn für den Parameter 'Rückgabematrix' ein Bereich auf dem Tabellenblatt - also kein Array - angegeben wird, was meistens der Fall ist, gibt die Funktion XVERWEIS nicht die gefundenen Werte zurück, sondern einen entsprechenden Zellbezug.

Dieser letzte Punkt eröffnet eine ganze Palette neuer Möglichkeiten. Denn nun kann die Funktion XVERWEIS in anderen Formeln an denjenigen Stellen eingesetzt werden, wo Zellbezüge erwartet werden.

Beispiel 2

Das Beispiel 2 zeigt einen solchen Fall. Zwei XVerweis-Funktionen werden als Parameter in die SUMME-Funktion eingesetzt.

Die Formel in Zelle G28 lautet:
= SUMME (XVerweis ($C$28; $F$5:$F$24; $G$5:$G$24) : XVerweis ($E$28; $F$5:$F$24; $G$5:$G$24; ; ;-1)).
Voraussetzung ist, dass die Jahreszahlen aufsteigend sortiert sind.

Um die Summe der Umsätze von 2020 bis 2022 zu berechnen, kann man die Formel = SUMME (G10:G22) verwenden. Um den Bereich variabel zu machen, ersetzen wir die beiden Adressen G10 und G22 durch XVerweis-Aufrufe.

Den Zellbezug G10 ersetzen wir durch den Ausdruck XVerweis ($C$28; $F$5:$F$24; $G$5:$G$24). Die XVerweis-Funktion sucht nach dem Jahr 2020 in normaler Suchrichtung und findet deshalb die erste Jahreszahl 2020 in der 6. Zeile der Liste. Aus dem Rückgabebereich $G$5:$G$24 gibt sie daher den Zellbezug $G$10 zurück.

Den Zellbezug G22 ersetzen wir durch den Ausdruck XVerweis ($E$28; $F$5:$F$24; $G$5:$G$24; ; ;-1). Die XVerweis-Funktion sucht nach dem Jahr 2022 in umgekehrter Suchrichtung und findet deshalb die letzte Jahreszahl 2022 in der 18. Zeile der Liste. Aus dem Rückgabebereich $G$5:$G$24 gibt sie daher den Zellbezug $G$22 zurück.

Zwischen den beiden XVerweis-Funktionen steht nur noch der Bereichsdoppelpunkt, der aus den beiden gefundenen Adressen G10 und G22 einen Bereich macht, über den die SUMME-Funktion summiert. Auf diese Weise kann man in den Eingabefeldern C28 und E28 nun verschiedene Jahreszahlen eintragen und bekommt immer die Summe der Umsätze für diesen Zeitraum.

Zwei Zusatzbemerkungen:
1.  Wenn man jetzt auf die Idee kommt, bei einer unsortierten Liste den zweiten und dritten Parameter in den XVERWEIS-Ausdrücken durch SORTIEREN(...) zu ersetzen, wird man den Fehlercode #WERT erhalten. Der Grund liegt darin, dass die Funktion SORTIEREN keinen Zellbezug zurückgibt, sondern ein Array von Werten. Wird nun der Funktion XVERWEIS kein Zellbezug übergeben, sondern ein Array, gibt sie auch keinen Zellbezug zurück, sondern gleichfalls ein Array. Die Funktion SUMME benötigt jedoch vor und nach dem Bereichsdoppelpunkt je einen Zellbezug.

2.  Das Beispiel 2 zeigt ganz gut diesen Aspekt der Funktion XVERWEIS (Rückgabe eines Zellbezugs).
In der Praxis würde man diese Aufgabenstellung jedoch nach wie vor mit Hilfe der Funktion SUMMEWENNS lösen:
= SUMMEWENNS ($G$5:$G$24; $F$5:$F$24; ">="&C28; $F$5:$F$24; "<="&E28)
Dieser Formel ist es egal, ob die Jahreszahlen sortiert sind oder nicht.

Die Suche in einer unsortierten Liste hat einen Geschwindigkeitsnachteil, denn die Suche in einer sortierten Liste nach dem sog. binären Suchverfahren (Suchmodus 2 oder -2) ist wesentlich schneller.

Die binäre Suche funktioniert so wie das Zahlenraten, bei dem man eine Zahl zwischen 1 und 1000 sucht:
Man wählt als erste Zahl die Mitte, also 500, fragt, ob dies die gesuchte Zahl sei und bekommt dann mitgeteilt, dass die gesuchte Zahl größer ist. Daraufhin wählt man vom in Frage kommenden Bereich 501 bis 1000 wieder die Mitte, also 750, und bekommt die Rückmeldung, dass die Zahl kleiner ist. Wenn man dies auf die gleiche Weise weiter fortsetzt, hat man spätestens nach 10 Fragen (und vergleichenden Rückmeldungen) die gesuchte Zahl gefunden.

Bei einer unsortierten Liste von 1000 Elementen muss man der Reihe nach alle Werte in der Liste vergleichen (sequentielle Suche). Im ungünstigsten Fall hat man 1000 Vergleiche, im Durchschnitt ungefähr 500 Vergleiche.

Zugabe:
Die Funktion XVERWEIS2

Die Funktion XVERWEIS2 ist keine der in Excel 365 verfügbaren Funktionen, sondern von mir frei erfunden.

Meine Motivation: In den Excel-Foren wird häufig nach einer VERWEIS-Funktion mit mehreren Suchkriterien gefragt und auch nach einer VERWEIS-Funktion, die eine Liste aller gefundenen Übereinstimmungen zurückgibt.

Die Funktion XVERWEIS2 ermöglicht zusätzlich zur Funktion XVERWEIS drei Dinge:

1. Mehrere Suchkriterien
2. Wildcards in allen Suchkriterien (optional)
3. Rückgabe aller gefundenen Übereinstimmungen (optional)

Der dritte Punkt ermöglicht das Filtern von Daten mit Hilfe von Wildcards (*, ?, ~) - siehe ▸Beispiel 4 (Filtern mit Wildcards).

Syntax

= XVerweis2 ( Suchkriterium1; Suchmatrix1; Rückgabematrix; [wenn_nicht_gefunden];
   [Vergleichsmodus]; [Suchmodus];
   [Suchkriterium2]; [Suchmatrix2]; [Suchkriterium3]; [Suchmatrix3]; ... )

Die ersten sechs Parameter stimmen mit denen der Funktion XVERWEIS überein.

Die ersten beiden Parameter sind das 1. Suchkriterium und die 1. Suchmatrix. Nach dem sechsten Parameter kann eine beliebige Anzahl weiterer Paare bestehend aus Suchkriterium und Suchmatrix folgen.

Suchkriterien, die nicht spezifiziert sind oder den leeren String enthalten, haben keine Auswirkung auf die Suche.

Zu beachten ist, dass beim Parameter 'Vergleichsmodus' die Werte 1 und -1 ungültig sind. Die Suche nach einem nächstgrößeren oder nächstkleineren Wert wäre bei mehreren Suchkriterien nicht mehr eindeutig. Die Werte 0 und 2 haben dieselbe Bedeutung wie bei XVERWEIS.

Beim Parameter 'Suchmodus' haben die Werte 1 und -1 dieselbe Bedeutung wie bei XVERWEIS, die Werte 2 und -2 haben eine andere Bedeutung als bei XVERWEIS. Eine binäre Suche in vorsortierten Listen ergäbe bei mehreren Suchkriterien - und damit mehreren Listen - keinen Sinn mehr.

Beispiel 3:

Dieses Beispiel zeigt die Suche nach den vier Suchkriterien Mitarbeiter, Region, Produkt und Jahr.
Da bei 'Jahr' nichts eingetragen ist (Zelle F29), hat der vierte Parameter keine Auswirkung auf die Suche.

Die verwendete Formel lautet:
= XVerweis2 ($C$29; $C$6:$C$25; $B$6:$G$25; "nothing"; 0; 1; $D$29; $D$6:$D$25; $F$29; $F$6:$F$25; $E$29; $E$6:$E$25)

Der Vergleichsmodus ist 0 (exakte Suche), der Suchmodus ist 1 (normale Suchreihenfolge, Rückgabe der ersten gefundenen Übereinstimmung).

Beispiel 4 - Filtern mit Wildcards

Dieses Beispiel zeigt eine Suche nach den vier Suchkriterien Mitarbeiter, Region, Produkt und Jahr im Wildcard-Modus (Vergleichsmodus = 2) mit Rückgabe aller gefundenen Übereinstimmungen (Suchmodus = 2).
Die Suchreihenfolge ist normal, d. h. beginnend mit dem ersten Element der Liste.

Die verwendete Formel lautet:
= XVerweis2 ($J$3; $C$6:$C$25; $B$6:$G$25; "nichts"; 2; 2; $K$3; $D$6:$D$25; $L$3; $E$6:$E$25; $M$3; $F$6:$F$25)

Mit dem Suchmodus -2 würde man dasselbe Suchergebnis erhalten, jedoch in umgekehrter Reihenfolge.

Als Zielbereich für diese Formel wurde der Bereich I6:N25 vorselektiert. Damit ist genügend Raum, um im Extremfall auch die komplette Liste als Ergebnismatrix aufzunehmen.

Das Verhalten der Funktion XVERWEIS2 ist in diesem Beispiel identisch mit dem der FILTER-Funktion. Der Unterschied besteht nur darin, dass man zur Filterung mit XVERWEIS2 auch Wildcards (*, ?, ~) einsetzen kann, was bei der Funktion FILTER nicht möglich ist.

Sonderthema:
Die Funktion XVERWEIS in Matrixformeln

In einem Excel-Forum traf ich auf eine Fragestellung, zu der aus gutem Grund keine Antwort gefunden wurde - zumindest nicht nach den Vorstellungen des Fragers. Er wollte wissen, ob es eine Möglichkeit gibt, sein Problem mit einer einzigen Formel - ohne Zwischenergebnisse in ausgeblendeten Spalten - zu lösen.

Die Lösung wäre gewesen, einen Ausdruck mit einer SVERWEIS-Funktion zu einer Matrixformel zu machen, indem man an Stelle eines einzelnen Suchkriteriums eine Matrix (Liste) von mehreren Suchkriterien angibt. Dieser Ausdruck müsste dann als Argument in eine ZÄHLENWENN-Funktion eingesetzt werden.

Die Überraschung:
Beim Ausprobieren stellte ich fest, dass diese Lösungsformel aufgrund einer fehlerhaften oder unvollständigen Implementierung der Microsoft-Programmierer in meinem Excel 2019 nicht funktionierte! Als ich in Excel-Online die SVERWEIS-Funktion durch XVERWEIS ersetzte, funktionierte es.

Ich möchte die Situation mit einem vereinfachten Beispiel nachstellen:
Im Bereich I5:I7 werden die Namen dreier beliebiger Mitarbeiter eingegeben.
In Zelle K13 soll die Summe der Umsätze dieser drei ausgewählten Mitarbeiter angezeigt werden.

Der Bereich K5:K7 wurde selektiert, die Formel = SVERWEIS ($I$5:$I$7; $C$5:$G$24; 5; 0) in die Bearbeitungszeile eingegeben und mit STRG-UMSCHALT-EINGABE abgeschlossen. Die SVERWEIS-Funktion gibt eine Matrix (Liste) von drei Umsatzzahlen zurück, die im Bereich Bereich K5:K7 dargestellt werden.

Jeder, der mit Matrixformeln vertraut ist, erwartet, dass man dieses 'Zwischenergebnis' im Bereich K5:K7 nun nicht benötigt, sondern den SVERWEIS-Ausdruck gleich als Parameter in eine SUMME-Funktion einsetzen kann. Dies ist in der Zelle K13 getan worden. Aber die SUMME-Funktion bekommt nicht die korrekte Liste von der SVERWEIS-Funktion übergeben. Da hat Microsoft einen BUG hinterlassen und seine Arbeitskraft lieber in die nachfolgenden Versionen investiert.

Dasselbe Beispiel in Online-Excel mit der Funktion XVERWEIS funktioniert erwartungtsgemäß:

Bei der Beschäftigung mit diesem Beispiel ist mir bewusst geworden, dass ich ganz vergessen habe, meine UDF 'XVerweis' so zu programmieren, dass sie auch eine Matrix von mehreren Suchkriterien als ersten Parameter verarbeiten kann.

Ab der Version 3.7 der Downloaddatei ist dieser Mangel behoben und die Funktionen 'XVerweis' und 'XVergleich' können nun auch in Matrixformeln eingesetzt werden.

Ein weiterer Microsoft BUG ist mir bei der Originalfunktion XVERWEIS aufgefallen:

Mit dem dritten Parameter der Funktion XVERWEIS, der Rückgabematrix, steuert man, welche Spalten des gefundenen Datensatzes zurückgegeben werden. In diesem Beispiel besagt die Rückgabematrix $B$5:$G$24, dass alle Spalten von B bis G zurückgegeben werden sollen. Dies funktioniert bei Microsoft aber nur, wenn nach einem einzelnen Datensatz gesucht wird (Formel in Zelle K13). Sucht man nach mehreren Datensätzen (Formel in Zelle K5), so wird für jeden Treffer nur die erste Spalte zurückgegeben.

Zumindest in Excel-Online (getestet am 06.01.2023) bekomme ich diesen Effekt. Ich vermute deshalb, dass es in Excel 365 genauso ist.

Meine UDF 'XVerweis' gibt alle gewünschten Spalten zurück:

Für Benutzer von Excel 365

Wer Excel 365 verwendet und an den beiden Sonderfunktionen SORTIERENNACH2 und XVERWEIS2 interessiert ist, kann hier eine Exceldatei herunterladen, die nur diese beiden UDFs enthält:
▸Download der Exceldatei
(Version 3.4 - Letzte Änderung: 15.03.2022)

Tutorial:   Excel-UDF erstellen

In diesem Tutorial wird am Beispiel der Funktion XVERWEIS2 gezeigt, wie man eine solche UDF mit Hilfe von VBA erstellt und welche Besonderheiten dabei zu beachten sind. Alle Phasen von der Auswertung der Parameter über die Implementierung des Suchvorgangs bis hin zur Aufbereitung des Rückgabewertes werden ausführlich erklärt.

Tutorial
Excel-UDF erstellen

▸Excel_UDF_Tutorial.pdf    (Letzte Änderung: 27.03.2022)

Wissen teilen ist die Zukunft der Menschheit