Excel – Wert über mehrere Suchkriterien finden – SUMMENPRODUKT

Zwei oder mehrere Suchkriterien können, insofern die Kombination der Suchkriterien keine Doppelten Paare erlaubt, genutzt werden, um über die Funktionen INDEX und SUMMENPRODUKT einen Zellinhalt wiederzugeben.

Anbei die Formel, falls der Datenbereich nicht als Tabelle formatiert ist.

434a

Hier die Formel:
=INDEX(A2:A10;SUMMENPRODUKT(ZEILE(A2:A10)-1;--(B2:B10=F2);--(C2:C10=F1));1)

INDEX(A2:A10;Zeile;1) sorgt dafür, dass später ein Wert aus diesem Bereich wiedergegeben wird.

SUMMENPRODUKT(ZEILE(A2:A10)-1;–(B2:B10=F2);–(C2:C10=F1)) ermittelt diese Zeile wie folgt:
Die Arrays aus SUMMENPRODUKT werden mit einander multipliziert. Das wäre zum einen die jeweilige Zeile – 1
ZEILE(A2:A10)-1, das wäre die Prüfung, ob in der jeweiligen passenden Zeile in Spalte B das gewünschte Suchkriterium steht und ob das Suchkriterium auch in Spalte C steht. Das Doppelminus sorgt dafür, dass aus einem WAHR als Ergebnis der Prüfung eine 1 wird. Man hätte hier auch WAHR mit dem Wert Eins multiplizieren können.
Wenn dann also Zeilennummer * 1 * 1 genommen werden kann, haben wir die richtige Zeilennummer gefunden. Bei FALSCH bei einer Prüfung käme entweder Zeilennummer * 0 * 1, Zeilennummer * 1 * 0 oder Zeilennummer * 0 * 0 heraus. In Summe macht das beim SUMMENPRODUKT halt dann nur wirklich den Wert der Zeilennummer.

Sollte der Bereich mit Als Tabelle formatieren oder STRG + T als Tabelle formatiert worden sein, lassen sich die Namen verwenden und die Formel bezieht sich immer auf den kompletten Datenbereich.

434b

Hier ist die Übungsdatei
Excel_434_SUMMENPRODUKT_2_Suchkriterien

Hier geht es zum Video

2 Kommentare

  1. Herbert Schmidt

    Hallo Andreas, wen ich ein Kriterium eingebe, das nicht in der Suche vorkommt (steht ja in der Zelle: =Index($A$2;0;1)). Man würde aber #Wert erwarten. Diesen bekommt man auch, wenn ich den ganzen Suchbereich z.B. ab A15 eintrage.
    So gibt es das Ergebnis Bea. Wenn man aber die die Formel in F3 mit 1 multipliziert, dann stimmen die Ergebnisse wieder und es gibt den Fehlerwert: #Wert.
    LG, Herbert