XVERWEIS – Mehrere Spalten zurückgeben
Die Beispielarbeitsmappe herunterladen
Dieses Tutorial zeigt Ihnen, wie Sie mehrere Spalten in Excel mit XVERWEIS zurückgeben können. Wenn Ihre Excel-Version XVERWEIS nicht unterstützt, lesen Sie, wie Sie stattdessen mehrere Spalten mit SVERWEIS zurückgeben können.
XVERWEIS – Aufeinanderfolgende Spalten zurückgeben
Ein Vorteil der XVERWEIS-Funktion besteht darin, dass sie mehrere aufeinanderfolgende Spalten auf einmal zurückgeben kann. Geben Sie dazu den Bereich der aufeinanderfolgenden Spalten in das Rückgabe-Array ein, und die Funktion gibt alle Spalten zurück.
=XVERWEIS(F3;B3:B7;C3:D7)
Beachten Sie, dass unser Rückgabe-Array aus zwei Spalten (C und D) besteht und somit 2 Spalten zurückgegeben werden.
XVERWEIS-FILTER – Nicht-benachbarte Spalten zurückgeben
Obwohl die XVERWEIS-Funktion mehrere aufeinanderfolgende Spalten zurückgeben kann, kann sie keine nicht benachbarten Spalten zurückgeben. Um nicht-benachbarte Spalten zurückzugeben, können wir die FILTER-Funktion verwenden.
=XVERWEIS(F3;C3:C7;FILTER(B3:D7;{1.0.1}))
Gehen wir die Formel durch:
Die FILTER-Funktion
Die FILTER-Funktion wird üblicherweise zum Filtern von Zeilen verwendet, aber sie kann auch zum Filtern von Spalten eingesetzt werden. Dazu geben wir ein horizontales Array aus Einsen (WAHR) und Nullen (FALSCH) mit der gleichen Anzahl von Spalten aus der Array-Eingabe (1. Argument) ein. Die Spalten, die mit Nullen übereinstimmen, werden herausgefiltert, während Spalten, die mit Einsen übereinstimmen, zurückgegeben werden.
=FILTER(B3:D7;{1.0.1})
Die XVERWEIS-Funktion
Die Ausgabe der FILTER-Funktion wird dann als Rückgabe-Array für unseren XVERWEIS verwendet.
=XVERWEIS(F3;C3:C7;H3:I7)
Kombinieren wir alle Funktionen miteinander, erhalten wir unsere ursprüngliche Formel:
=XVERWEIS(F3;C3:C7;FILTER(B3:D7;{1.0.1}))XVERWEIS-FILTER-ZÄHLENWENN – Rückgabe nicht-benachbarter Spalten
Anstatt die herauszufilternden Spalten manuell auszuwählen, können wir die ZÄHLENWENN-Funktion verwenden, um die Spalten, die zurückgegeben und herausgefiltert werden sollen, automatisch zu bestimmen.
=XVERWEIS(F3;C3:C7;FILTER(B3:D7;ZÄHLENWENN(G2:H2;B2:D2))
Gehen wir nun die Formel durch:
Die ZÄHLENWEN-Funktion
Zunächst verwenden wir die ZÄHLENWENN-Funktion, um die Instanzen der einzelnen Quellkopfzeilen (z. B. B2:D2) aus den Ausgabekopfzeilen (z. B. M2:N2) zu zählen.
=ZÄHLENWENN(M2:N2;B2:D2)
Die FILTER-Funktion
Das Array-Ergebnis der ZÄHLENWENN-Funktion wird dann als Filterbedingung verwendet, um die Spalten zu filtern.
=FILTER(B3:D7;H3:J3)
Die XVERWEIS-Funktionen
Schließlich können wir das Ergebnis der FILTER-Funktion in der XVERWEIS-Funktion verwenden, um die gewünschten Spalten zurückzugeben.
=XVERWEIS(F3;C3:C7;K3:L7)
Kombinieren wir alle Funktionen miteinander, erhalten wir unsere ursprüngliche Formel:
=XVERWEIS(F3;C3:C7;FILTER(B3:D7;ZÄHLENWENN(G2:H2;B2:D2))
XVERWEIS-WAHL – Rückgabe von Spalten in unterschiedlicher Reihenfolge
Die XVERWEIS-FILTER-ZÄHLENWENN-Formel kann nicht benachbarte Spalten zurückgeben, aber was ist, wenn wir eine Spalte in einer anderen Reihenfolge zurückgeben wollen? Um dies zu lösen, verwenden wir die WAHL-Funktion.
=XVERWEIS(F3;B3:B7;WAHL({2.1};C3:C7;D3:D7))
Gehen wir die Formel durch:
Die WAHL-Funktion
Zunächst verwenden wir die WAHL-Funktion, um Spalten in einer bestimmten Reihenfolge in einem Array zusammenzufassen.
=WAHL({2.1};C3:C7;D3:D7)
Hinweis: Wir geben die gewünschten Spalten separat in die WAHL-Funktion ein (z. B. C3:C7, D3:D7) und ordnen sie anhand vom Index (1. Argument).
Die XVERWEIS-Funktion
Das resultierende Array der WAHL-Funktion wird dann als Rückgabe-Array verwendet.
=XVERWEIS(F3;B3:B7;H3:I7)
Die Kombination aller Funktionen zusammen ergibt unsere ursprüngliche Formel:
=XVERWEIS(F3;B3:B7;WAHL({2.1};C3:C7;D3:D7))XVERWEIS-CHOOSECOLS – Rückgabe von Spalten in unterschiedlicher Reihenfolge
Die XVERWEIS-WAHL-Formel wird lang und mühsam, wenn wir viele Spalten zurückgeben müssen. Glücklicherweise gibt es eine neue Array-Funktion (derzeit in der BETA-Version von Excel 365), die bequemer als die CHOOSE-Funktion ist. Diese Funktion wird CHOOSECOLS genannt.
=XVERWEIS(F3;B3:B7;CHOOSECOLS(B3:D7;3;2))
Lassen Sie uns die Formel durchgehen:
Die CHOOSECOLS-Funktion
Im Gegensatz zur WAHL-Funktion, bei der wir die Spalten in einer bestimmten Reihenfolge verbinden, gibt die CHOOSECOLS-Funktion die ausgewählten Spalten in der angegebenen Reihenfolge zurück.
=CHOOSECOLS(B3:D7;3;2)
Hinweis: Die Zahlen, die wir in den Spaltennummernargumenten angeben (beginnend mit dem 2. Argument), sind die relativen Spaltennummern der Spalten aus dem angegebenen Array (z. B. B3:D7).
Die XVERWEIS-Funktion
Schließlich können wir die XVERWEIS-Funktion mit dem Ergebnis der CHOOSECOLS-Funktion verwenden.
=XVERWEIS(F3;B3:B7;H3:I7)
Wenn wir alle Funktionen miteinander kombinieren, erhalten wir unsere ursprüngliche Formel:
=XVERWEIS(F3;B3:B7;CHOOSECOLS(B3:D7;3;2))XVERWEIS-CHOOSECOLS-VERGLEICH – Rückgabe von Spalten in unterschiedlicher Reihenfolge
Anstatt die Spalten und ihre Reihenfolge manuell auszuwählen, können wir die VERGLEICH-Funktion verwenden, um die Spalten, die für die Aufgabe erforderlich sind, und ihre Reihenfolge automatisch zu bestimmen.
=XVERWEIS(F3;B3:B7;CHOOSECOLS(B3:D7;VERGLEICH(G2:H2;B2:D2;0))
Gehen wir nun die Formel durch:
Die VERGLEICH-Funktion
Zunächst verwenden wir die VERGLEICH-Funktion, um die relativen Spaltenpositionen jeder Ausgabeüberschrift anhand der Quellüberschriften zu bestimmen.
=VERGLEICH(L2:M2;B2:D2;0)
Die CHOOSECOLS-Funktion
Da wir nun die Positionen der ausgewählten Spalten haben, können wir die CHOOSECOLS-Funktion verwenden, um diese in einem Array zurückzugeben.
=CHOOSECOLS(B3:D7;H3:I3)
Die XVERWEIS-Funktion
Der letzte Schritt besteht darin, das Ergebnis der CHOOSECOLS-Funktion in das Rückgabe-Array der XVERWEIS-Funktion einzugeben.
=XVERWEIS(F3;B3:B7;J3:K7)
Die Kombination aller Funktionen zusammen ergibt unsere ursprüngliche Formel:
=XVERWEIS(F3;B3:B7;CHOOSECOLS(B3:D7;VERGLEICH(G2:H2;B2:D2;0))
