Listen/Datensätze (10) -> Alle Zelladressen eines Suchbegriffes in einer Matrix auflisten (450)

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


zurück