Den XVERWEIS ausreizen 39

Der XVERWEIS an sich ist schon eine Bereicherung. Aber da geht noch deutlich mehr!
 

Wer Excel aus Microsoft 365/Office 365 oder das brandneue Excel 2021 einsetzt, hat vermutlich schon Bekanntschaft mit der XVERWEIS-Funktion gemacht oder zumindest davon gehört. Es lohnt sich unbedingt, sich mit dem Nachfolger des berühmt-berüchtigten SVERWEIS zu beschäftigen, bringt er doch wesentliche Verbesserungen mit sich.

Darüber hatte ich auch vor längerer Zeit schon im Artikel Goodbye SVERWEIS, willkommen XVERWEIS! geschrieben.

Wenn du also schon mit XVERWEIS gearbeitet hast, dann zeige ich dir heute mit ein paar Spezialfällen, wie man noch viel mehr herausholen kann. Oder du kommst auf den Geschmack, dich endlich mit dieser tollen Funktion zu beschäftigen und den SVERWEIS endgültig zu begraben.

Und so geht’s:

Wichtiger Hinweis zu Beginn:
Falls du noch Excel 2019 oder älter einsetzt, steht dir diese Funktion leider nicht zur Verfügung. Weiterlesen darfst du natürlich trotzdem 😉

Spezialfall 1: XVERWEIS in zwei Richtungen

Ich habe eine klein Umsatztabelle vorbereitet, die für sechs Verkäufe die Umsätze für die Monate Januar bis Juni enthält. Nun möchte ich über zwei Eingabefelder einen Monat und einen Verkäufer festlegen und mir den dazu passenden Umsatz ausgeben lassen (die Beispieldatei kannst du dir hier herunterladen):

Die Ausgangslage: Umsatztabelle nach Verkäufer und Monat

Die Ausgangslage: Umsatztabelle nach Verkäufer und Monat


Die Herausforderung ist also, dass wir die Tabelle sowohl vertikal (nach dem Verkäufer) als auch horizontal (nach dem Monat) durchsuchen müssen und somit zwei Suchkriterien haben.

Der normale XVERWEIS mit nur einem Suchkriterium funktioniert so:
=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn nicht gefunden]; [Vergleichsmodus]; [Suchmodus])
Die letzten drei Parameter sind optional und für unsere Zwecke nicht erforderlich.

Würde ich nur nach einem Verkäuferumsatz für den, sagen wir mal, März suchen, wäre die Formel bezogen auf mein Beispiel:
=XVERWEIS(E13;A4:A9;D4:D9)

Zuerst ein Standardfall für XVWEIS

Zuerst ein Standardfall für XVWEIS

Wie schafft man es nun, dass die Suchmatrix variabel ist?

Ganz einfach: Über einen zweiten XVERWEIS im XVERWEIS!

Spezialfall 1: Suche mit 2 Kriterien

Spezialfall 1: Suche mit 2 Kriterien


=XVERWEIS(B12;A4:A9;XVERWEIS(B11;B3:G3;B4:G9))
Anstatt für den dritten Parameter (Rückgabematrix) einen festen Zellenbereich anzugeben, setzten wir gleich einen XVERWEIS ein. Damit werden die Monatsüberschriften nach dem gewählten Monat durchsucht. Die Besonderheit hier ist, dass der Rückgabebereich nicht eine einzelne Zeile ist, sondern ein ganzer Zeilenbereich (B4:G9).

Spezialfall 2: Dynamische Summenspalten über alle Zeilen

Im Normalfall gibt XVERWEIS einen einzelnen Wert zurück, so wie auch im vorhergehenden Beispiel. In Kombination mit der SUMME-Funktion lässt sich damit aber auch ganze Zellenbereiche zurückliefern.

In diesem Beispiel möchte ich die Summe der Umsätze aller Verkäufer zwischen zwei frei wählbaren Monaten ausgeben:

Spezialfall 2: Dynamische Summe

Spezialfall 2: Dynamische Summe

Auch das ist kein Problem. Benötigt werden wieder zwei XVERWEIS-Funktionen, die innerhalb der SUMME-Funktion zum Einsatz kommen und dort den „von..bis“-Bereich bestimmen:

Spezialfall 2: Lösung

Spezialfall 2: Lösung


=SUMME(XVERWEIS(B16;B3:G3;B4:G9):XVERWEIS(B17;B3:G3;B4:G9))

Der erste XVERWEIS liefert sozusagen die Von-Spalte, der zweite XVERWEIS die Bis-Spalte, getrennt durch einen Doppelpunkt. Das Geniale ist, die Monate müssen in den beiden Eingabezellen nicht einmal in der richtigen Reihenfolge angegeben werden. Von Februar bis April liefert das gleiche Ergebnis wie von April bis Februar!

Spezialfall 3: Dynamische Summenspalten über eine Zeile

Setzen wir gleich noch eins drauf. Zu den frei wählbaren Anfangs- und Endmonaten möchte ich jetzt auch noch einen bestimmten Verkäufer angeben:

Spezialfall 3: Summe über einen Zeitraum, variabler Verkäufer

Spezialfall 3: Summe über einen Zeitraum, variabler Verkäufer


Wir haben also einen dynamischen Spaltenbereich kombiniert mit einer dynamischen Zeile.

Die Formel wird schon etwas umfangreicher und sieht jetzt so aus:

Spezialfall 3: Lösung

Spezialfall 3: Lösung


=SUMME(XVERWEIS(B21;B3:G3;XVERWEIS(B23;A4:A9;B4:G9)):XVERWEIS(B22;B3:G3;XVERWEIS(B23;A4:A9;B4:G9)))

Das heißt, die beiden Von..Bis-Bereiche innerhalb der SUMME-Funktion werden jeweils durch verschachtelte XVERWEIS-Funktionen dynamisiert. Jeder dieser verschachtelten XVERWEISE ist im Grunde identisch mit dem Beispiel aus Spezialfall 1. Nur dass hier eben zwei davon zum Einsatz kommen.

Spezialfall 4: Dynamische Summenspalten und dynamische Zeilen

Um dem ganzen die Krone aufzusetzen, soll jetzt zum Monatsbereich auch noch ein Verkäufer-Bereich angegeben werden.

Spezialfall 4: Dynamische Summen über zwei Richtungen

Spezialfall 4: Dynamische Summen über zwei Richtungen

Du ahnst es vermutlich schon und musst jetzt ganz tapfer sein. Die Formel wird nämlich ein ziemliches Monster:

Spezialfall 4: Lösung

Spezialfall 4: Lösung


=SUMME((XVERWEIS(B27;B3:G3;XVERWEIS(B29;A4:A9;B4:G9)):XVERWEIS(B28;B3:G3;XVERWEIS(B29;A4:A9;B4:G9))):(XVERWEIS(B27;B3:G3;XVERWEIS(B30;A4:A9;B4:G9)):XVERWEIS(B28;B3:G3;XVERWEIS(B30;A4:A9;B4:G9))))
Denn jetzt haben wir es mit zwei jeweils zweimal verschachtelten XVERWEIS-Funktionen innerhalb der SUMME-Funktion zu tun. Nur dadurch erreicht man eine Dynamisierung in beide Richtungen.

Ich gebe zu, dass diese Formel nicht ganz einfach und auch nicht für den täglichen Gebrauch geeignet ist. Aber wir besprechen hier schließlich Spezialfälle und da ist es doch irgendwie beruhigend zu wissen, dass es ginge, falls man es bräuchte 🙂

Welche interessanten Anwendungsmöglichkeiten für den XVERWEIS fallen dir noch ein?
Lass es uns in den Kommentaren wissen!

 

Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

39 Gedanken zu “Den XVERWEIS ausreizen

  • Avatar-Foto
    Marcel

    Und wo ist jetzt genau der Vorteil im Vergleich zu Index (in der Bezugsvariante) und Summenprodukt (für Angabe mehrerer Kriterien)?

    • Avatar-Foto
      Martin Weiß

      Hallo Marcel,

      ich gebe dir recht, es gibt hier nicht zwingend einen Vorteil. Es hängt von den tatsächlichen Gegebenheiten und Vorlieben ab und ist lediglich eine alternative Möglichkeit.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    John

    Hallo Martin,

    super Danke! Bei mir funktionieren auch die nachfolgenden Formeln. Diese sind jedoch etwas kürzer, deshalb schreibe ich diese mal hier rein. Gerne testen und bei Fehlfunktionen melden.

    Fall 3:
    =SUMME(XVERWEIS(B23;A4:A9;XVERWEIS(B21;$B$3:$G$3;B4:G9):XVERWEIS(B22;B3:G3;B4:G9)))

    Fall 4:
    =SUMME((XVERWEIS(B27;$B$3:$G$3;XVERWEIS(B29;A4:A9;B4:G9)):XVERWEIS(B28;B3:G3;XVERWEIS(B30;A4:A9;B4:G9))))

    VG
    John

    • Avatar-Foto
      Martin Weiß

      Hallo John,

      vielen Dank, das sind sehr elegante Lösungen! Vor allem der Fall 4 ist deutlich kürzer und damit besser als meine Variante.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Martin Röllin-Kälin

    Hallo Martin
    Ich freue mich immer, wenn ich von dir das News-Mail erhalte!
    Mit X-VERWEIS habe ich folgendes Problem: in der Suchmatrix habe ich teilweise mehr als ein Kriterium, das dem Suchkriterium entspricht. Da wird dann nur das Resultat vom ersten zurückgeliefert. Gäbe es eine Kombination mit einer Array-Formel oder sonst eine Lösung, damit alle Übereinstimmungen zurückgeliefert würden?
    Danke für’s Studieren, und herzliche Grüsse aus der Schweiz
    Martin

  • Avatar-Foto
    Gerhard Duscha

    Lieber Martin,
    Du erweiterst mein Denken bei jedem Newletter. Bisher habe ich (aus Bequemlichkeit) Summewenn dem einfachen XVERWEIS vorgezogen.
    Aber jetzt…
    Danke für die Info

    • Avatar-Foto
      Martin Weiß

      Hallo Gerhard,

      gerne, freut mich zu hören! Auch ich lerne immer wieder dazu 🙂

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Rainer Linkenbach

    Ich habe eine Matrix mit Konten, und möchte herausfinden, ob ein bestimmtes Konto darin vorkommt.
    Also z. B. Spalten A-J; Zeilen 1-100
    Irgendwo in dieser Matrix kann das Konto 9999 vorhanden sein, oder auch nicht, das möchte ich wissen. Wie beim SVERWEIS würde mir reichen, wenn das Kriterium angezeigt wird oder #NV erscheint.
    Praktische Anwendung ist die Buchungsmatrix im Rahmen einer NAV Einführung. In der upload Datei will ich das eben prüfen.
    Viele Grüße
    Rainer

    • Avatar-Foto
      Martin Weiß

      Hallo Rainer,

      das Problem wäre beispielsweise mit einer bedingten Formatierung zu lösen. Angenommen, die Kontenmatrix steht im Bereich A1:J100 und in Zelle L1 steht das gesuchte Konto.
      Dann markierst die diese Matrix und legst eine Formatierungsregel an:
      Menü „Start | Bedingte Formatierung | Neue Regel…“
      Als Regeltyp wählst Du „Formel zur Ermittlung der zu formatierenden Zellen verwenden“
      Und in das Eingeabefeld gibst du dann folgende Formel ein:
      =A1=$L$1
      (wenn deine Matrix eine andere Startzelle hat, dann musst du diese anstatt von A1 eingeben)
      Dann legst du noch eine Formatierung fest, z.B. gelber Hintergrund.

      Abhängig vom Wert, den du in Zelle L1 einträgst, sollte jetzt dieser Wert in der Matrix gelb hervorgehoben werden – oder eben nicht.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Julius

    Ich habe letztens versucht eine Lösung zu finden für eine Tabelle mit zwei Überschriftszeilen (erste Zeile mit Jahr, zweite Zeile mit Monat – super unpraktisch, aber in meinem Job bin ich leider häufig mit extrem schlecht verarbeitbaren Daten konfrontiert).

    Ich habe jetzt extra eine vereinfachte und übersichtlichere Tabelle erstellt, um sie hier zu posten, aber ich kann leider keine Screenshots anfügen. Ich umreiße es kurz:

    B1 bis E1: Kriterium 1, bei mir „A A B B“, entspricht im Ursprünglichen den Jahreszahlen (12 mal 2021, 12 mal 2022)
    B2 bis E2: Kriterium 2, bei mir „AA AB BA BB“, entspricht im Ursprünglichen den Monaten (2mal Januar bis Dezember)
    A3 bis A5: Kriterium 3, bei mir „1 2 3″, entspricht im Ursprünglichen verschiedenen Kunden
    B3 bis E5: Daten, bei mir spaßeshalber verschiedene Obstsorten, entspricht im Ursprünglichen den jeweiligen Kundenumsätzen

    B8: Auswahlfenster für Kriterium 1, bei mir A oder B, im Ursprünglichen die Jahreszahl 2021 oder 2022
    B9: Auswahlfenster für Kriterium 2, bei mir AA, AB, BA und BB, im Ursprünglichen die Monate Januar bis Dezember
    B10: Kriterium 3, bei mir 1, 2 oder 3, im Ursprünglichen die Kundenliste

    Die Auswahlfenster sind natürlich noch schön mit Dropdowns, damit lässt sich viel angenehmer und intuitiver arbeiten 😉

    Nun der interessante Teil, die Verformelung: =XVERWEIS(B10;A3:A5;XVERWEIS(B9;WENN(B1:E1=B8;B2:E2;““);B3:E5))

    Der Wenn-Bereich WENN(B1:E1=B8;B2:E2;““) gibt als Matrix den Bereich B2 bis E2 aus und lässt dabei die Zellen leer, die nicht unter dem entsprechenden Kriterium A (Zelle B8 in Bereich B1 bis E1) stehen.

    Dann habe ich einen Xverweis davor gesetzt: XVERWEIS(B9;WENN(B1:E1=B8;B2:E2;““);B3:E5). Das Array aus der Wenn-Funktion wird nun nach Kriterium 2 durchsucht. Sobald er gefunden ist, wird aus der entsprechenden Spalte der Bereich von Zeile 3 bis 5 als Array ausgegeben.

    Zuletzt kommt ein weiterer Xverweis davor: XVERWEIS(B9;WENN(B1:E1=B8;B2:E2;““);B3:E5). Dieser durchsucht den Bereich A3 bis A5 nach Kriterium 3. Die entsprechende Zeileninformation wendet er dann auf das im zweiten Step erzeugte Array an, das ja bereits nur noch die Ergebnisspalte für die gleichzeitig geltenden Kriterien 1 und 2 darstellt.

    Auf diese Weise können mehrere verschiedene Kriterien geltend gemacht werden. Je nach Wunsch und Möglichkeit stelle ich gerne meine Beispieldatei zur Verfügung!

    Ergänzung von Martin:
    Hier gibt es die Beispieldatei zum Download.

    • Avatar-Foto
      Martin Weiß

      Hallo Julius,

      danke für deine Lösung und die ausführliche Beschreibung dazu. Wenn du möchtest, kannst du mir deine Beispieldatei an info@tabellenexperte.de schicken und ich stelle sie dann hier in deinem Kommentar zum Download für andere Leser bereit.

      Schöne Grüße
      Martin

  • Avatar-Foto
    Holger Dörr

    Hallo Martin,
    großartiger Hinweis. Vielen Dank!

    Habe noch eine weitere Herausforderung:
    Mehrspaltige Tabelle, in den ersten drei Spalten stehen Suchkriterien, die ich kombiniert suchen möchte. Spalte A und B jeweils mit exakter Übereinstimmung; Spalte C soll das nächst kleinere Element gesucht werden. Rückgabematrix dann die Spalten weiter rechts.
    Spalten A bis C sind nicht sortiert.
    Kann ich den xverweis dann auch noch kombinieren, wenn die Suchkriterien in Spalten stehen und eine Kombi von Spalten und Zeilen sind. Ich habe schon dreifach-Kombi von sverweis versucht, aber klappt nicht richtig.
    Ich freue mich auf Deine Lösung – noch einen schönen Vatertag!

    Beste Grüße
    Holger

    • Avatar-Foto
      Holger Dörr

      Danke an Julius!
      Durch Deinen Lösungsansatz konnte ich auch meine Frage auflösen.
      Durch einen doppelte Wenn-Funktion habe ich den Bereich eingrenzt und ich konnte diesen Ergebnisbereich als Suchmatrix in der XVERWEIS-Funktion nutzen. Voilá! Funktioniert. Vielen Dank.
      Es hätte fast mit einem SVerweis geklappt, aber dieser hat leider nicht die Möglichkeiten den Vergleichsmodus so fein einzustellen.
      Danke nochmal.

  • Avatar-Foto
    Winfried

    Hallo Martin,
    vielen Dank für die ausführliche Erläuterung. Das ist sehr hilfreich.

    Spezialfall 1:
    „Anstatt für den dritten Parameter (Suchmatrix) einen festen Zellenbereich anzugeben, setzten wir gleich einen XVERWEIS ein. “
    Müsste es hier nicht „Rückgabematrix“ heißen?

    Liebe Grüße
    Winfried

    • Avatar-Foto
      Martin Weiß

      Hallo Winfried,

      absolut richtig, es muss Rückgabematrix heißen. Vielen Dank für den Hinweis, ist jetzt korrigiert.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Hartmut Faust

    Komme einfach nicht klar. Habe einen dynamischen Kalender erstellt. Mit dem Xverweis habe ich die bundeseinheitlichen Feiertage eingefügt. Jetzt möchte ich aber den Kalender so gestalten dass bundeseinheitlich genutzt werden kann. Im Kalender kann man das entsprechende Bundesland auswählen. In einem extra angelegten Tabellenblatt habe ich die Feiertage den Bundesländern zugeordnet.
    Habe auch schon sehr viel mit xverweis ausprobiert komme aber auf keine Lösung. (Wahrscheinlich Brett vor dem KopfI).
    Wenn Rheinland Pfalz ausgewählt wird, sollen auch die Feiertage für dieses Bundesland angezeigt werden.

      • Avatar-Foto
        Martin Weiß

        Hallo Hartmut,

        wenn ich es richtig sehe, benötigst du einen XVERWEIS in zwei Richtungen: Senkrecht für die Feiertage und waagerecht für die Bundesländer. Und ja, der XVERWEIS in zwei Richtungen ist am Anfang schon etwas ungewohnt, aber in deinem Fall wäre das ja das Beispiel 1 oben aus dem Artikel.

        Eine andere Variante hätte ich, falls du dich für den Newsletter angemeldet hast (oder dies vielleicht tun möchtest). Denn damit hast du Zugriff auf einen Download-Bereich mit einigen Excel-Vorlagen, unter anderem eine Kalendervorlage 2023. Und dort ist ebenfalls ein Feiertagskalender nach Bundestland enthalten. Allerdings nicht mit XVERWEIS, sondern noch etwas komplexer mit INDEX, VERGLEICH und INDIREKT, da es auch noch eine Länderauswahl für Österreich und die Schweiz gibt.

        Aber ansonsten würde ich mir das Beispiel 1 nochmal näher ansehen.

        Schöne Grüße,
        Martin

  • Avatar-Foto
    Sebastian

    Hallo.

    Wie immer, ein sehr guter Artikel.
    Ich zwinge mich inzwischen sogar immer nur noch den XVERWEIS statt den anderen Verweisen zu nutzen.
    Man verwendet ja ständig die alten Funktionen, weil „das hat man doch schon immer so gemacht“.
    Diese Funktion ist aber echt toll und soll auch bei mir den Einzug in die normale Verwendung finden.

    Ich schriebe aber selten ohne eine konkrete Frage 🙂
    Ist es mit dem XVERWEIS eigentlich auch möglich bestimmte Werte in Wertebereichen zu finden?
    Ich habe Tabelle, in denen Postleitzahlbereiche in den Spalten A (von) und B (bis) definiert sind, also z.B. 23701 – 23829.
    In Spalte C steht ein Wert, der für diesen Postleitzahlenbereich gilt.

    In einer weiteren Tabelle habe ich eine Postleitzahlenliste, die sämtliche in D existenten Postleitzahlen enthält.
    In dieser Tabelle soll nun in einer neuen Spalte der Wert entsprechend der ersten Tabelle eingetragen werden.

    Ich hänge gerade nämlich mit einer lächerlich langen ERSTERWERT-Funktion rum, die lediglich 126 Argumente (total wenig ;)) zulässt, bei über 200 Postleitzahlbereichen.
    Diese prüft zur Zeit, ob die PLZ der langen Liste >= der ersten VON-PLZ ist UND <= der ersten BIS-PLZ. Wenn ja, dann Wert aus der Spalte D ansonsten Prüfung der nächsten Zeile usw.

    Ich hoffe es gibt eine elegantere Methode, da meine Tabelle vermutlich auch mal wer anders bei uns verwalten muss 🙂

    Danke+Gruß
    Sebastian

    • Avatar-Foto
      Sebastian

      achja… Das Umständliche daran habe ich vergessen zu erwähnen…
      Mit den Postleitzahlbereichen aus der ersten Tabelle werden nicht sämtliche Postleitzahlen abgedeckt.
      Alle Postleitzahlen, die keinem Bereich der ersten Tabelle zugeordnet werden können sollen einen Standartwert erhalten.

      • Avatar-Foto
        Martin Weiß

        Hallo Sebastian,

        mein erster Gedanke war, einfach -1 für den Vergleichsmodus zu verwenden. Aber das würde nur funktionieren, wenn die erste Tabelle auch alle Bereiche enthalten würde. Was bei dir ja leider nicht der Fall ist. Daher habe ich jetzt leider auch keine Lösung parat, mit XVERWEIS wird das nicht machbar sein.
        Ich würde es vielleicht mit VERGLEICH und einer mehrstufigen Herangehensweise probieren:
        – erster Vergleich gegen die „Von“-Spalte (mit Vergleichsmodus -1)
        – zweiter Vergleich gegen die „Bis“-Spalte (mit Vergleichsmodus -1)
        – dritter Vergleich gegen die „Bis“-Spalte (mit Vergleichsmodus 0, um den Randwert zu erwischen)

        Und dann je nach Ergebnis aus den drei Vergleichen einen Wert zurückliefern oder auch nicht. Aber wie gesagt, das ist jetzt nur eine grobe Idee…

        Schöne Grüße,
        Martin

  • Avatar-Foto
    Albert

    Hallo,

    wie könnte ich hier jetzt noch die Bedingung Suchen nach Teilbegriffen hinzufügen?
    Ich habe die Funktion wie folgt modifiziert, erhalte jedoch stets #NV- Fehler:
    =SUMME(XVERWEIS(ISTZAHL(SUCHEN(„Suchtext“;B3:G3));B3:G3;XVERWEIS(ISTZAHL(SUCHEN(„Suchtext“;A4:A9));A4:A9;B4:G9))

    Danke für die Info
    Gruß Albert

    • Avatar-Foto
      Martin Weiß

      Hallo Albert,

      die Funktion ISTZAHL liefert ja die Wahrheitswerte WAHR oder FALSCH zurück, die so aber nicht in der Tabelle vorkommen. Daher muss das Suchkritierium WAHR sein und die ISTZAHL-Funktion stellt die Suchmatrix dar.
      So müsste es eigentlich klappen:
      =SUMME(XVERWEIS(WAHR;ISTZAHL(SUCHEN(„Suchbegriff“;B3:G3));XVERWEIS(WAHR;ISTZAHL(SUCHEN(„Suchbegriff“;A4:A9));B4:G9)))

      Vorausgesetzt natürlich, die beiden Suchbegriffe werden auch gefunden.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Darko

    Hallo Martin!

    Kann man den Xverweis auch rückwärts tätigen? In ihrem Beispiel würde ich den Verkäufer herauszufinden wollen, wenn man in einem bestimmten Monat nach einem Bestimmten Betrag sucht. Ist das möglich? Sind auch mehrere Ergebnisse möglich wenn der gleiche Betrag von mehreren Verkäufern erzielt wurde?

    LG
    Darko

    • Avatar-Foto
      Martin Weiß

      Hallo Darko,

      mit dem XVERWEIS allein bekommt man das nicht hin. Aber in Kombination mit INDEX und VERGLEICH geht es. Bezogen auf meine Beispieldatei würde die Formel so aussehen:
      =INDEX(A4:A9;VERGLEICH(B12;XVERWEIS(B11;A3:G3;A4:G9);0))

      Dabei wird in Zelle B11 der Monat und in Zelle B12 der gesuchte Betrag erwartet. Eine Ausgabe von mehr als einem Verkäufer ist aber nicht möglich.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Britta

    Hallo Martin,

    ich sitze nun schon ne lange Zeit an einem Problem und finde keine Lösung.

    Ich habe eine Tabelle mit vielen Rezepturen. Mittels XVerweis soll mir die Rezeptur in einem separaten Tabellenblatt ausgegeben werden.
    Ich habe es nun geschafft, den XVerweis mit der WENN Funktion zu koppeln. Es funktioniert für die erste Rezeptur in der WENN Funktion.
    Wenn ich nun aber den Namen der Rezeptur ändere funktioniert die WENN Funktion mit dem XVerweis nicht mehr.

    B12 = Rezepturname

    Das ist die Funktion die funktioniert wenn B12 sich auf die angegeben Such-&Rückgabe Bereiche bezieht.
    =WENN((XVERWEIS($B12;’Rezepturen‘!$D$2:$D$25;’Rezepturen‘!$E$2:$E$25))>0;(XVERWEIS($B12;’Rezepturen‘!$D$2:$D$25;’Rezepturen‘!$E$2:$E$25));“FEHLER“)

    Wenn ich nun B12 ändere und er keinen passenden Inhalt im Such-&Rückgabe Bereich findet dann bekomme ich einen #NV. Ich gehe davon aus, dass der Wahrheitstest WENN(XVERWEIS($B12;’Rezepturen‘!$D$2:$D$25;’Rezepturen‘!$E$2:$E$25))>0 ausgeführt wird und als WAHR empfunden wird, weil in den Zellen steht ja auch Text, aber halt nicht der den Excel suchen soll.

    Wenn ich nun den Suchbereich anpasse, dann funktioniert die Formel wieder.
    =WENNFEHLER(WENN((XVERWEIS($B12;’Rezepturen!$G$2:$G$25;’Rezepturen‘!$H$2:$H$25))>0;(XVERWEIS($B12;’Rezepturen‘!$G$2:$G$25;’Rezepturen‘!$H$2:$H$25));““);“FEHLER“).

    Mein Plan: Ich habe 35 Rezepturen, wenn er die gesuchte Zutat in der ersten Spalte nicht findet, soll er in die nächste Spalte gehen und dort suchen. Und immer so weiter bis er alle 35 Rezepturen durch hat und die Zutat gefunden hat. D.h. ich habe die Formal auch 35 mal hintereinander mit der WENN Funktion verschaltet. Jede einzelne Funktion für sich funktioniert, aber bei der Verschachtelung funktioniert nicht.

    Vielleicht hast du ja eine Idee wie ich mein Problem lösen kann.

    • Avatar-Foto
      Martin Weiß

      Hallo Britta,

      es ist etwas schwierig, aus der Entfernung eine geeignete Formel vorzuschlagen, wenn man die Daten nicht kennt oder sieht. Daher ist mir noch nicht klar, warum du überhaupt mit einer WENN-Funktion arbeitest. Zur Info: Im XVERWEIS kann man mit dem 4. Argument angeben, was passieren soll, wenn nichts gefunden wird. Und hier könnte man dann den nächsten XVERWEIS reinpacken. Also etwa in der Art:
      =XVERWEIS($B$12;$D$2:$D$25;$E$2:$E$25;XVERWEIS($B$12;$G$2:$G$25;H2:$H$25;XVERWEIS($B$12;$J$2:$J$25;$K$2:$K$25;“Fehler“)))

      Bei 35 Verschachtelungen wird das Ganze jedoch extrem gruselig und ich würde auch wirklich von so etwas abraten und überlegen, wie man die Datentabelle vielleicht geschickter aufbauen kann. Gegebenenfalls wäre auch eine Lösung mit Power Query denkbar, in der alle 35 Rezepturen eingelesen und in eine Auswertungstabelle überführt werden.

      Aber wie gesagt, aus der Entfernung ist so etwas schwer zu beurteilen. Daher habe ich jetzt auch keinen konkreten Tipp für Dich.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Alexandra Becker

    Hallo Zusammen,
    ich versuche gerade mich in Excel zurecht zu finden, aber ich habe folgende Problematik:
    Ich möchte aus Tabelle A einige Daten in Tabelle B übernehmen, mit folgendem Hintergrund:
    In Tabelle A sind zu einer Bestellnummer teilweise in mehreren Zeilen unterschiedliche Daten hinterlegt.
    Ich möchte aber in Tabelle B (in diesem Fall eine Rechnung), alle Zeilen UND Spalten übernehmen, die mit der Bestellnummer versehen sind.
    Mit mehreren Spalten funktioniert das super mit dieser Formel:
    =XVERWEIS($C$18;’Abgeschl. bis Einsatzauftrag‘!$E:$E;’Abgeschl. bis Einsatzauftrag‘!$G:$K)

    Aber da teilweise unter einer Bestellnummer mehrere Zeilen (in diesem Fall Einsatztage) laufen, möchte ich eben alle Zeilen übernehmen…

    • Avatar-Foto
      Martin Weiß

      Hallo Alexandra,

      da sieht es beim XVERWEIS schlecht aus, denn er kann immer nur den jeweils ersten (oder letzten) gefundenen Eintrag liefern. Ein Lösung würde die FILTER-Funktion bieten, die aber nur in Microsoft 365 oder Excel 2021 enthalten ist.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Alexandra Becker

        Vielen Dank für deine Antwort.
        Auf die Filter-Funktion bin ich auch schon gekommen, hier bekomme ich aber den Fehler #Wert.

        • Avatar-Foto
          Alexandra Becker

          Wenn man die Formel denn richtig eingibt, funktioniert es mit der Filter-Funktion. Vielen Dank für den Hinweis, so habe ich hier nochmal intensiver reingeschaut. 😉

  • Avatar-Foto
    ABG

    Mit der neueren Let Funktion kann man diese Formel eigentlich recht kompakt einstampfen, macht sie zwar nicht zwingend viel kürzer aber deutlich besser lesbar und wenn man sie mal anpassen muss, muss man nur an einer Stelle anpacken und nicht alle Bereiche n allen Stellen ersetzen aber so auf den ersten Blick, bietet sie für größere Bereiche sogar „mehr“ als das was der moderne Filter einem bietet, denn den müsste auf jeden Spalten/Zeilenbereich einzeln anpassen, außer mit Mtrans(von:bis) könnte man auch Bereiche übergeben, aber auch hier kann man nicht einfach Start und Ende angeben, sondern mehrere Worte/Zeichenketten definieren)

    =Let(matrixv;A4:A9;B4:G9;matrixh;B3:G3;
    SUMME((XVERWEIS(B27;matrixh;XVERWEIS(B29;matrixv)):XVERWEIS(B28;matrixh;XVERWEIS(B29;matrixv))):(XVERWEIS(B27;matrixh;XVERWEIS(B30;matrixv)):XVERWEIS(B28;matrixh;XVERWEIS(B30;matrixv))))
    )

    • Avatar-Foto
      Martin Weiß

      Hallo ABG,

      vielen Dank für deine Lösung. Ich muss gestehen, dass ich bisher noch nicht so richtig mit der LET-Funktion warm geworden bin. Aber das liegt mehr an mir als an der Funktion. Und es ist ja wirklich so, je mehr Verschachtelungen man in seinen Formeln hat, desto mehr kann LET seine Vorzüge ausspielen. Vielleicht sollte ich mich doch etwas mehr damit anfreunden… 😉

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Iseta Isaria

    Hallo Martin,

    ich habe eine Tabelle, die ich über XVerweis befülle. Die Tabelle ist jeden Monat die gleiche, aber die Basistabelle wechselt jeden Monat. Im Moment mache daher jeden Monat die Formel neu. Hast Du eine Idee, wie ich den Basistabellenwechsel (mit jeweils neuem Pfad) automatisiert einbauen kann?
    Danke Iseta

    • Avatar-Foto
      Martin Weiß

      Hallo Iseta,

      wenn ich dich richtig verstehe, dann liegt die Basistabelle also in einer anderen Datei. In diesem Fall würde ich auf jeden Fall dazu raten, die Basistabelle per Power Query in deine Auswertungsdatei einzulesen. Damit vermeidest du externe Bezüge und kannst die XVERWEIS-Formeln immer unverändert lassen.
      Und damit du auch immer andere Dateien einlesen kannst, könntest du den Pfad in einer eigenen Zelle hinterlegen und dort anpassen. Diese Zelle liest du ebenfalls in Power Query ein und verwendest sie als Parameter. Immer vorausgesetzt natürlich, dass die Basistabelle immer den gleichen Aufbau hat und sich praktisch nur der Dateiname und Pfad ändert.

      Wie das geht, habe ich in diesem Artikel beschrieben:
      Eine einzelne Zelle an Power Query übergeben

      Schöne Grüße,
      Martin