SVERWEIS bei mehrspaltigen Suchkriterien + Mehrfachtreffer

Gruppe: Verweisfunktionen

Beitrag: SVERWEIS bei mehrspaltigen Suchkriterien + Mehrfachtreffer

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, daß 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.

Das Portal zum Thema Excel-Formeln