Office 2010: SVERWEIS mit mehreren Ergebnissen, wobei Suchkriterium irgendwo in der Zelle

[excelmaniac]

Hallo zusammen,

ich möchte einen SVERWEIS erstellen, der mir alle Ergebnisse zu einem Suchkriterium liefert, wobei das Suchkriterium sich jedoch irgendwo in den zu durchsuchenden Zellen befindet (also an beliebiger Stelle in der Zelle).. im konkreten Fall können sich mehrere Semikolongetrennte Werte in den Zellen befinden, wo genau darunter das Suchkriterium befindet, kann in jeder der Zellen, wo es vorkommt, anders sein. Also mal ist nur der zu suchende Wert darin (also am Anfang der Zelle), mal befindet er sich jedoch irgendwo unter anderen Werten.

Mit einer Matrix-Formel hab ich das für eine genaues Suchkriterium schon mal hinbekommen, aber mit ungenauer Position in der jeweiligen Zelle weiß ich nicht wie ich sowas umsetzen kann.

Wäre super wenn Ihr mir da weiterhelfen könnt...

Vielen Dank im Voraus und beste Grüße,
Marc

[steve1da]

Hola,

das Suchkriterium kann auch mit Sternchen arbeiten:

Code: [Auswählen]

=sverweis("*"&A1&"*";........)
Gruß,
steve1da

[excelmaniac]

Hallo,

vielen Dank für die Antwort, die mir schon etwas weiterhilft.. dies würde aber nur den ersten Treffer liefern, oder?

Ich benötige ja eine Liste (Zeilenweise) mit allen Treffern (das Suchkriterium kommt mehrfach (jeweils irgendwo in einer Zelle) vor. Soweit ich weiß geht das nur über eine Matrix-Formel. Wie würde das aussehen?

Viele Grüße,
Marc

[steve1da]

Hola,

Wie würde das aussehen?

Gegenfrage: wie sieht deine Tabelle aus?
Eine Beispieldatei würde helfen.

Gruß,
steve1da

[excelmaniac]

Hallo,

das sähe in etwa so aus:

A  B
1  a;
2  b;
3  a;b;c;
4  b;c;
5  c;
6  a;c

Das Suchkriterium steht in Spalte B, die Rückgabewerte in Spalte A.

Gesucht werden soll z.B. "b".. das kommt in B2,B3,B4 vor. Das Semikolon hängt da immer hinten als festes Trennzeichen dran.

In Spalte C sollen dann Zeile für Zeile alle Treffer mit "b" untereinander ausgegeben werden, in diesem Fall die Werte 2,3,4 aus den Zellen A2,A3,A4.

Mit einer Matrixformel funktioniert es für eindeutige Werte (mir würde also nur etwas ausgeben werden, wenn ausschließlich "b;" gefunden wird), die "gemischten" Zellen erkennt die Matrixformel leider nicht und das ist das Problem was ich bislang nicht lösen kann.

Viele Grüße,
Marc

[steve1da]

Hola,

folgende Matrixformel:

Code: [Auswählen]

=WENNFEHLER(INDEX($A$1:$A$99;KKLEINSTE(WENN(ISTZAHL(SUCHEN("b";$B$1:$B$99));ZEILE($1:$99));ZEILE(A1)));"")
Gruß,
steve1da

[excelmaniac]

Super! Hat funktioniert!! 

Muchos gracias!

Viele Grüße,
Marc

[steve1da]

Hola,

der Übersicht halber, die Aggregat-Variante:

Code: [Auswählen]

=WENNFEHLER(INDEX($A$1:$A$99;AGGREGAT(15;6;ZEILE($1:$99)/(ISTZAHL(SUCHEN("b";$B$1:$B$99)));ZEILE()));"")
Gruß,
steve1da

[excelmaniac]

Vielen Dank

Da ich mit Matrix-Formeln noch nicht so lange arbeite würde mich interessieren, ob die Aggregat-Variante denn bestimmte Vorteile hat bzw. wann man welche einsetzen würde..

Viele Grüße,
Marc

[steve1da]

Hola,

die Aggregat Variante hat Performance Vorteile gegenüber der Matrixformel.

Einsetzen kann man sie erst ab Excel 2010, dann aber so oft es geht ;-)

Gruß,
steve1da

[excelmaniac]

Das hab ich bei der Matrixformel gemerkt.. brauchte ein bisschen was bis die Berechnung durch war

Werde ich auch nochmal mit der Aggregat-Variante versuchen...

Kannst Du mir die Gesamtformel eventuell etwas erläutern? Ist so verschachtelt dass ich da noch nicht so ganz durchsteige muss ich gestehen

[steve1da]

Hola,

welche der beiden Formeln meinst du denn jetzt genau?

Im Endeffekt suchen beide Formeln nach einem "b" in Spalte B, und zwar im Bereich B1:B99. Wenn es gefunden wird, wird der erste Wert aus Spalte A wiedergegeben, wegen Zeile(A1). Beim runterziehen wird daraus Zeile(A2), was einer 2 entspricht, es wird also der 2 .gefundene Wert wiedergegeben, sofern vorhanden.

Gruß,
steve1da

[excelmaniac]

Okay das verstehe ich aber den Aufbau des Aggregats-Formel-Teils leider noch nicht so ganz..

[steve1da]

Hola,

so wird diese Formel nunmal aufgebaut, wenn mehrere Ergebnisse zu einem Suchkriterium zurückgegeben werden sollen.

Edit: siehe auch: http://thehosblog.com/2013/05/08/excel-mehrere-suchkriterien-mehrere-treffer-aggregat/
Gruß,
steve1da

[excelmaniac]

Hallo,

besten Dank.. schau ich mir gleich mal an

Viele Grüße,
Marc