Neben den Funktionen SUMME und MITTELWERT gehört der SVERWEIS zu den am meisten eingesetzten Funktionen in Excel. Die neue Funktion XVERWEIS erweitert die Einsatzmöglichkeiten der herkömmlichen Funktion SVERWEIS.

Die Funktion XVERWEIS steht im Januar 2020 nur den Excel-Anwendern zur Verfügung, die am Insiderprogramm von Office 365 teilnehmen oder den monatlichen, gezielten Kanal im Rahmen von Office 365 abonniert haben.

Wie die Funktion SVEWREIS sucht die Funktion XVERWEIS in einer Tabelle nach dem ersten Vorkommen eines Wertes oder Ausdrucks, der entweder exakt mit dem Suchbegriff übereinstimmt oder der nach dem größten, unteren Grenzwert. Wird der Suchbegriff in der Tabelle nicht gefunden, wird der Fehlerwert #NV (nicht vorhanden) zurückgegeben.

XVERWEIS und SVERWEIS im Vergleich

Die Funktion XVERWEIS bietet folgende Erweiterungen und Änderungen gegenüber dem SVERWEIS.

• Im Gegensatz zum SVERWEIS können Sie mit der Funktion XVERWEIS den Suchbegriff in jeder Spalte der Tabelle suchen. Beim SVERWEIS wird nur die erste Spalte einer Tabelle durchsucht.
• Standardmäßig wird beim XVERWEIS nach einem Wert gesucht, der exakt mit dem angegebenen Suchbegriff übereinstimmt.
• Der XVEREWIS kann eine oder mehrere Zellen aus der Zeile zurückgegeben, in der der passende Suchbegriff gefunden wurde. Beim SERVWEIS kann durch die Angabe einer Spaltennummer nur ein einzelner Zellenwert zurückgegeben werden.
• Der XVERWEIS hat einen Parameter eingebaut, mit Sie einen Ausdruck definieren können, der beim Nichtvorhandensein eines Suchbegriffs erscheinen soll. Beim SVERWEIS benötigen Sie dazu eine weitere Funktion, wie z.B. die Funktion WENNNV.
• Mit dem XVERWEIS können Sie eine Tabelle statt von oben nach unten auch von unten nach oben durchsuchen.
• Beim XVERWEIS kann der Suchbegriff Platzhalter wie * für ein oder mehrere beliebige Zeichen oder ein ? für genau ein Zeichen enthalten

XVERWEIS im Funktionsassistenten

Im Funktionsassistenten sieht die Funktion XVERWEIS folgendermaßen aus:

Die Funktion XVERWEIS kennt insgesamt sechs Parameter. Die ersten drei Parameter sind obligatorisch. Der sechste Parameter, der im obigen Bild erst durch Scrollen angezeigt wird, lautet Suchmodus.

Suche nach einem Begriff in der zweiten Spalte einer Tabelle

Im folgenden Beispiel soll in einer Artikelliste in der zweiten Spalte nach dem Artikel mit der Bezeichnung PC 6 gesucht werden. Falls der Artikel gefunden wird, sollen mehrere Angaben zum gefundenen Artikel zurückgegeben werden:

Die Formel in Zelle H2 lautet:

=XVERWEIS(G2;B2:B18;A2:D18)

Die Formel sucht in dem Zellbereich B2:B18 nach dem Begriff PC 6, der in Zelle G2 eingegeben wurde. Wenn der Artikel gefunden wird, sollen alle Angaben zu dem Artikel, die in den Spalten A bis D erscheinen, zurückgegeben werden. Da mehr als ein Wert zurückgegeben wird, wird ein Überlaufbereich eingerichtet. Den Überlaufbereich erkennen Sie am blauen Rahmen. Weitere Informationen zu den Eigenschaften von Überlaufbereichen finden Sie hier. Wenn Sie für den dritten Parameter Rückgabematrix nur Zellen einer Spalte angeben, wird kein Überlaufbereich generiert.

Falls ein Artikel nicht gefunden wird, erscheint der Fehlerwert #NV.

Wenn Sie den Fehlerwert durch einen besseren Ausdruck ersetzen möchten, geben Sie diesen Ausdruck in der XVERWEIS-Funktion als vierten Parameter an:

In dem obigen Beispiel lautet die Formel in Zelle H2 folgendermaßen:

=XVERWEIS(G2;B2:B18;A2:D18;”Artikel existiert nicht”)

Wenn Sie die Artikelliste aus dem obigen in eine Tabelle mit dem Namen Bestellungen umwandeln, lautet die Formel in Zelle H2 folgendermaßen:

=XVERWEIS(G2;Bestellungen[Bezeichnung];Bestellungen[[Bezeichnung]:[Preis]])

Suche nach einem Begriff mit Platzhalter

Mit der Funktion XVERWEIS können Sie mit Platzhalterzeichen nach einem Begriff in einer beliebigen Spalte einer Tabelle suchen.

In dem folgenden Beispiel soll im Zellbereich B2 bis B18 nach einer Bezeichnung gesucht werden, die mit PC anfängt. In Zelle G2 besteht der Ausdruck PC*.

Die passende Formel in Zelle H2 lautet:

=XVERWEIS(G2;B2:B18;A2:D18;”Artikel existiert nicht”;2)

Für die Platzhaltersuche wird der fünfte Parameter benötigt. Die Zahl 2 steht für eine Platzhaltersuche. Falls Sie den fünften Parameter weglassen, sucht XVERWEIS nach einer Bezeichnung, die genau PC* lautet.

Suche nach dem nächst kleinerem Element in einer Tabelle

Wie die Funktion SVERWEIS können Sie mit der Funktion XVERWEIS auch nach dem nächst kleinerem Element in der Spalte einer Tabelle suchen. Der Unterschied ist, dass Sie diese Suche beim XVERWEIS explizit angeben müssen. Beim SVERWEIS ist diese Suche Standard, wenn Sie den vierten Parameter der Funktion nicht angeben.

In dem Beispiel soll im Zellbereich D2:D18 nach dem ersten Preis gesucht werden, der gleich 1000 € ist oder wenn keiner gefunden wird, nach dem nächst kleinerem Wert.

Die Formel in Zelle G2 lautet:

=XVERWEIS(G2;D2:D18;A2:D18;”Artikel existiert nicht”;-1)

Entscheidend ist hier der fünfte Parameter mit dem Wert –1. Wenn Sie als fünften Parameter den Wert 1 eingeben, wird nach einem Artikel gesucht mit einem Preis von 1000 € oder höher.

Der Vollständigkeit halber sei erwähnt, dass Sie als fünften Parameter noch den Wert 0 angeben können. Der Wert 0 ist die Standardeinstellung und kann weggelassen werden. In diesem Fall sucht XVERWEIS in einer Spalte nach einer exakten Übereinstimmung mit dem angegeben Suchbegriff.

Suche mit zwei Kriterien

Der zweite Parameter der Funktion XVERWEIS lautet Suchmatrix. Sie können über die Suchmatrix zwei oder mehrere Bedingungen angegeben, die ein gesuchter Artikel erfüllen soll.

In dem folgenden Beispiel soll nach dem ersten Artikel gesucht werden, der zur Produktgruppe Laptop gehört und dessen Preis über 1100 € liegt. Die beiden Kriterien stehen in den Zellen G2 und H2.

Die Formel in Zelle I2 lautet:

=XVERWEIS(1;(C2:C18=G2)*(D2:D18>H2);A2:D18)

Die Funktion sucht im Zellbereich C2:C18 nach dem Begriff Laptop und im Zellbereich D2:D18 nach einem Preis größer als 1100 €. Der erste Artikel, der beide Bedingungen erfüllt, ist der Artikel mit der Bezeichnung Laptop 2. Wie kommt die Formel zu diesem Ergebnis? Es wird intern eine Tabelle aufgebaut, für die jede Zeile und jede Bedingung eine 0 vergibt, wenn die Bedingung nicht erfüllt ist, und eine 1, wenn sie erfüllt ist. Für jede Zeile werden die beiden Werte multipliziert. Wenn beide Bedingungen erfüllt sind, ist das Ergebnis 1. Das Ergebnis 1 erscheint als erstes beim Artikel Laptop 2.

Hinweise

• Die Funktion XVERWEIS kann nicht nur den SVERWEIS ersetzen, sondern auch den WVERWEIS, in dem Sie als Suchbereich mehrere Zellen einer Zeile und als Rückgabebereich Zellen in einer oder mehrere Zeilen eingeben.
• Sie können mit der Funktion XVERWEIS nach einem Wert oder Ausdruck suchen, indem Sie statt von oben nach unten bzw. links nach rechts von unten nach oben bzw. von rechts nach links suchen. Geben Sie dazu für den sechsten Parameter Suchmodus den Wert -1 ein. Dann beginnt die Suche beim letzten Element in der Suchspalte bzw. Suchzeile.
• Achten Sie darauf, dass der Zellbereich, in dem Sie nach einem Begriff suchen, genauso viele Zeilen bzw. Spalten hat wie der Rückgabebereich. Sonst erscheint der Fehlerwert #WERT.
• Die Funktion XVERWEIS erlaubt eine Binärsuche in auf- oder absteigender Reihenfolge. Geben Sie dafür den sechsten Parameter Suchmodus den Wert 2 für eine auf- oder den Wert -2 für eine absteigende Suchreihenfolge ein. Um richtige Ergebnisse zu erhalten, muss der durchzusuchende Bereich auf- bzw. absteigend sortiert sein. Bei einer großen Tabelle dürfte eine solche Suche schneller sein.

Der zweite Parameter Suchbereich darf maximal eine Spalte oder eine Zeile umfassen. Sonst erscheint der Fehlerwert #WERT.