KGrößte in verschachtelter Formel (Wildcard)

[Ronn]

Hallo zusammen,

ich stehe vor einem Problem welches ich irgendwie nicht lösen kann. Gegeben ist folgende Formel:

Code:

=WENNFEHLER(INDEX(Testreiter!$J$2:$J$1000;KGRÖSSTE((Testreiter!$J$2:$J$1000=$A$1)*(ZEILE(Testreiter!$J$2:$J$1000)-1);ZÄHLENWENN(Testreiter!$J$2:$J$1000;$A$1)+1-ZEILE(A1)));"")

Diese funktioniert wunderbar. Suche ich nach einem genauen Wort, wird dieses dann auch schön untereinander aufgelistet (die entsprechenden Treffer). Nun zu meinem Problem. Ich bekomme es nicht hin, die Formel "Wildcard"-fähig zu basteln. 

Bsp: 
Ich möchte in A1 (Bezug in der Formel: $A$1), oder auch in der Formel selbst, gerne das Wort "Nudel*" suchen. Damit sollen die Treffer "Nudeln" und "Nudelauflauf" und Zellen die irgendwas mit Nudel* beinhalten, gefunden werden. 

Frage:
wie setze ich das um? wo ist mein Denkfehler bzw. was muss ich tun, um die Formel wie vorgesehen umzubiegen?

Besten Dank für eure Tipps und Tricks :)

Viele Grüße

[{Boris}]

Hi,

direkte Zellvergleiche

(Testreiter!$J$2:$J$1000=$A$1)

sind nicht Wildcard-fähig. Das kannst Du z.B. mit - in Deinem Beispiel - LINKS in Abhängigkeit der Länge des Suchbegriffs lösen:

(LINKS(Testreiter!$J$2:$J$1000;LÄNGE($A$1))=$A$1)

[steve1da]

Hola,

Code:

=WENNFEHLER(INDEX(Testreiter!$J$2:$J$1000;KGRÖSSTE((ISTZAHL(SUCHEN($A$1;Testreiter!$J$2:$J$1000)))*(ZEILE(Testreiter!$J$2:$J$1000)-1);ZÄHLENWENN(Testreiter!$J$2:$J$1000;"*"&$A$1&"*")+1-ZEILE(A1)));"")

Gruß,
steve1da

[BoskoBiati]

Hi,

was spricht gegen AGGREGAT:

Code:

=WENNFEHLER(INDEX(J:J;AGGREGAT(15;6;ZEILE($J$2:$J$10)/(SUCHEN($A$1;$J$2:$J$10));ZEILE(A1)));"")

[{Boris}]

Hi Edgar,

Code:

Was spricht gegen AGGREGAT:
=WENNFEHLER(INDEX(J:J;AGGREGAT(15;6;ZEILE($J$2:$J$10)/(SUCHEN($A$1;$J$2:$J$10));ZEILE(A1)));"")

Im Prinzip alles 
a) die Division 1 geteilt durch das reine SUCHEN-Array (also ohne ISTZAHL) liefert krumme Dinger, da die SUCHEN-Ergebnisse ja auch mitten im Text oder am Ende stehen können.
b) Unter bestimmten Umständen spillt die Formel in xl365 bis Zeile 1.048.576 - siehe Bild (dort nur bis Zeile 10 ersichtlich - aber es geht in der Tat bis zum Ende)

   

[BoskoBiati]

Hi Boris,

a) sehe ich nicht als Problem, aber Du kannst mich ja eines Besseren belehren.
b) ist bei der Matrixformel von Steve1da auch nicht besser.

Außerdem ginge auch das:

Code:

=WENNFEHLER(INDEX(J:J;AGGREGAT(15;6;ZEILE($J$2:$J$10)/(SUCHEN($A$1;$J$2:$J$10)<>0);ZEILE(A1)));"")

[Ronn]

Hallo zusammen,

wow, Wahnsinn. Bin überwältigt vom super schnellen Feedback. Es hat funktioniert. Ich hab die Variante von Steve genommen und es klappt super. Danke für euren blitzschnellen support. Ich wünsche allen einen super Start in das Wochenende.

Beste Grüße

[{Boris}]

Hi Edgar,

ich hatte mich nur exakt auf die Formel bezogen, die Du zuerst gepostet hattest.

Code:

a) sehe ich nicht als Problem, aber Du kannst mich ja eines Besseren belehren.

In meinem Bildbeispiel liefert AGGREGAT im Ergebnis 0,33333, was sich aus der Auswertung 

=AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10}/{6;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!};ZEILE(A1))
=AGGREGAT(15;6;{0,333333333333333;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!;#WERT!};ZEILE(A1))
={0,333333333333333}

ergibt.

Und 0,333333333333 - an INDEX übergeben - wird zu INDEX(J:J;0) - und damit entsteht halt der Millionen-Spill ;-=)

Code:

b) ist bei der Matrixformel von Steve1da auch nicht besser.

Doch, da dort keine "krummen Dinger" durch Verwendung von ISTZAHL (hatte ich ja auch geschrieben) entstehen - somit wird auch keinesfalls der Zeilenindex 0 für INDEX entstehen und es gibt kein Spill-Thema.

In Deiner Formel reicht ja das dann von Dir anschließ0end ergänzte <>0 - ist dann faktisch das Selbe wie ISTZAHL. Diese Lösung würde ich dann ja auch so favorisieren

Wollte halt nur auf Deine erste Formel eingehen

[BoskoBiati]

Hi Boris,

überzeugt.