Mehrfacher SVERWEIS: Nach mehreren Kriterien gleichzeitig suchen

12. August 2014
Martin Althaus Von Martin Althaus, Excel, Office ...

Es gibt verschiedene Möglichkeiten, Listen oder Tabellen nach Inhalten zu durchsuchen. Die komfortabelste Variante besteht darin, aus einer Zelle ein Suchfeld zu machen, welches automatisch den dazugehörigen Inhalt ermittelt. Mit dieser Methode sorgen Sie für automatisierte Tabellen.

Excel bietet Ihnen für das Aufspüren von Zellinhalten per Formel verschiedene Verweisfunktionen wie WAHL, VERGLEICH, VERWEIS, INDEX und SVERWEIS an. Sie übergeben der Funktion einen gesuchten Inhalt und die Funktion stellt fest, ob oder wo der Inhalt in einer Tabelle auftaucht. 

Ein Vorteil der meisten Verweisfunktionen liegt darin, dass sie auch den nächstkleineren Wert finden können, wenn der gesuchte Wert nicht gefunden wird. So fragen Sie zum Beispiel Umsatztabellen oder Artikellisten ab und finden immer ein Ergebnis. Allerdings haben alle Verweisfunktionen eines gemeinsam: Sie können von Haus aus immer nur einen Suchparameter angeben. Indem Sie aber eine spezielle Matrixformel einsetzen, können Sie Verweise in Excel auch mit mehreren Suchkriterien einsetzen.

In der abgebildeten Arbeitsmappe [1] sehen Sie eine Artikelliste, aus der Sie schnell und bequem anhand mehrerer Kriterien passende Artikelnummern finden möchten. Die Kriterien haben Sie rechts neben der Liste aufgeführt. Die gefundene Artikelnummer soll in Zelle H8 erscheinen.

Der erste Schritt besteht darin, eine Formel einzubinden, mit der Sie eine Artikelnummer anhand eines Suchkriteriums ermitteln. Diese Formel erweitern Sie dann Schritt für Schritt für die weiteren Suchkriterien. Über die folgende Formel in Zelle H8 suchen Sie die Artikelnummer zur Größe aus Zelle H3: [2]

=SVERWEIS(H3;A3:E40;5)

 

Sie übergeben der SVERWEIS-Funktion als erstes Argument die komplette Liste, in der Sie einen Inhalt suchen. Als zweites Argument übergeben Sie das Suchkriterium, nach dem in der ersten Spalte des übergebenen Bereichs gesucht werden soll. Das dritte Argument definiert die Spalte, aus der das Ergebnis geliefert werden soll. Übergeben Sie die Liste anhand der ersten Spalte aufsteigend sortiert, damit Excel den nächstkleineren Wert finden kann. Die Formel findet die Artikelnummer 2.253 aus Zelle E16. Da die Größe 142 aus Zelle H3 in der Liste nicht auftritt, wird der nächstkleinere Wert 139 gefunden.

Weiteres Suchkriterium professionell einbinden

Im nächsten Schritt möchten Sie als zweites Kriterium zusätzlich nach der Gruppe in Zelle H4 suchen. Das heißt, es soll nur eine Artikelnummer gefunden werden, bei der die gesuchte Gruppe auftritt. Dazu erweitern Sie die Formel in Zelle H8 um eine WENN-Funktion und setzen die Formel als Matrixformel ein:

=SVERWEIS(H3;WENN(B3:B40=H4;A3:E40;““);5)

Bestätigen Sie die Formel nach der Eingabe über die Tastenkombination Strg+Umschalt+Eingabe als Matrixformel. [3]

Als Ergebnis wird nun die Artikelnummer 1.188 geliefert. Die Formel findet in der Spalte „Größe“ den Wert 126, da dies der größte Wert ist, der kleiner oder gleich dem gesuchten Wert 142 ist und als Gruppe den Inhalt C2 aufweist.

Die WENN-Funktion in der Formel überprüft jede Zelle des Bereichs B3:B40 auf Übereinstimmung mit dem Inhalt aus Zelle H4. Nur wenn eine Übereinstimmung vorliegt, wird die entsprechende Zeile des Bereichs B3:E40 an die SVERWEIS-Funktion übergeben.

Weitere Suchkriterien ganz einfach hinzufügen

Nach demselben Muster können Sie zusätzliche Suchkriterien einbinden, indem Sie in der Formel weitere WENN-Funktionen verschachteln. Um alle vier Suchkriterien zu berücksichtigen, binden Sie zwei weitere verschachtelte WENN-Funktionen in die Formel in Zelle H8 ein:

=SVERWEIS(H3;WENN(B3:B40=H4;WENN(C3:C40 =H5;WENN(D3:D40=H6;A3:E40;““);““);““);5)

Nach der Bestätigung der Formel über die Tastenkombination Strg+Umschalt+Eingabe liefert sie die Artikelnummer 1.748. Dies ist die Nummer aus Zeile 14 zur Größe 125. Das ist die Zeile mit dem höchsten Wert in der Spalte „Größe“, in der alle drei zusätzlichen Kriterien erfüllt sind. [4]

 

Die erste WENN-Funktion übergibt nun bei positiver Prüfung nicht schon die entsprechende Zeile an die SVERWEIS-Funktion. Stattdessen prüft eine weitere WENN-Funktion auf die Übereinstimmung mit dem zweiten zusätzlichen Kriterium. Falls auch diese Prüfung positiv ausfällt, kommt die dritte WENN-Funktion zum Einsatz. Erst wenn alle drei WENN-Funktionen eine positive Prüfung liefern, übergibt die dritte WENN-Funktion die entsprechende Zeile an die SVERWEIS-Funktion. Auf diesem Wege können Sie bis zu sieben WENN-Funktionen verschachteln und somit auf bis zu acht Kriterien prüfen.

Werden Sie mit unseren Tipps zum Excel-Profi!

Jetzt gratis per E-Mail

DDV Ehrencodex E-Mail-Marketing
  • Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters.
  • Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter.
  • Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Datenschutzgarantie:

Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters. Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter. Jederzeit abbestellbar durch einen Link im Newsletter.

Weitere Artikel zum Thema

Newsletter

Jetzt kostenlose Excel-Hilfe anfordern!

  • die besten Tipps
  • völlig kostenlos
  • jederzeit abbestellbar
DDV Ehrencodex E-Mail-Marketing
  • Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters.
  • Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter.
  • Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Datenschutzgarantie:

Wir benötigen Ihre E-Mail-Adresse für die Zusendung des Newsletters. Wir geben Ihre E-Mail-Adresse garantiert nicht an Dritte weiter. Jederzeit abbestellbar durch einen Link im Newsletter.

Unsere Experten

Rudolf Ring twittert für @Computerwissen

Die von Ihnen eingegebene E-Mail Adresse ist nicht korrekt. Bitte korrgieren Sie Ihre Eingabe und klicken Sie auf "OK":

Bitte wählen Sie mindestens einen Newsletter aus und klicken Sie auf "OK"