Excelformeln - FORMELN - Übersicht - Verweisfunktionen (3) - SVERWEIS bei mehrfach vorkommendem Suchkriterium

Das grosse Archiv mit 582
VBA-freien Formellösungen und Tips

Verweisfunktionen (3) ->

SVERWEIS bei mehrfach vorkommendem Suchkriterium - ohne Doppler * (28)

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($1:$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)}

{=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)}
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($1:$99));ZEILE(A1))))}

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

In Formel 1 wurde noch der Teil mit ZÄHLENWENN 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.

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($1:$99)))))}
C2 runterkopieren

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

zurück