Neben SVERWEIS und INDEX.MATCH verfügt Excel über drei weitere Suchfunktionen, die spezifische Probleme lösen: LOOKUP für einfache Suchvorgänge in sortierten Daten, INDIREKT zum Erstellen von Zellbezügen aus Textzeichenfolgen und OFFSET zum Erstellen dynamischer Bereiche. Hier erfährst du, wie die einzelnen Funktionen funktionieren und wann du sie einsetzen solltest.
LOOKUP-Funktion
LOOKUP durchsucht einen Bereich nach einem Wert und gibt einen entsprechenden Wert aus einem anderen Bereich zurück. Es wird immer eine ungefähre Übereinstimmung gesucht, und die Suchdaten müssen in aufsteigender Reihenfolge sortiert sein.Vektorform
Die Vektorform durchsucht eine einzelne Zeile oder Spalte und gibt einen Wert aus der entsprechenden Zeile oder Spalte zurück.
Syntax: =LOOKUP(lookup_value; lookup_vector; result_vector) Beispiel:| A (Bewertung) | B (Note) |
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
=LOOKUP(85, A1:A5, B1:B5)
LOOKUP ermittelt den größten Wert in A1:A5, der kleiner oder gleich 85 ist. Das ist 80. Die Funktion gibt den entsprechenden Wert aus B1:B5 zurück: „B“.
Wichtig: Der Suchvektor muss in aufsteigender Reihenfolge sortiert sein. Die Funktion „LOOKUP“ verfügt nicht über einen Modus für exakte Übereinstimmungen - sie führt immer eine ungefähre Übereinstimmung durch (kleiner oder gleich).Array-Form
Die Array-Formel nimmt eine einzelne Tabelle als Argument. Ist die Tabelle höher als breit, durchsucht LOOKUP die erste Spalte. Ist sie breiter als hoch, durchsucht sie die erste Zeile. Das Ergebnis wird immer aus der letzten Spalte oder Zeile zurückgegeben.
Syntax: =LOOKUP(lookup_value; array)Die Array-Form wird im modernen Excel kaum noch verwendet. Die Vektorform ist übersichtlicher und berechenbarer.
Wann sollte man LOOKUP und wann SVERWEIS verwenden?
Verwende die Funktion „LOOKUP“, wenn:
- Deine Daten sind sortiert und du möchtest eine ungefähre Übereinstimmung (Notenskalen, Steuerklassen)
- Du möchtest die einfachstmögliche Syntax für eine schwellenwertbasierte Suche
Verwende SVERWEIS, wenn:
- Du benötigst Exakt-Suchen (FALSE oder 0 als viertes Argument)
- Deine Daten sind möglicherweise nicht sortiert
Verwende INDEX.MATCH für:
- Linksverweise, große Datensätze oder wenn Spalten eingefügt werden könnten
INDIREKT-Funktion
INDIREKT wandelt eine Textzeichenfolge in eine Zellreferenz um, die Excel auswertet. Anstatt direkt auf eine Zelle zu verweisen, baust du die Referenzadresse als Text auf, und INDIREKT sorgt dafür, dass Excel sie als echte Referenz behandelt. Syntax: =INDIREKT(ref_text; [a1])- ref_text - eine Textzeichenfolge, die eine Zellen- oder Bereichsadresse darstellt
- a1 - TRUE (Standard) für Referenzen im A1-Stil, FALSE für Referenzen im R1C1-Stil
Einfaches Beispiel
=INDIRECT("A1")
Das entspricht einfach nur =A1 - für sich genommen nicht besonders nützlich, aber die wahre Stärke kommt erst zum Tragen, wenn die Textzeichenfolge dynamisch ist.
Dynamische Arbeitsblattverweise
INDIREKT ist besonders nützlich, wenn du auf eine Zelle in einem anderen Blatt verweisen möchtest, wobei der Name des Blatts aus einer anderen Zelle stammt.
Angenommen, in Zelle B1 steht der Text „Januar“ und du hast ein Arbeitsblatt namens „Januar“. So rufst du Zelle A1 aus diesem Arbeitsblatt ab:
=INDIRECT(B1 & "!A1")
Ändere B1 in „Februar“ und die Formel bezieht die Daten automatisch aus dem Februar-Blatt. Das ist mit einem direkten Verweis wie =January!A1 nicht möglich - der ist fest codiert.
Dynamische Referenz auf benannte Bereiche
Wenn du benannte Bereiche mit den Namen „Nord“, „Süd“ und „Ost“ hast und Zelle A1 einen dieser Namen enthält:
=SUM(INDIRECT(A1))
Diese SUMME-Funktion summiert den benannten Bereich, der mit dem Text in A1 übereinstimmt. Ändere A1 von „Nord“ in „Süd“ und die SUMME-Funktion wird automatisch aktualisiert.
Wann man INDIREKT verwendet
Verwende INDIREKT, wenn:
- Du musst eine Referenz aus Text erstellen (dynamische Blattnamen, variable Spaltenbuchstaben)
- Du suchst eine Formel, die auf einen Bereich verweist, dessen Adresse sich je nach Benutzereingabe ändert
Achtung: INDIREKT ist flüchtig - die Funktion wird jedes Mal neu berechnet, wenn sich eine beliebige Zelle in der Arbeitsmappe ändert, auch wenn sich die Eingaben nicht geändert haben. In großen Arbeitsmappen kann die häufige Verwendung von INDIREKT die Neuberechnung merklich verlangsamen.
OFFSET-Funktion
OFFSET gibt eine Referenz auf einen Bereich zurück, der eine bestimmte Anzahl von Zeilen und Spalten von einer Startzelle entfernt ist. Die Funktion kann eine einzelne Zelle oder einen gesamten Bereich einer bestimmten Größe zurückgeben. Syntax: =OFFSET(reference; rows; cols; [height]; [width])- reference - Ausgangspunkt
- rows - um wie viele Zeilen verschoben werden soll (positiv = nach unten, negativ = nach oben)
- cols - um wie viele Spalten verschoben werden soll (positiv = nach rechts, negativ = nach links)
- height - optional, Anzahl der Zeilen im zurückgegebenen Bereich
- width - optional, Anzahl der Spalten im zurückgegebenen Bereich
Einfaches Beispiel
=OFFSET(A1, 2, 1)
Beginnt bei A1, geht 2 Zeilen nach unten und 1 Spalte nach rechts - gibt den Wert in B3 zurück.
Einen Bereich zurückgeben
=SUM(OFFSET(A1, 0, 0, 5, 1))
Gibt eine Referenz auf einen Bereich mit 5 Zeilen und 1 Spalte zurück, der bei A1 beginnt - entspricht =SUMME(A1:A5). Das ist nützlich, wenn die Größe dynamisch ist.
Dynamikbereich für ein Diagramm oder SUMME
Eine häufige Anwendung: Die letzten N Zeilen einer Spalte summieren, wobei N aus einer Zelle stammt. Wenn B1 die Anzahl der zu berücksichtigenden Monate enthält:
=SUM(OFFSET(A10, 0, 0, -B1, 1))
Beginnt bei A10, nutzt eine negative Höhe, um die Zeilen bis B1 nach oben zu verschieben. Ändere den Wert in B1 von 3 auf 6, und die SUMME passt sich automatisch an.
OFFSET für dynamische Dropdown-Listen
Die Kombination aus „OFFSET“ und „ANZAHL2“ erstellt einen benannten Bereich, der sich erweitert, sobald du Daten hinzufügst:
=OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1)
Als Formel für einen benannten Bereich deckt diese immer genau so viele Zeilen ab, wie Einträge in Spalte A vorhanden sind, was sie für dynamische Datenüberprüfungslisten nützlich macht.
Achtung: Genau wie INDIREKT ist OFFSET eine flüchtige Funktion und wird ständig neu berechnet. Für statische Nachschläge ist INDEX vorzuziehen, da diese Funktion nicht flüchtig ist.Häufig gestellte Fragen
Was ist der Hauptunterschied zwischen LOOKUP und SVERWEIS?SVERWEIS erfordert, dass die Suchspalte die Spalte ganz links im Tabellenbereich ist, unterstützt die exakte Übereinstimmung (viertes Argument = FALSE/0) und ermöglicht es dir, eine beliebige Rückgabespalte anhand ihrer Nummer auszuwählen. LOOKUP ist einfacher, verwendet jedoch immer eine ungefähre Übereinstimmung (keine Option für exakte Übereinstimmung) und erfordert sortierte Daten. Für die meisten geschäftlichen Anwendungen sind SVERWEIS oder INDEX VERGLEICH besser geeignet als LOOKUP.
Kann INDIREKT auf eine andere Arbeitsmappe verweisen?Ja, aber die andere Arbeitsmappe muss geöffnet sein. Die Referenzzeichenfolge muss den Namen der Arbeitsmappe enthalten: =INDIREKT("[WorkbookName.xlsx]SheetName!A1"). Wenn die referenzierte Arbeitsmappe geschlossen ist, gibt INDIREKT einen #REF!-Fehler zurück. Für arbeitsmappenübergreifende Verweise auf geschlossene Dateien solltest du stattdessen direkte Verweise oder Power Query verwenden.
Wann würde ich OFFSET statt INDEX verwenden?Verwende OFFSET, wenn du einen Bereich variabler Größe zurückgeben musst (für Diagramme, SUMMEWENN-Bereiche oder Datenüberprüfungslisten, die wachsen). INDEX kann eine einzelne Zelle anhand ihrer Position zurückgeben, aber keinen Bereich variabler Höhe auf dieselbe Weise als Referenz zurückgeben. Verwende INDEX für Suchvorgänge; verwende OFFSET für dynamische Bereiche.
Warum zeigt meine OFFSET-Formel den Fehler #REF! an?Ein #REF!-Fehler bei OFFSET bedeutet in der Regel, dass die resultierende Referenz außerhalb der Arbeitsblattgrenzen liegt. Beispielsweise versucht =OFFSET(A1; -1; 0), eine Zeile über A1 zu springen, die es nicht gibt. Überprüfe, ob deine Zeilen- und Spaltenargumente die Referenz nicht über die Ränder des Arbeitsblatts hinausschieben.