Verweisfunktionen (3) -> SVERWEIS bei mehrfach vorkommendem Suchkriterium - ohne Doppler * (28) |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
*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)}
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($1:$99));ZEILE(A1))))}
ab Excel-2007:
{=WENNFEHLER(INDEX(A:A;KKLEINSTE(WENN(B$1:B$99="x";ZEILE($1:$99));ZEILE(A1)));"")}
sowie:
{=KGRÖSSTE((B$1:B$99="x")*A$1:A$99;ZEILE(A1))}
bzw. =KGRÖSSTE(INDEX((B$1:B$99="x")*A$1:A$99;0);ZEILE(A1))
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($1:$99));ZEILE(A1)));"")}
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)))))}
bzw. ab Excel2007:
{=WENNFEHLER(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