Das grosse Archiv mit 620
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   GäSTEBUCH   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
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))))}
bzw. ab Excel2007:
{=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))}

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.

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
 
    zurück


 
powered by telltarget