Verweisfunktionen (3) -> SVERWEIS bei mehrspaltigen Suchkriterien + Mehrfachtreffer (30)

Aufgabe
Bei der SVERWEIS-Funktion wird nur eine Spalte durchsucht.
Wie lautet die Formel, wenn das Suchkriterium in zwei Spalten A und B steht?

A               B               C        1 a  b  20   2 x  k  17   3 m  y  30   4 x  y  60   5 g  h  10   6 x  y  22   7 w  f  24   8 e  d  62   9 x  y  35   10 y  x  40

Lösung
Suchspalte 1 : A, Suchbegriff "x"
Suchspalte 2 : B, Suchbegriff "y"
Ergebnisspalte: C

{=INDEX(C:C;VERGLEICH("x"&"y";A1:A99&B1:B99;0))}
Kann es vorkommen, dass xy in Spalte A und nichts in Spalte B steht, dann:
{=INDEX(C:C;VERGLEICH("x"&"*"&"y";A1:A99&"*"&B1:B99;0))}
bzw. durch Vergleich des Wahrheitswertes 1:
{=INDEX(C:C;VERGLEICH(1;(A1:A99="x")*(B1:B99="y");0))}

ohne Abschluss als {Array}:
=INDEX(C:C;VERGLEICH(1;MMULT((A1:A99="x")*(B1:B99="y");1);0))
sowie:
=INDEX(C:C;SummenPRODUKT((A1:A99="x")*(B1:B99="y")*ZEILE(1:99)))
sowie:
=INDEX(C:C;VERGLEICH("x"&"y";INDEX(A1:A99&B1:B99;0);0))
sowie:
=VERWEIS(2;1/(A1:A99&B1:B99="x"&"y");C:C)
bzw.
=VERWEIS(2;1/(A1:A99&"*"&B1:B99="x"&"*"&"y");C:C)

Kommt die Suchbegriffskombination mehrfach vor, liefern die INDEX-Formeln den ersten, die VERWEIS-Formeln den letzten Treffer.
Sollen alle Treffer aufgelistet werden:
{=WENN(ZEILE(Z1)>SUMME((A$1:A$99="x")*(B$1:B$99="y"));"";INDEX(C:C;KKLEINSTE(WENN((A$1:A$99="x")*(B$1:B$99="y");ZEILE($1:$99));ZEILE(Z1))))}
runterkopieren

Erläuterung
"x" und "y" können natürlich auch in einzelnen Zellen stehen (ohne Gänse).
Die GROSS-klein-Schreibung der Suchkriterien wird ignoriert.
"komplette Spalten" funktionieren nicht in Arrayformeln (Excelversionen vor 2010) - die Treffer-Spalte betrifft dies nicht.

zurück

gestaltet by Biene Maya