Zurück zu Excel-Formeln

XVERWEIS – Mehrere Spalten zurückgeben

Beispielarbeitsmappe herunterladen

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 mehrere spalten zurueckgeben hauptfunktion

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)

xverweis mehrere spalten zurueckgeben

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}))

xverweis nicht benachbarte spalten zurueckgeben

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})

xverweis mehrere spalten zurueckgeben filter funktion

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)

xverweis nicht benachbarte spalten zurueckgeben detailliert

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))

xverweis-mehrere spalten zurueckgeben filter zaehlenwenn funktion

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)

xverweis mehrere spalten zurueckgeben zaehlenwenn funktion

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)

xverweis mehrere spalten zurueckgeben filter

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)

xverweis mehrere spalten zurueckgeben filter zaehlenwenn detailliert

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))

xverweis mehrere spalten zurueckgeben wahl funktion

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)

xverweis mehrere spalten zurueckgeben wahl zwischenergebnis

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)

xverweis mehrere spalten zurueckgeben wahl detailliert

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))

xverweis mehrere spalten zurueckgeben choosecols formel

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)

xverweis mehrere spalten zurueckgeben choosecols

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)

xverweis mehrere spalten zurueckgeben zwischenergebnis

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))

xverweis mehrere spalten zurueckgeben choosecols vergleich formel

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)

xverweis mehrere spalten zurueckgeben vergleich funktion

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)

xverweis mehrere spalten zurueckgeben choosecols vergleich zwischenergebnis

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)

xverweis mehrere spalten zurueckgeben choosecols vergleich detailliert

Die Kombination aller Funktionen zusammen ergibt unsere ursprüngliche Formel:

=XVERWEIS(F3;B3:B7;CHOOSECOLS(B3:D7;VERGLEICH(G2:H2;B2:D2;0))

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Zurück zu Excel-Formeln