Der SVERWEIS (oder VLOOKUP auf Englisch) ist eine der mächtigsten Funktionen in Excel. Er ermöglicht es Ihnen, Daten aus einer Tabelle oder einem Bereich basierend auf einem Suchkriterium zu finden. Aber was, wenn Sie mehr als ein Suchkriterium haben? Keine Sorge, dieser Artikel führt Sie durch verschiedene Techniken, um den SVERWEIS mit mehreren Suchkriterien wie ein Profi zu meistern. Wir decken sowohl einfache als auch fortgeschrittene Methoden ab, damit Sie für jede Situation gerüstet sind.
Grundlagen des SVERWEIS
Bevor wir uns in die Welt der Multi-Kriterien-Suche stürzen, frischen wir die Grundlagen des SVERWEIS auf. Die Syntax des SVERWEIS sieht wie folgt aus:
=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])- Suchkriterium: Der Wert, nach dem Sie suchen.
- Matrix: Der Zellbereich, in dem Sie suchen und aus dem Sie den Wert zurückgeben möchten.
- Spaltenindex: Die Nummer der Spalte in der Matrix, die den Rückgabewert enthält.
- [Bereich_Verweis]: Optional; WAHR für eine ungefähre Übereinstimmung, FALSCH für eine genaue Übereinstimmung. Verwenden Sie fast immer FALSCH für genaue Ergebnisse.
Der SVERWEIS durchsucht die erste Spalte der Matrix nach dem Suchkriterium und gibt den Wert aus der angegebenen Spalte (Spaltenindex) in derselben Zeile zurück. Das ist einfach, aber was, wenn wir mehrere Suchkriterien haben?
Technik 1: Die Hilfsspalte – Die einfachste Lösung
Die einfachste und oft effektivste Methode zur Verwendung des SVERWEIS mit mehreren Suchkriterien ist die Verwendung einer Hilfsspalte. Dabei erstellen Sie eine neue Spalte in Ihrer Datentabelle, die die Werte Ihrer Suchkriterien kombiniert.
Schritt-für-Schritt-Anleitung:
- Kombinieren Sie die Suchkriterien in einer neuen Spalte: Angenommen, Sie haben zwei Suchkriterien: „Produkt” (Spalte A) und „Farbe” (Spalte B). Erstellen Sie eine neue Spalte (z. B. Spalte C) und verwenden Sie die Formel
=A2&"_"&B2(oder=A2&B2, wenn Sie keinen Trenner benötigen), um die Werte der Spalten A und B zu kombinieren. Achten Sie darauf, einen eindeutigen Trenner (hier „_”) zu verwenden, um Verwechslungen zu vermeiden. Ziehen Sie die Formel nach unten, um sie auf alle Zeilen anzuwenden. - Erstellen Sie eine entsprechende kombinierte Spalte in Ihrer Suchtabelle: Erstellen Sie eine ähnliche kombinierte Spalte in der Tabelle, in der Sie suchen, mit derselben Formel und demselben Trenner.
- Verwenden Sie den SVERWEIS mit der kombinierten Spalte als Suchkriterium: Verwenden Sie den SVERWEIS, um nach der kombinierten Spalte zu suchen. Beispiel:
=SVERWEIS(A1&"_"&B1;Datenbereich;3;FALSCH), wobei A1 und B1 die Zellen mit Ihren Suchkriterien sind, „Datenbereich” der Bereich Ihrer Tabelle mit der Hilfsspalte und 3 die Spalte, aus der Sie den Wert zurückgeben möchten.
Vorteile: Einfach zu verstehen und zu implementieren. Funktioniert gut für kleine bis mittelgroße Datensätze.
Nachteile: Benötigt eine zusätzliche Spalte in Ihrer Datentabelle. Nicht ideal für sehr große Datensätze, da das Erstellen der Hilfsspalte Zeit in Anspruch nehmen kann.
Technik 2: Der SVERWEIS in Kombination mit der INDEX– und VERGLEICH-Funktion
Eine flexiblere und leistungsfähigere Methode ist die Kombination des SVERWEIS mit der INDEX– und VERGLEICH-Funktion. Diese Kombination bietet mehr Kontrolle und kann komplexere Suchanforderungen bewältigen.
Wie es funktioniert:
- Die VERGLEICH-Funktion sucht nach einem bestimmten Wert in einem Bereich und gibt die relative Position dieses Werts innerhalb des Bereichs zurück.
- Die INDEX-Funktion gibt den Wert in einer Tabelle oder einem Bereich an einer bestimmten Zeilen- und Spaltennummer zurück.
Schritt-für-Schritt-Anleitung:
- Verwenden Sie die VERGLEICH-Funktion, um die Zeilennummer zu finden, die alle Suchkriterien erfüllt: Hier kommt die Array-Formel ins Spiel (muss mit STRG+UMSCHALT+ENTER bestätigt werden). Beispiel:
=VERGLEICH(1;(A2:A10="Suchkriterium1")*(B2:B10="Suchkriterium2");0)- A2:A10 ist der Bereich für das erste Suchkriterium (z.B. Produkt).
- „Suchkriterium1” ist der Wert des ersten Suchkriteriums.
- B2:B10 ist der Bereich für das zweite Suchkriterium (z.B. Farbe).
- „Suchkriterium2” ist der Wert des zweiten Suchkriteriums.
- Das
(A2:A10="Suchkriterium1")*(B2:B10="Suchkriterium2")erstellt ein Array von WAHR/FALSCH-Werten. WAHR wird als 1 und FALSCH als 0 behandelt. Wenn beide Bedingungen erfüllt sind, ergibt das Produkt 1 (WAHR*WAHR=1). - Die VERGLEICH-Funktion sucht nach dem ersten Vorkommen von 1 in diesem Array und gibt die Position zurück.
- Verwenden Sie die INDEX-Funktion, um den Wert aus der gewünschten Spalte an der gefundenen Zeile zurückzugeben: Kombinieren Sie die INDEX– und VERGLEICH-Funktion:
=INDEX(C2:C10;VERGLEICH(1;(A2:A10="Suchkriterium1")*(B2:B10="Suchkriterium2");0))- C2:C10 ist der Bereich, aus dem Sie den Wert zurückgeben möchten (z.B. Preis).
- Der Rest der Formel ist die gleiche VERGLEICH-Formel aus Schritt 1.
Wichtig: Denken Sie daran, diese Formel als Array-Formel mit STRG+UMSCHALT+ENTER zu bestätigen.
Vorteile: Keine Hilfsspalte erforderlich. Flexibler und leistungsfähiger als die Hilfsspaltenmethode. Funktioniert gut mit dynamischen Daten.
Nachteile: Komplexer zu verstehen und zu implementieren. Array-Formeln können die Leistung bei sehr großen Datensätzen beeinträchtigen.
Technik 3: Die SUMMENPRODUKT-Funktion (mit Einschränkungen)
Die SUMMENPRODUKT-Funktion kann auch verwendet werden, um nach mehreren Kriterien zu suchen, allerdings mit einigen Einschränkungen. Sie ist am besten geeignet, wenn Sie *einen einzigen* Wert suchen, der bestimmten Kriterien entspricht und nicht mehrere übereinstimmende Werte, die summiert werden.
Wie es funktioniert:
Die SUMMENPRODUKT-Funktion multipliziert Arrays elementweise und gibt die Summe der Produkte zurück. Wir nutzen dies aus, indem wir Arrays erstellen, die WAHR (1) oder FALSCH (0) basierend auf unseren Suchkriterien enthalten.
Schritt-für-Schritt-Anleitung:
=SUMMENPRODUKT((A2:A10="Suchkriterium1")*(B2:B10="Suchkriterium2")*(C2:C10))- A2:A10 ist der Bereich für das erste Suchkriterium.
- „Suchkriterium1” ist der Wert des ersten Suchkriteriums.
- B2:B10 ist der Bereich für das zweite Suchkriterium.
- „Suchkriterium2” ist der Wert des zweiten Suchkriteriums.
- C2:C10 ist der Bereich, aus dem der Wert zurückgegeben wird (dies *muss* numerisch sein, da SUMMENPRODUKT multipliziert).
Wichtig: Diese Formel funktioniert am besten, wenn nur *eine* Zeile die Kriterien erfüllt. Wenn mehrere Zeilen die Kriterien erfüllen, wird die Summe der Werte in Spalte C zurückgegeben. Wenn Sie Textwerte zurückgeben müssen, ist diese Methode nicht geeignet.
Vorteile: Keine Array-Formel erforderlich (einfacher zu handhaben als INDEX/VERGLEICH). Relativ einfach zu verstehen.
Nachteile: Funktioniert nur, wenn die zurückzugebende Spalte numerisch ist und nur eine Zeile die Kriterien erfüllt. Nicht flexibel wie INDEX/VERGLEICH.
Technik 4: Die AGGREGAT-Funktion (Excel 2010 und höher)
Die AGGREGAT-Funktion ist eine vielseitige Funktion, die in Excel 2010 eingeführt wurde. Sie kann verschiedene Berechnungen durchführen und dabei Fehlerwerte und ausgeblendete Zeilen ignorieren. Sie kann auch verwendet werden, um den SVERWEIS mit mehreren Suchkriterien zu emulieren.
Schritt-für-Schritt-Anleitung:
=AGGREGAT(15;6;ZEILE(A2:A10)/( (A2:A10="Suchkriterium1")*(B2:B10="Suchkriterium2") );1)- 15: Gibt an, dass wir die Funktion KLEINSTE verwenden (kleinster Wert).
- 6: Gibt an, dass wir Fehlerwerte ignorieren sollen.
- ZEILE(A2:A10): Gibt ein Array mit den Zeilennummern des Bereichs zurück.
- (A2:A10=”Suchkriterium1″)*(B2:B10=”Suchkriterium2″): Erstellt ein Array mit WAHR/FALSCH-Werten basierend auf den Suchkriterien.
- / (Division): Wenn die Suchkriterien erfüllt sind, dividieren wir die Zeilennummer durch 1 (WAHR), was die Zeilennummer ergibt. Wenn die Suchkriterien nicht erfüllt sind, dividieren wir die Zeilennummer durch 0 (FALSCH), was einen Fehlerwert ergibt (Division durch Null).
- ;1: Gibt an, dass wir den kleinsten Wert (die erste übereinstimmende Zeile) suchen.
Diese Formel gibt die Zeilennummer der ersten Zeile zurück, die alle Suchkriterien erfüllt. Verwenden Sie diese Zeilennummer dann mit der INDEX-Funktion, um den Wert aus der gewünschten Spalte zurückzugeben:
=INDEX(C2:C10;AGGREGAT(15;6;ZEILE(A2:A10)/( (A2:A10="Suchkriterium1")*(B2:B10="Suchkriterium2") );1))Vorteile: Ignoriert Fehlerwerte. Keine Array-Formel erforderlich.
Nachteile: Etwas komplexer zu verstehen als die Hilfsspaltenmethode. Nur in Excel 2010 und höher verfügbar.
Zusammenfassung und Empfehlungen
Die beste Methode zur Verwendung des SVERWEIS mit mehreren Suchkriterien hängt von Ihren spezifischen Anforderungen und der Größe Ihres Datensatzes ab:
- Hilfsspalte: Die einfachste und schnellste Lösung für kleinere Datensätze.
- INDEX/VERGLEICH: Die flexibelste und leistungsfähigste Lösung für größere Datensätze und dynamische Daten.
- SUMMENPRODUKT: Geeignet, wenn Sie *einen einzigen* numerischen Wert suchen, der bestimmten Kriterien entspricht.
- AGGREGAT: Eine gute Alternative, wenn Sie Fehlerwerte ignorieren müssen und keine Array-Formeln verwenden möchten (verfügbar ab Excel 2010).
Experimentieren Sie mit den verschiedenen Techniken und wählen Sie diejenige, die für Ihre Situation am besten geeignet ist. Mit etwas Übung werden Sie den SVERWEIS mit mehreren Suchkriterien wie ein Profi meistern!