Tutorials

Tutorial: XVERWEIS – Mehrere nicht zusammenhängende Spalten zurückgeben

Melden

Hier ist ein ausführliches Tutorial, wie du mit der XVERWEIS-Funktion (XLOOKUP) in Excel mehrere Spalten zurückgibst, die nicht direkt nebeneinander liegen.

Standardmäßig gibt der XVERWEIS entweder einen einzelnen Wert oder einen zusammenhängenden Zellbereich zurück. Oft benötigt man jedoch Daten aus Spalte A und Spalte D, möchte aber die Spalten B und C überspringen.

In diesem Tutorial lernst du die zwei effizientesten Methoden für dieses Problem kennen (optimiert für Microsoft 365 und Excel 2021).

Das Szenario

Stell dir vor, du hast eine Mitarbeitertabelle:

  • Spalte A: Mitarbeiter-ID
  • Spalte B: Vorname
  • Spalte C: Nachname
  • Spalte D: Abteilung
  • Spalte E: Gehalt

Ziel: Du gibst die ID ein und möchtest automatisch den Nachnamen (Spalte C) und das Gehalt (Spalte E) erhalten. Spalte D soll ignoriert werden.

Methode 1: Kombination mit SPALTENWÄHLEN (Der modernste Weg)

Die Funktion SPALTENWÄHLEN (engl. CHOOSECOLS) ist der eleganteste Weg, um die Rückgabematrix des XVERWEIS zu filtern.

Die Formel:

=SPALTENWÄHLEN(XVERWEIS(H2; A2:A10; B2:E10); 2; 4)

So funktioniert es:

  1. XVERWEIS(H2; A2:A10; B2:E10): Der XVERWEIS sucht nach der ID in H2 und gibt zunächst die gesamte Zeile (Spalte B bis E) zurück.
  2. SPALTENWÄHLEN(…; 2; 4): Diese Funktion umschließt den XVERWEIS. Die Zahlen am Ende geben an, welche Spalten aus dem gefundenen Bereich ausgegeben werden sollen.
    • Die 2 steht für die zweite Spalte des gewählten Bereichs (Nachname).
    • Die 4 steht für die vierte Spalte des gewählten Bereichs (Gehalt).

Methode 2: Kombination mit WAAGRECHT.STAPELN (Maximale Flexibilität)

Wenn die Spalten, die du zurückgeben möchtest, in deiner Quelldatei wild verstreut sind (vielleicht sogar links vom Suchkriterium), ist WAAGRECHT.STAPELN (engl. HSTACK) die beste Wahl.

Die Formel:

=XVERWEIS(H2; A2:A10; WAAGRECHT.STAPELN(C2:C10; E2:E10))

So funktioniert es:

  1. WAAGRECHT.STAPELN(C2:C10; E2:E10): Diese Funktion erstellt "virtuell" eine neue Tabelle, die nur aus den Spalten C und E besteht.
  2. XVERWEIS: Der XVERWEIS nutzt diese neu gestapelte Matrix als Rückgabebereich. Excel gibt automatisch beide Werte in nebeneinanderliegende Zellen aus (Spill-Effekt).

Methode 3: Die klassische "Konstanten-Array"-Methode (Ohne Zusatzfunktionen)

Falls du eine ältere Excel-Version nutzt, die SPALTENWÄHLEN noch nicht beherrscht, kannst du die Rückgabe über eine Array-Konstante steuern.

Die Formel:

=XVERWEIS(H2; A2:A10; WENN({1.0.1}; C2:C10; E2:E10))

Hinweis: Diese Methode ist etwas komplexer in der Logik und wird heute meist durch Methode 1 oder 2 ersetzt.

Zusammenfassung & Profi-Tipps

Methode Vorteil Empfehlung
SPALTENWÄHLEN Sehr übersichtlich bei großen Tabellen. Beste Wahl für Standard-Layouts.
WAAGRECHT.STAPELN Spalten können in beliebiger Reihenfolge kombiniert werden. Wenn die Reihenfolge der Ausgabe anders als in der Quelle sein soll.

Wichtige Hinweise:

  • Überlauf-Fehler (#ÜBERLAUF!): Achte darauf, dass die Zellen rechts neben deiner Formel leer sind, da der XVERWEIS die Ergebnisse dort "ausspuckt".
  • Formatierung: Wenn du ein Gehalt zurückgibst, musst du die Zielzelle eventuell noch als "Währung" formatieren, da Excel das Format nicht immer automatisch übernimmt.

Glückwunsch! Du kannst nun XVERWEIS nutzen, um genau die Daten zu extrahieren, die du wirklich brauchst.

0