SVERWEIS & INDIREKT – Dynamischer Bereich – Excel & Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie die Funktion INDIREKT verwenden können, um den Suchbereich der SVERWEIS-Funktion in Excel und Google Sheets zu definieren.

INDIREKT & SVERWEIS

Es könnte vorkommen, dass Sie mal einen SVERWEIS abhängig von bestimmten Zellenwerten für mehrere Bereiche gleichzeitig durchführen müssen. In diesem Fall ist die INDIREKT-Funktion eine Option , mit der Sie einen Suchbereich definieren oder sogar einen dynamischen Verweis auf mehrere Blätter erstellen können.

=SVERWEIS($B3;INDIREKT("'"&C$2&"'!"&"B3:C5");2;FALSCH)

Hier haben wir Daten im Bereich B3:C5 auf jedem Blatt. Anstatt eine SVERWEIS-Formel für jedes Blatt zu schreiben, können wir mit der INDIREKT-Funktion auf die Blätter dynamisch verweisen.

INDIREKT – Dynamischer Suchbereich

Der Suchbereich für C3 soll folgendermaßen aussehen:

'2018!'B3:C5

Wir brauchen also nur eine Formel zu schreiben, die diesen Bereich innerhalb der INDIREKT-Funktion erzeugt:

"'"&C$2&"'!"&"B3:C5"

Wir verwenden (&), um den Text zusammen mit der teilweise gesperrten Zellenreferenz, die das Jahr (C$2) enthält, zusammenzuführen. Wenn die Formel dann in den Bereich kopiert wird, gibt die Funktion INDIREKT das erforderliche Blatt für jede Spalte aus.

WAHL & SVERWEIS

Die Funktion INDIREKT ist „volatil“. Sie wird jedes Mal neu berechnet, wenn Excel dies tut und das kann dazu führen, dass Ihre Arbeitsmappe langsam berechnet.

Stattdessen könnte in diesem Fall die WAHL-Funktion eine bessere Alternative sein. Mit der WAHL-Funktion können Sie aus einer Liste „auswählen“, was ausgegeben werden soll.

=WAHL(C2;SVERWEIS(B3;'2018'!B3:C5;2;FALSCH);SVERWEIS(B3;'2019'!B3:C5;2;FALSCH);SVERWEIS(B3;'2020'!B3:C5;2;FALSCH))

In diesem Beispiel ist die Liste in der WAHL-Funktion jede mögliche SVERWEIS-Formel. Jeder Bereich ist fest programmiert und jede Zelle verweist auf alle drei Blätter. Der Indexwert in Zeile 2 teilt der Funktion mit, welches Element der Liste verwendet werden soll, d.h. auf welchem Blatt die Suche durchgeführt werden soll.

SVERWEIS & INDIREKT in Google Sheets

Diese Formeln funktionieren in Google Sheets auf die gleiche Weise wie in Excel.