Wie findet man in Excel einen Wert anhand von zwei oder mehreren Kriterien?

Stellen Sie sich vor, Sie arbeiten mit einer Obstverkaufstabelle wie der unten dargestellten. Möglicherweise müssen Sie den Verkaufsbetrag anhand mehrerer Kriterien – etwa Obstsorte, Verkaufsdatum und Gewicht – ermitteln. Mit Arrayformeln in Excel lassen sich diese Werte effizient abrufen, selbst wenn mehrere Bedingungen gleichzeitig erfüllt sein müssen. Diese Methode ist flexibel und eignet sich ideal für Datensätze, bei denen Sie einen einzelnen Zellwert suchen, der mehreren Kriterien entspricht.

Arrayformel 1: Wert mit zwei oder mehreren Kriterien in Excel finden

Die allgemeine Struktur dieser Arrayformel lautet wie folgt:

{=INDEX(Matrix;VERGLEICH(1;(Kriterium1=Suchmatrix1)*(Kriterium2=Suchmatrix2)…*(Kriterium n=Suchmatrix n);0))}

Wenn Sie beispielsweise den Verkaufsbetrag für Mangoam 9/3/2019ermitteln möchten, geben Sie die folgende Formel in eine leere Zelle ein und drücken Sie Strg+Umschalttaste+Enter, um sie als Arrayformel zu bestätigen:

=INDEX(F3:F22;VERGLEICH(1;(J3=B3:B22)*(J4=C3:C22);0))

Hinweis: In diesem Beispiel

• F3:F22 ist die Spalte „Betrag“, aus der Sie den Wert abrufen möchten.
• B3:B22 ist die Spalte „Datum“; C3:C22 ist die Spalte „Obst“.
• J3 enthält das Datum, das als erstes Kriterium gewählt wurde; J4 enthält den Obstnamen, der als zweites Kriterium verwendet wird.

Das Hinzufügen weiterer Kriterien ist unkompliziert. Um beispielsweise den Verkaufsbetrag für Mango am 9,3.2019 mit einem Gewicht von 211 zu suchen, fügen Sie die dritte Bedingung sowohl im VERGLEICH-Teil als auch in den Suchmatrizen wie folgt hinzu:

=INDEX(F3:F22;VERGLEICH(1;(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22);0))

Drücken Sie nach der Eingabe der Formel erneut Strg+Umschalttaste+Enter, um sie zu bestätigen. Das Ergebnis ist der Verkaufsbetrag, der alle angegebenen Kriterien erfüllt.

Arrayformel 2: Wert mit zwei oder mehreren Kriterien in Excel durch Verkettung finden

Alternativ können Sie in Ihrer Formel eine Verkettung nutzen, um eine andere Herangehensweise zu verfolgen – besonders dann, wenn Sie eine kompakte Struktur bevorzugen. Die grundlegende Formel lautet:

=INDEX(Matrix;VERGLEICH(Kriterium1&Kriterium2…&KriteriumNSuchmatrix1&Suchmatrix2…&SuchmatrixN0);0)

Um beispielsweise den Verkaufsbetrag für ein Obst mit einem Gewicht von 242am 9/1/2019abzurufen:

=INDEX(F3:F22;VERGLEICH(J3&J4B3:B22&C3:C22;0);0)

Hinweis: Hier

• F3:F22 ist die Betragsspalte; B3:B22 ist das Datum; E3:E22 ist die Gewichtsspalte.
• J3 enthält das Datum; J5 enthält den Gewichtungswert für Ihre Kriterien.

Für mehr als zwei Kriterien erweitern Sie sowohl die Kriterien als auch die Suchmatrizen in derselben Reihenfolge:

=INDEX(F3:F22;VERGLEICH(J3&J4&J5B3:B22&C3:C22&E3:E22;0);0)

Drücken Sie wie zuvor Strg + Umschalttaste + Enter, um das korrekte Ergebnis zu erhalten.

Beide Arrayformel-Methoden ermöglichen es Ihnen, den ersten Wert zu finden, der alle Ihre Kriterien erfüllt. Sie setzen jedoch voraus, dass die Zellbereiche dieselbe Größe haben, und liefern nicht mehrere übereinstimmende Werte – es wird ausschließlich die erste Übereinstimmung zurückgegeben. Falls keine Übereinstimmung vorliegt, gibt die Formel einen #NV-Fehler zurück. Wenn Sie eine Formel bevorzugen, die sämtliche Übereinstimmungen anzeigt, sollten Sie die FILTER-Funktion in Betracht ziehen (Details dazu finden Sie unten).

Einige praktische Tipps und Hinweise:

• Wenn Sie mit neueren Excel-Versionen wie Microsoft 365 oder Excel 2021 arbeiten, können Sie dynamische Arrayformeln und die FILTER-Funktion nutzen, um diesen Vorgang zu vereinfachen.
• Um #N/A-Fehler zu vermeiden, wenn keine Übereinstimmungen vorhanden sind, betten Sie die Formel in WENNFEHLER ein – zum Beispiel: =IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),„Not found").
• Stellen Sie sorgfältig sicher, dass Ihre Suchkriterienzellen weder zusätzliche Leerzeichen noch unterschiedliche Datentypen enthalten.
• Wenn Sie nach dem Drücken von nur Enter einen Fehler erhalten, drücken Sie unbedingt Strg+Umschalttaste+Enter, um die Formel als Arrayformel zu bestätigen (für Excel 2019 und früher).

Zusätzlich zur Verwendung von Formeln bietet Excel die Erweiterte Filter-Funktion, mit der Sie alle Zeilen filtern und extrahieren können, die zwei oder mehr Kriterien erfüllen, und die übereinstimmenden Ergebnisse an einem anderen Ort anzeigen lassen. Dieser Ansatz ist besonders hilfreich, wenn Sie alle Datensätze sehen möchten, die Ihre festgelegten Bedingungen erfüllen – statt nur einen einzelnen Wert abzurufen. So geht’s:

1. Wechseln Sie zur Daten-Registerkarte und wählen Sie in der Gruppe „Sortieren und Filtern“ Erweitert, um das Dialogfeld „Erweiterter Filter“ zu öffnen.

2. Führen Sie im Dialogfeld Erweiterter Filter die folgenden Einstellungen durch:
(1) Wählen Sie im Abschnitt Aktion die Option An andere Stelle kopieren aus.
(2) Markieren Sie für Listeden Bereich mit den Daten, die Sie filtern möchten ()A1:E21 in diesem Beispiel).
(3) Wählen Sie für Kriterienbereichden Bereich mit Ihren Filterbedingungen aus ()H1:J2 hier). Stellen Sie sicher, dass die Überschriften in diesem Kriterienbereich exakt mit denen in Ihrer Datentabelle übereinstimmen.
(4) Wählen Sie bei Kopieren nachdie erste Zelle aus, an der Sie die gefilterten Ergebnisse einfügen möchten ()H9 in diesem Fall).

3. Klicken Sie auf OK, um die Filterung durchzuführen.

Die Zeilen, die sämtliche Bedingungen Ihres Kriterienbereichs erfüllen, werden in den von Ihnen festgelegten Zielbereich kopiert – ideal, um alle Datensätze zu prüfen oder zu berichten, die mehrere Filterkriterien gleichzeitig erfüllen.

Einige Tipps und Vorsichtsmaßnahmen:

• Achten Sie darauf, dass die Überschriften Ihres Kriterienbereichs exakt mit denen in Ihrer Hauptdatentabelle übereinstimmen – andernfalls funktioniert der Filter möglicherweise nicht korrekt.
• Der erweiterte Filter unterstützt sowohl UND- als auch ODER-Bedingungen: Werden Kriterien in derselben Zeile platziert, gilt die UND-Logik (alle Bedingungen müssen erfüllt sein); bei Verwendung separater Zeilen greift die ODER-Logik (mindestens eine Bedingung muss erfüllt sein).
• Der erweiterte Filter aktualisiert sich nicht dynamisch, wenn sich Ihre Daten ändern; Sie müssen den Filter erneut anwenden, nachdem Sie Ihre Daten oder Kriterien aktualisiert haben.
• Beachten Sie, dass leere Zellen im Kriterienbereich möglicherweise als „beliebiger Wert“ für dieses Feld interpretiert werden.

Im Vergleich zu formelbasierten Lösungen eignet sich der erweiterte Filter besonders gut zum Extrahieren vollständiger Datensätze, die den Kriterien entsprechen, anstatt nur einer einzelnen Zelle. Er ist jedoch nicht für Echtzeit- oder häufig aktualisierte Abfragen geeignet, da der Filter nach jeder Datenänderung erneut ausgeführt werden muss.

Wenn Sie eine aktuelle Version von Excel verwenden (Microsoft 365 oder Excel 2021 und neuer), bietet die FILTER-Funktion eine dynamische und intuitive Möglichkeit, alle Werte abzurufen, die mehrere Kriterien erfüllen. Diese Lösung ist besonders empfehlenswert, wenn sich Ergebnisse automatisch aktualisieren sollen – sobald sich Daten oder Kriterien ändern – und kommt ganz ohne komplizierte Matrixformeleingabe aus.

1. Geben Sie in einer leeren Zelle eine Formel wie die folgende ein:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

In dieser Formel:

• F3:F22 ist Ihre Betragsspalte.
• B3:B22 ist die Datumsspalte, abgeglichen mit dem Datum in J3.
• C3:C22 ist die Obstsäule, abgeglichen mit dem Obst in J4.

Wenn Sie eine dritte Bedingung hinzufügen möchten, beispielsweise dass die Spalte Gewicht (E3:E22)einem Wert in J5entspricht, erweitern Sie die Formel:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

Nachdem Sie die Eingabetaste gedrückt haben, zeigt Excel alle Beträge an, die sämtliche Kriterien erfüllen. Falls keine Übereinstimmung gefunden wird, gibt die Formel einen #BERECHNEN!-Fehler zurück, den Sie mithilfe von WENNFEHLER abfangen können:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Vorteile:

• Die Ergebnisse aktualisieren sich automatisch, sobald sich Ihre Daten oder Kriterien ändern.
• Formeln lassen sich einfacher pflegen und erweitern als herkömmliche Arrayformeln.
• Gibt alle Übereinstimmungen zurück, nicht nur den ersten gefundenen Wert.

Einschränkung: Nur verfügbar in Microsoft 365 oder Excel 2021 und höher. Wird in älteren Versionen nicht unterstützt.