Mehrfacher SVERWEIS: Wie Sie in Excel nach mehreren Kriterien gleichzeitig suchen
Die schnellste und beste Möglichkeit, nach spezifischen Kriterien zu suchen, besteht in der Anwendung der Funktion SVERWEIS. Mit ihr ist es möglich, einen gesuchten Wert in Microsoft Excel in Millisekunden zu finden.
Der SVERWEIS – so funktioniert er mit einem Suchkriterium
Um zu verstehen, wie der SVERWEIS mit mehreren Suchkriterien funktioniert, müssen Excel-User im ersten Schritt verstehen, wie die Funktion SVERWEIS grundsätzlich aufgebaut ist. Im folgenden Beispiel wird die Anzahl der angestellten Aushilfen in einem Unternehmen in den unterschiedlichen Monaten eines Kalenderjahres beleuchtet.
Es soll durch einen SVERWEIS dargestellt werden, wie viele Aushilfen im Monat März und im Monat Dezember beschäftigt waren.
Die SVERWEIS-Formel im Beispiel für den Monat Dezember lautet:
SVERWEIS(D8;A:B;2;0)
Die Formel sucht wie folgt nach den entsprechenden Zahlen für Aushilfen:
- Suche den Wert von D8 (Monat Dezember) in den Spalten A und B.
- Suche den Wert der Spalte B. Diese wird durch die 2 in der Formel dargestellt.
- Die 0 in der Formel zeigt an, dass nach einem exakten Wert gesucht werden soll.
Die Funktion SVERWEIS sucht zusammenfassen nach einem einzelnen Suchkriterium. Die Matrix der Formel ist der Bereich, in dem sich die relevanten Werte befinden. Zusätzlich muss im SVERWEIS die Spalte angegeben werden, in der der gesuchte Wert enthalten ist.
Wichtig:
Bei der Funktion SVERWEIS muss das Suchkriterium grundsätzlich in der Spalte 1 stehen. Die gesuchten zugehörigen Werte müssen darüber hinaus rechts vom Suchkriterium abgebildet sein. Ist die Kalkulationstabelle anders aufgebaut, funktioniert der SVERWEIS nicht. Eine Kombination aus den Funktionen INDEX und VERGLEICH kann in diesem Fall zielführend sein, die gewünschte Abfrage zu erstellen.
SVERWEIS mit mehreren Suchkriterien – so integrieren Sie die WENN-Funktion
Im oben abgebildeten Beispiel wurde abgefragt, wie viele Aushilfen in einem bestimmten Monat in einem Unternehmen gearbeitet haben. Mit der Funktion SVERWEIS konnten die spezifischen Werte problemlos abgeleitet werden. Im folgenden Beispiel sehen Sie eine umfangreiche Artikelliste mit 36 Einträgen [1], aus der Sie schnell und bequem anhand mehrerer Kriterien passende Artikelnummern filtern möchten.
Die Suchkriterien werden rechts in der Kalkulationstabelle aufgeführt. Die gefundene Artikelnummer soll in der Zelle H8 erscheinen.
Der erste Schritt besteht darin, eine Formel mit SVERWEIS einzubinden, mit der Sie eine Artikelnummer anhand eines Suchkriteriums ermitteln. Diese Formel erweitern Sie in der Folge 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
Die Formel sucht auf folgende Weise nach der entsprechenden Artikelnummer:
- Suche den Wert von H3 (Größe 142) in den Spalten A bis E.
- Suche den Wert der Spalte E (Artikelnummer). Diese wird durch die 5 in der Formel dargestellt.
Zusammenfassend übergibt die SVERWEIS-Funktion als erstes Argument die komplette Liste, in der Sie einen Inhalt suchen. Als zweites Argument wird das Suchkriterium fokussiert, 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.
Ein zweites Suchkriterium einbinden
Im nächsten Schritt möchten Sie als zweites Kriterium zusätzlich nach der Gruppe in Zelle H4 suchen. Es soll ausschließlich 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)
Info:
Im Microsoft Excel unterscheidet man zwischen herkömmlichen Formeln und den leistungsfähigeren Matrixformeln. Matrixformeln ermöglichen es Excel-Nutzern, komplexe Berechnungen anzustellen. Diese können mit den Standardformeln nicht durchgeführt werden. Da nach der Eingabe der Formel zwingend die Tastenkombination „STRG + UMSCHALT + EINGABE" betätigt werden muss, sind Matrixformeln international als oder CSE-Formeln (CTRL + SHIFT + ENTER) bekannt.
Bestätigen Sie die Formel nach der Eingabe über die Tastenkombination Strg + Umschalt + Eingabe als Matrixformel. [3]
Als Ergebnis wird die Artikelnummer 1.188 geliefert. Die Formel findet in der Spalte „Größe“ den Wert 126. Es handelt sich hierbei um den größten Wert in der Kalkulationstabelle. Er ist kleiner oder gleich dem gesuchten Wert 142 ist und weist in Bezug auf die Gruppe den Inhalt C2 auf.
Die WENN-Funktion in der Formel überprüft jede Zelle des Bereichs B3:B40 auf Übereinstimmung mit dem Inhalt aus Zelle H4. Sobald eine Übereinstimmung vorliegt, wird die entsprechende Zeile des Bereichs B3:E40 an die SVERWEIS-Funktion übergeben.
Weitere Suchkriterien jederzeit problemlos hinzufügen
Nach demselben Muster können Sie zusätzliche Suchkriterien hinzufügen. Dies geschieht, 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 Matrixformel über die Tastenkombination Strg + Umschalt+ Eingabe liefert der SVWERWEIS die Artikelnummer 1.748. Dies ist die Nummer aus Zeile 14 zur Größe 125. Es handelt sich um 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 bei positiver Prüfung nicht sofort die entsprechende Zeile an die SVERWEIS-Funktion. Stattdessen prüft eine weitere WENN-Funktion, ob es eine Übereinstimmung mit dem zweiten zusätzlichen Kriterium gibt. Falls diese Prüfung ebenfalls 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.
Zusammenfassung: So können Sie in Excel effektiv nach mehreren Kriterien suchen
Nutzer von Microsoft Excel stehen in der täglichen Arbeit fortlaufend vor der Frage, wie Sie Daten und Informationen aus umfangreichen oder verschachtelten Kalkulationstabellen auslesen können. Die wirkungsvollste Verweisfunktion finden Excel-User in der Funktion SVERWEIS. Ein wesentlicher Vorteil der Funktion SVERWEIS und anderer Verweisfunktionen liegt darin, dass sie standardmäßig nach dem nächstkleineren Wert suchen, sobald der gesuchte Wert nicht gefunden wird.
Da die Funktion SVERWEIS standardmäßig auf einen Suchbegriff limitiert ist, muss bei mehreren Suchkriterien eine erweiterte Formel zum Einsatz kommen. Um mehrere Suchbegriffe über die Funktion SVERWEIS zu finden, eignet sich eine Matrixformel mit WENN-Funktionen. Sie prüft nacheinander die einzelnen WENN-Funktionen und verweist bei positivem Ergebnis auf den SVERWEIS. Auf diese Weise ist sichergestellt, dass bei umfangreichen Tabellenkalkulationen mit verschiedenen Suchbegriffen das korrekte Ergebnis ausgegeben wird.
FAQ
Was kann die Funktion SVERWEIS?
Die Funktion SVERWEIS in Microsoft Excel gehört zu den wichtigsten und effektivsten Funktionen in Excel. Über SVERWEIS können Sie eine umfangreiche Kalkulationstabelle automatisiert nach einem bestimmten Wert durchsuchen. Das Ergebnis wird aufbereitet in einer anderen Zelle ausgegeben.
Wann nutzt man die Funktion WENN in EXCEL?
Die Funktion WENN gehört zu den am meisten genutzten Funktionen in Microsoft Excel. Mit der Funktion WENN legt man im ersten Schritt eine Bedingung fest. Im zweiten Schritt verknüpft man WENN beispielsweise mit der Funktion DANN und legt fest, welche Information ausgegeben wird. Mit der Funktion SONST kann man die Ergebnisalternative ebenfalls bestimmen. Praxisbeispiel: WENN Mitarbeiter A mehr als 240 Tage im Jahr gearbeitet hat DANN Prämie A SONST Prämie B.
Welcher Unterschied besteht in Excel zwischen Matrixformeln und Standardformeln?
Am bekanntesten sind in Excel die herkömmlichen Standardformeln. Als Matrixformeln bezeichnet man in Excel leistungsfähige Formeln, mit denen es möglich ist, komplexe Berechnungen anzustellen. Um eine Matrixformel auszuführen, muss die Matrixformel nach der Eingabe mit der Tastenkombination "STRG+UMSCHALT+EINGABE" bestätigt werden. Die Tastenkombination zeigt Excel, dass es sich um eine Matrixformel handelt.
