Frage zur Anwendung SVERWEIS und FINDEN

[MikeMax]

Zunächst möchte ich mich für die Aufnahme in dieses Form bedanken! Vor über 15 Jahren war ich in Excel sehr fit und habe selbst vielen Leuten helfen können, aber seit dem habe ich es kaum mehr gebraucht... bis jetzt... und jetzt brauche ich Hilfe!

Siehe kleine Arbeitsmappe anbei: Ich habe eine Tabelle mit längeren Zeichenketten. In einer Hilfstabelle stehen eine Anzahl Worte (Substrings), die in den langen Zeichenketten vorkommen können. Wenn sie das tun, will ich in der Haupttabelle neben dem langen Sting ein bestimmtes anderes Wort anzeigen, das auch in der Hilfstabelle steht (abhängig davon, welches Wort gefunden wurde).

Zur Beispiel-Arbeitsmappe: Sie besteht aus 2 ganz einfachen Tabellen, selbsterklärend. Nur für die 3 gelb hinterlegten Zellen suche ich eine Formel, die das rot dargestellte Ergebnis selbständig einträgt.

Wer kann helfen??

Vielen Dank!   :19:

[BoskoBiati]

Hallo,

so:


=WENN(SUMMENPRODUKT(ISTZAHL(SUCHEN(Hilfstabelle!$A$2:$A$8;Tabelle1!A2))*1)=0;"nicht da";INDEX(Hilfstabelle!$B$2:$B$8;SUMMENPRODUKT(ISTZAHL(SUCHEN(Hilfstabelle!$A$2:$A$8;Tabelle1!A2))*ZEILE($A$1:$A$7))))

[Jockel]

Hallo, ich würde so tun..:

Arbeitsblatt mit dem Namen 'Hilfstabelle'
	A	B	C	D	E	F	G
1	Suche-Substring	Ergebnis für Ausgabe			Langer String	Ausgabe
2	Tisch	Einrichtung			.. bla Hund Katze Maus bla...	Nagetier	Nagetier
3	Stift	Schreibwaren			... bla Rot Grün Blau bla ...	Farbe	Farbe
4	Auto	Fahrzeug			... bla kommt nicht vor bla ...	(leer)
5	Grün	Farbe
6	Tasse	Geschirr
7	Maus	Nagetier
8	Schraubendreher	Werkzeug

Zelle	Formel
G2	=WENN(SUMMENPRODUKT(ISTZAHL(SUCHEN($A$1:$A$8;E2))*ZEILE($A$1:$A$8));INDEX($B$1:$B$8;SUMMENPRODUKT(ISTZAHL(SUCHEN($A$1:$A$8;E2))*ZEILE($A$1:$A$8)));"")

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[MikeMax]

Jockel und BoskoBiati: Beide Antworten funktionieren prächtig! VIELEN, VIELEN DANK!!!  :100:

Mike

[BoskoBiati]

Hallo,

sind auch fast gleich!

[Peter]

Hallo,

nur mit den Beispielswerten getestet:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Langer String	Ausgabe
2	.. bla Hund Katze Maus bla...	Nagetier	Nagetier
3	... bla Rot Grün Blau bla ...	Farbe	Farbe
4	... bla kommt nicht vor bla ...	(leer)	(leer)

Zelle	Formel
C2	{=WENNFEHLER(INDEX(Hilfstabelle!$B$1:$B$8;VERGLEICH(1;VERGLEICH("*"&Hilfstabelle!$A$1:$A$8&"*";A2;0);0));"(leer)")}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Ob deine Mac-Version Wennfehler kennt kann ich nicht beurteilen. Das müsstest du ausprobieren.

Es handelt sich hier um eine Matrixformel - das könnte bei größeren Bereichen auf die Performance schlagen.

[MikeMax]

Nun habe ich doch noch ein Problem: Die Länge der beiden (!) Tabellen muss variabel sein! Die Lösungen von Jockel und BoskoBiati gehen von Tabellen mit einem festen Umfang aus. Aber ich muss bei beiden Tabellen beliebig Zeilen löschen und hinzufügen können ohne die Formel zu ändern...

Peter: Vielen Dank! Bei mir auf dem Mac kommt zwar keine Fehlermeldung, aber die Formel gibt immer "(leer)" aus, auch in den Zeilen, in denen "Nagetier" oder "Farbe" ausgegeben werden sollte.

Meine Anwendung wird dann eine Tabelle1 mit zigtausend Zeilen haben, während die Hilfstabelle nur etwa 30 Zeilen haben wird. Insofern ist die Performance nicht ganz unwichtig.

Hintergrund: Es ist eine Art Kassenbuch, bei dem Anhand von Stichworten in einem längeren Buchungstext automatisch eine Kategorie für die jeweilige Buchung vorgegeben werden soll. Und die Liste dieser Stichworte mit zugehörigen Kategorien steht eben in der Hilfstabelle.

Nochmal Danke für die Mühe!   :19:

[shift-del]

Nun habe ich doch noch ein Problem: Die Länge der beiden (!) Tabellen muss variabel sein! Die Lösungen von Jockel und BoskoBiati gehen von Tabellen mit einem festen Umfang aus. Aber ich muss bei beiden Tabellen beliebig Zeilen löschen und hinzufügen können ohne die Formel zu ändern...

Dafür hat Microsoft die formatierten Tabellen erfunden (STRG-T). In Formeln werden dann nicht mehr Zellbezüge verwendet sondern strukturierte Verweise.

[MikeMax]

Dafür hat Microsoft die formatierten Tabellen erfunden (STRG-T). In Formeln werden dann nicht mehr Zellbezüge verwendet sondern strukturierte Verweise.

Es tut mir sehr leid, das habe ich gerade jetzt erst gelernt. Und umgesetzt so gut ich kann (siehe neue Datei anbei).

Aber es funktioniert noch immer nicht: Sobald ich am Ende der Hilfstabelle eine neue Zeile anfüge, zeigen alle Formeln in Tabelle1 nur noch "#NV".

Warum? Was mache ich falsch?

Nochmal DANKE!

Mike

[shift-del]

Hallo Mike

Ich habe aus der Tabelle in Tabellenblatt "Tabelle1" auch in eine formatierte Tabelle gemacht und eine andere Formel verwendet.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Langer String	Ausgabe SOLL	Formel
2	bla Maus bla	Nagetier	Nagetier
3	bla Grün bla	Farbe	Farbe
4	kommt nicht vor	(leer)

Zelle	Formel
C2	=WENNFEHLER(VERWEIS(42;1/SUCHEN(Tabelle1[Suche-Substring];[@[Langer String]]);Tabelle1[Ergebnis für Ausgabe]);"")

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg