Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Listen/Datensätze

Beitrag: Alle Zelladressen eines Suchbegriffes in einer Matrix auflisten

Aufgabe
Alle Zelladressen eines Suchbegriffs sollen aufgelistet werden.
Der Suchbegriff kann auch nur zum Teil vorliegen.

       A               B               C               D               E               F               G               H               I       
1 Warnes  Lightfoot  Guthrie  Warnes  dylan  3  A3  1  A3  
2 Baez  McDonald  Leven  Cohen    3  C3  1  A7  
3 Dylan  Simon  Dylan  Dylan    3  D3  2  B10  
4 Leven  Collins  Leven  Felix    7  A7  3  C3  
5 Felix  Lightfoot  Cohen  Simon    10  B10  4  D3  
6 Cohen  Wader  Warnes  Shocked    #ZAHL!    #ZAHL!   
7 Dylan  Shocked  Leven  Lightfoot    #ZAHL!    #ZAHL!   
8 Guthrie  Warnes  Collins  McDonald           
9 Lightfoot  Cohen  Wader  Cohen           
10 McDonald  Dylan  Baez  Collins           
11 Wader  Simon  Felix  Wader           
12 Guthrie  Collins  Shocked  Leven           

Lösung
Der Suchbegriff steht in Zelle E1.

Bei nur einer Spalte A ist es relativ einfach:
{=WENNFEHLER("A"&KKLEINSTE(WENN(ISTZAHL(SUCHEN("*"&$E$1&"*";A$1:A$99));ZEILE(X$1:X$99));ZEILE(X1));"")}
und runterkopieren.

Bei mehreren Spalten - hier: A bis D - braucht man die Hilfsspalte ab F1 (ausblenden):
Die Zelladressen werden zeilenweise aufgelistet.
{=KKLEINSTE(WENN(ISTZAHL(SUCHEN("*"&$E$1&"*";A$1:D$99));ZEILE(X$1:X$99));ZEILE(X1))}
Das Ergebnis in G1:
{=WENNFEHLER(ADRESSE(F1;KKLEINSTE((WENN((ZEILE(X$1:X$99)=F1)*ISTZAHL(SUCHEN("*"&$E$1&"*";A$1:D$99));SPALTE(A:D)));ZÄHLENWENN(F$1:F1;F1));4);"")}
F1 und G1 runterkopieren.

Für eine spaltenweise Auflistung die Spalten H und I:
{=KKLEINSTE(WENN(ISTZAHL(SUCHEN("*"&$E$1&"*";A$1:D$99));SPALTE(A:D));ZEILE(X1))}


{=WENNFEHLER(ADRESSE(KKLEINSTE((WENN((SPALTE(A:D)=H1)*ISTZAHL(SUCHEN("*"&$E$1&"*";A$1:D$99));ZEILE(X$1:X$99)));ZÄHLENWENN(H$1:H1;H1));H1;4);"")}

Download