Nutzen Sie unser 
In Excel ist die Funktion SVERWEIS() oft eine der ersten Funktionen, die Anwender zum Durchsuchen von Daten verwenden. Allerdings hat SVERWEIS() eine Einschränkung: Es kann nur in einer Spalte nach einem Suchbegriff gesucht werden. Doch was, wenn mehrere Kriterien gleichzeitig erfüllt werden müssen, wie in deinem Beispiel mit Artikelnummer, Lagerort und Lagerart? In solchen Fällen können andere Formeln, wie INDEX() in Verbindung mit SUMMENPRODUKT() oder eine Array-Formel, genutzt werden.
Ausgangslage
Die zu analysierenden Daten befinden sich in den Spalten A, B, C und D:
So soll beispielsweise für die Suchbegrifft
Art.-Nr.: = 4712
Lagerort: = Lager B
Lagerart: = Aussen
der Wert 500 aus Zeile 10 zurückgeliefert werden, da in dieser Zeile alle drei Suchbegriffe identisch sind.
Das Ergebnis kann mit Hilfe der Funktino INDEX() in Verbindung mit der Funktion SUMMENPRODUKT() erreicht werden. Dabei handelt es sich um keine Matrixfunktion. Die Formel dazu lautet wie folgt:
=INDEX(D:D;SUMMENPRODUKT((A1:A20=H5)*(B1:B20=H6)*(C1:C20=H7)*ZEILE(1:20)))
Alternativ kann auch eine Matrix-Funktion verwendet werden, die etwas kürzer ist:
{=INDEX(D:D;VERGLEICH(H5&H6&H7;A1:A20&B1:B20&C1:C20;0))}
Hinweis:
Die geschweiften Klammern dürfen nicht eingegeben werden, sondern müssen über die Tastenkombination [Strg] + [Umschalt] + [Enter] erzeugt werden.
Das Ergebnis sieht wie folgt aus.
Nachfolgend können Sie die Beispieldatei mit den entsprechenden Formeln herunterladen.
Lösung 2: Array-Formel
Eine Alternative, die eine kürzere Schreibweise ermöglicht, ist die Verwendung einer Array-Formel. Diese Formel kombiniert die drei Kriterien in einer einzigen Suchoperation:
excel
Code kopieren
{=INDEX(D:D;VERGLEICH(H5&H6&H7;A1:A20&B1:B20&C1:C20;0))}
Erläuterung der Array-Formel:
- H5&H6&H7: Verkettet die Suchkriterien (Artikelnummer, Lagerort, Lagerart) zu einem einzigen String.
- A1:A20&B1:B20&C1:C20: Verkettet ebenfalls die Werte aus den drei Spalten, um nach dem kombinierten Wert zu suchen.
- VERGLEICH(...): Findet die Position, an der der kombinierte Suchwert in den verketteten Spaltenwerten vorkommt.
- INDEX(D:D; ...): Gibt den Wert in Spalte D zurück, der in der gefundenen Zeile steht.
Hinweis:
Um die Array-Formel korrekt auszuführen, muss sie mit [Strg] + [Umschalt] + [Enter] bestätigt werden. Excel fügt automatisch die geschweiften Klammern {} hinzu.
Erweiterung: Verwendung von FILTER in Excel 365
Eine weitere moderne Methode, insbesondere für Nutzer von Excel 365, ist die Verwendung der Funktion FILTER(). Diese Funktion ermöglicht es, Suchergebnisse direkt zu filtern und auszugeben. Ein großer Vorteil ist, dass keine Matrixformel oder komplexe Kombinationen erforderlich sind.
Die Formel für dein Beispiel könnte wie folgt lauten:
excel
Code kopieren
=FILTER(D1:D20; (A1:A20=H5)*(B1:B20=H6)*(C1:C20=H7))
Erläuterung der FILTER-Formel:
- FILTER(D1:D20; ...): Gibt den Wert aus Spalte D zurück, der die Bedingungen erfüllt.
- (A1=H5)(B1=H6)(C1=H7): Diese logischen Vergleiche filtern die Zeilen, die alle Kriterien erfüllen.
Diese Methode ist besonders effizient und leicht verständlich.
Fazit
Für die Suche nach Daten in mehreren Spalten bietet Excel flexible Lösungsansätze, abhängig von der Excel-Version und dem Anwendungsfall. Die Kombination aus INDEX() und SUMMENPRODUKT() ist eine kraftvolle Lösung, insbesondere für komplexere Suchvorgänge. Alternativ können moderne Funktionen wie FILTER() in Excel 365 eine elegantere und benutzerfreundlichere Alternative darstellen.
