XVERWEIS() „für Dummies“

Die in EXCEL365 neu eingeführte „Zauber“-Formel XVERWEIS() ist sehr mächtig und eröffnet völlig neue Möglichkeiten bei der täglichen Arbeit. Sie ist die moderne Variante des SVERWEIS() und löst diesen ab. XVERWEIS() ermöglicht umfangreiche Optionen bei der Datenauswertung. Wichtigste Verbesserung: Sie können in Tabellen jetzt auch „nach links“ suchen. Die Formel kann auch aus Tabellen mehrere Werte gleichzeitig ausgeben und macht dadurch die Arbeit effizienter. Probieren Sie die beschriebenen Möglichkeiten mit unserer Beispieldatei doch einmal direkt aus.

XVERWEIS() „für Dummies“

Die Excel-Funk­ti­on SVERWEIS() hat einen gro­ßen Schwach­punkt: in der Ziel­ta­bel­le kann sie nur von links nach rechts suchen. Wenn sich der gesuch­te Wert links von der Such­spal­te befin­det, kann der SVERWEIS() nicht ein­ge­setzt wer­den. Auch in unsor­tier­ten Ziel­ta­bel­len lie­fert SVERWEIS() fal­sche Ergeb­nis­se, wenn man den drit­ten Para­me­ter nicht auf „FALSCH“ setzt.
Lie­fert SVERWEIS() kein Ergeb­nis, erhält man #NV! als Feh­ler­wert und muss die For­mel mit der WENNFEHLER()-Funktion umschlie­ßen. Bei­trag zum SVERWEIS() und WVERWEIS().

Der neue XVERWEIS() hat kei­ne die­ser Ein­schrän­kun­gen und kann noch eini­ges dar­über hinaus.

1.    Die Formel des XVERWEIS() in Excel

Die XVERWEIS()-Funktion durch­sucht eine Tabel­le und gibt dann das Ele­ment zurück, dass der ers­ten gefun­de­nen Über­ein­stim­mung entspricht.

Las­sen Sie sich von der For­mel bit­te nicht erschre­cken! Sie wer­den das Mus­ter bald ver­in­ner­li­chen. Sie brau­chen es auch „nur“ ver­ste­hen, aber nicht aus­wen­dig können. 

Excel hilft Ihnen! Wenn Sie die For­mel ein­tip­pen, zeigt Ihnen Excel die benö­tig­ten Anga­ben der For­mel an, sobald Sie ( gedrückt haben. (Das gilt übri­gens grund­sätz­lich in Excel.)

=XVERWEIS(Suchkriterium; Such­ma­trix; Rück­ga­be­ma­trix; [wenn_nicht gefunden];[Vergleichsmodus])

Such­kri­te­ri­um:                das ist der Begriff, nach dem Sie suchen

Such­ma­trix:                   steht für den Such­be­reich, in dem Sie Infor­ma­tio­nen suchen

Rück­ga­be­ma­trix:            die Spal­te mit den gewünsch­ten Ergeb­nis­sen, die­se kann auch links vom Such­be­reich liegen.

[wenn_nicht gefun­den]: legt fest, was aus­ge­ge­ben soll, wenn der gesuch­te Wert nicht gefun­den wurde

[Ver­gleichs­mo­dus]:         stan­dard­mä­ßig wird immer die genaue Über­ein­stim­mung gesucht

[Such­mo­dus]:                 stan­dard­mä­ßig wird die die Suche beim ers­ten Ele­ment begonnen

Der XVERWEIS() unter­schei­det zwi­schen der Such­ma­trix (die zu durch­su­chen­de Spal­te) und einer Rück­ga­be­ma­trix (die Spal­te mit den gewünsch­ten Ergeb­nis­sen). Damit kann man auch Ergeb­nis­se fin­den, die links von der Such­spal­te liegen.

In die­sem Bei­spiel wird die Vor­wahl eines Lan­des – von links nach rechts – gefun­den. Das gin­ge auch – wie bis­her – mit dem SVERWEIS().
Die Suche des Lan­des anhand der Vor­wahl – von rechts nach links – ist die wich­ti­ge Neue­rung des XVERWEIS().

Auf den im SVERWEIS() fast immer not­wen­di­gen FALSCH-Para­me­ter kann ver­zich­tet wer­den. XVERWEIS() geht davon aus, dass immer die genaue Über­ein­stim­mung gesucht wer­den soll.

Mit der Ein­ga­be einer ein­zi­gen For­mel kön­nen auch meh­re­re Wer­te gelie­fert werden:

Dazu gibt man in der Rück­ga­be­ma­trix meh­re­re Spal­ten an (hier: C4:D13).

2.    Beispiele für die Verwendung von XVERWEIS() 

Am Bei­spiel einer Umsatz­ta­bel­le sehen Sie die unter­schied­li­chen Mög­lich­kei­ten beim Ein­satz des XVERWEIS():

In die­sem Bei­spiel sieht man noch­mals die grund­sätz­li­che Anwen­dung der XVERWEIS()-Funktion. Die Rück­ga­be­ma­trix (D4:D9) ist in die For­mel eingegeben.

Mehr Dyna­mik erhält man, wenn der Monat frei wähl­bar ist:

Mit zwei ver­schach­tel­ten XVERWEIS()-Funktionen kön­nen Tabel­len auch in bei­de Rich­tun­gen durch­sucht wer­den.

Anstatt für den drit­ten Para­me­ter (Rück­ga­be­ma­trix) einen fes­ten Zell­be­reich anzu­ge­ben, setzt man einen XVERWEIS() ein. Damit wer­den die Pro­duk­te nach dem gewähl­ten Wert durch­sucht. Der Rück­ga­be­wert ist nicht eine ein­zel­ne Zel­le, son­dern ein Zellbereich.

Mit der Kom­bi­na­ti­on der SUMME()-Funktion las­sen sich nicht nur Ein­zel­wer­te, son­dern gan­ze Zel­len­be­rei­che zurück­lie­fern. Im Bei­spiel wird die Sum­me aller Ver­käu­fe zwi­schen zwei frei wähl­ba­ren Mona­ten ausgegeben.

Der ers­te XVERWEIS() lie­fert für die SUMME() die Von-Spal­te, der zwei­te XVERWEIS() die Bis-Spal­te – getrennt durch den Dop­pel­punkt der SUMME()-Funktion.

3.    Fazit

Jetzt haben Sie alles Wich­ti­ge und eini­ge Bei­spie­le zum Excel XVERWEIS() kennengelernt.

Am bes­ten ver­su­chen Sie jetzt gleich ein­mal selbst, die For­meln anzu­wen­den. Die For­mel ist sehr mäch­tig und eröff­net völ­lig neue Mög­lich­kei­ten bei der täg­li­chen Arbeit mit EXCEL.

Viel Erfolg dabei!

Latest posts by Win­fried Eitel (see all)

Schreiben Sie einen Kommentar

Ihre E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert