SVERWEIS bei mehrfach vorkommendem Suchkriterium

Gruppe: Verweisfunktionen

Beitrag: SVERWEIS bei mehrfach vorkommendem Suchkriterium

Aufgabe

SVERWEIS findet nur den ersten Eintrag, der dem Suchkriterium entspricht.
Es sollen aber alle Einträge bei einem mehrfach vorkommendem Suchkriterium aufgelistet werden.

	A	B	C	D	E
1	367			849	990
2	121			990	849
3	232			451	451
4	849	x			0
5	185				0
6	382
7	990	x
8	847
9	861
10	451	x		Formel 1	Formel 2 *

*Kurzfassung

Lösung

Suchspalte: B
Ergebnisspalte: A
Suchbegriff:"x" (stattdessen natürlich auch eine Zelladresse ohne "")

{=INDEX(A:A;KKLEINSTE(WENN(B$1:B$99="x";ZEILE(X$1:X$99));1))}

Ermittelt den ersten Wert, der dem Suchkriterium entspricht.

Handelt es sich in der Ergebnisspalte um Zahlen größer Null und ist die Reihenfolge des Vorkommens egal, geht es kürzer (es wird absteigend aufgelistet) mit:

{=KGRÖSSTE((B$1:B$99="x")*A$1:A$99;1)}

bzw. ohne Array-Abschluss =KGRÖSSTE(INDEX((B$1:B$99="x")*A$1:A$99;0);1)

{=INDEX(A:A;KKLEINSTE(WENN(B$1:B$99="x";ZEILE($1:$99));2))}

{=KGRÖSSTE((B$1:B$99="x")*A$1:A$99;2)}

bzw. =KGRÖSSTE(INDEX((B$1:B$99="x")*A$1:A$99;0);2)
Ermittelt den zweiten Wert; - usw.

Zum runterkopieren (ohne jeweils manuell die Formel hinten mit 1 2 3 ... anpassen zu müssen) mit:

{=WENN(ZEILEN($1:1)>ZÄHLENWENN(B:B;"x");"";INDEX(A:A;KKLEINSTE(WENN(B$1:B$99="x";ZEILE(X$1:X$99));ZEILE(X1))))}

ab Excel-2007:

{=WENNFEHLER(INDEX(A:A;KKLEINSTE(WENN(B$1:B$99="x";ZEILE(X$1:X$99));ZEILE(X1)));"")}

sowie:

{=KGRÖSSTE((B$1:B$99="x")*A$1:A$99;ZEILE(X1))}

bzw. =KGRÖSSTE(INDEX((B$1:B$99="x")*A$1:A$99;0);ZEILE(X1))

In Formel 1 wurde noch der Teil mit ZÄHLENWENN bzw. WENNFEHLER eingebaut, um Fehlermeldungen, wenn kein x mehr auftaucht zu unterdrücken.
Ist in Nr. 2 wohl nicht nötig, da dann 0 (Null) ausgegeben wird.
Die Variante ohne Array-Abschluss funktioniert nicht mit WENN (hier bei KKLEINSTE).

Ergänzung:
Soll nicht nur nach einem Kriterium sondern mehreren gesucht werden (hier x oder y in Spalte B), dann so (ODER funktioniert nicht):

{=WENNFEHLER(INDEX(A:A;KKLEINSTE(WENN((B$1:B$99="x")+(B$1:B$99="y");ZEILE(X$1:X$99));ZEILE(X1)));"")}

Erweiterung - ohne Doppler (neopa).
Kommen identische Einträge in Spalte A mit dem Suchkriterium in Spalte B vor und diese sollen nur eimal gelistet werden, dann:
in C1:

=INDEX(A:A;VERGLEICH("x";B:B;))

in C2:

{=WENN(MAX(ISTNV(VERGLEICH(A$1:A$99;C$1:C1;))*(B$1:B$99="x"))=0;"";INDEX(A:A;MIN(WENN(ISTNV(VERGLEICH(A$1:A$99;C$1:C1;))*(B$1:B$99="x");ZEILE(X$1:X$99)))))}

bzw. ab Excel-2007:

{=WENNFEHLER(INDEX(A:A;MIN(WENN(ISTNV(VERGLEICH(A$1:A$99;C$1:C1;))*(B$1:B$99="x");ZEILE(X$1:X$99))));"")}

C2 runterkopieren

Erläuterung

siehe auch in der Kategorie Statistik:
in Rangliste bei gleichen Werten den richtigen Verweis liefern

Das Portal zum Thema Excel-Formeln