Nach bestimmten Wörtern suchen

[mikeleb]

Hallo,

das Problemist nicht so ganz ohen, aber mir einer Matrixformel lösbar.
Gib in C1 folgende Formel ein:

Code: Alles auswählen

=VERSCHIEBUNG($Tabelle2.$B$1;MIN(WENN(ISTFEHLER(SUCHEN($Tabelle2.$A$1:$A$5;A1));100;ZEILE($Tabelle2.$A$1:$A$5)))-1;0)

und schließe sie mit Shift+Strg+Enter ab.

[mikeleb]

Hallo,
zur Erklärung: Der Code-Teil

Code: Alles auswählen

WENN(ISTFEHLER(SUCHEN($Tabelle2.$A$1:$A$5;A1));100;ZEILE($Tabelle2.$A$1:$A$5))

durchforstet den Bereich $Tabelle2.$A$1:$A$5 auf das Vorhandensein des Textes aus A1. Dabei wird die Prüfung für jede Zelle des Bereiches durchgeführt und liefert entweder die Zeile (in der der Text gefunden wurde) oder 100 (als Dummy-Zeile). Anschließend wird das Minimum aller Werte genommen. Dabei können zwei Fälle auftreten
a) der Text wurde in einer Zelle (z. B. in $Tabelle2.$A$2) gefunden, dann liefert das Minimum den Wert 2
b) der Text wurde nicht gefunden, dann liefert das Minimum den Wert 100
Anschließend wird der Focus von $Tabelle2.$B$1 um entsptrechend viele Zeilen nach unten verschoben (bis zum gewünschten Eintrag oder eben bis zur Zelle B100, die leer sein sollte) und der dortige Wert angezeigt.
Leider wird bei diesem Verfahren anstatt einer leeren Zelle der Wert 0 angezeigt. Das lässt sich umgehen, indem du
a) in die Zelle $Tabelle2.$B$100 ein Leerzeichen setzt
b) Nullwerte nicht anzeigen lässt
c) den Formatcode anpasst: z. B. so Standard;;;@

[lorbass]

@mikeleb, chapeau!

liefert entweder die Zeile (in der der Text gefunden wurde) oder 100 (als Dummy-Zeile)

Ich hätte da noch 'nen kleinen Verbesserungsvorschlag anzubringen. Wenn in mikelebs Formel

1. im Dialog Namen verwalten für den Zellbereich

   Code: Alles auswählen

   $Tabelle2.$A$1:$A$9999

   z.B. der Name Kategorien vergeben wird und
    
2. im Dialog Namen verwalten für den Term

   Code: Alles auswählen

   MAX(NICHT(ISTLEER(Kategorien))*ZEILE(Kategorien))

   z.B. der Name MaxKategorie angelegt wird und
    
3. die Konstante »100« durch den Namen MaxKategorie ersetzt wird,

erhält man die Matrix-Formel (!)

Code: Alles auswählen

{=VERSCHIEBUNG($Tabelle2.$B$2;MIN(WENN(ISTFEHLER(SUCHEN(Kategorien;A2));MaxKategorie+1;ZEILE(Kategorien)))-1;0)}

Vorteile:

1. Durch die Verwendung „sprechender“ Namen wird die Formel leichter lesbar.
2. Bei wachsender Tabelle muss der zu verwendende Zellbereich an nur einer einzigen Stelle, nämlich im Dialog Namen verwalten in der Definition des Zellbereichs Kategorien angepasst werden. Die in der Tabelle1 eingesetzten Formeln müssen nicht geändert werden!

Beides ist im Interesse der zukünftigen Pflege der Anwendung und dürfte diese spätestens dann erheblich erleichtern, wenn die Details der Lösung längst vergessen sind.

Gruß
lorbass

[ChristianAC]

bei mir zeigt der gar nichts an,
was mache ich falsch??

[mikeleb]

Hallo,

da deine Stichworttabelle in Zeile 9 beginnt, muss die Verschiebung korrigiert werden.
Anstelle "-1" nun "-9" (fast am Ende der Formel)

Code: Alles auswählen

=VERSCHIEBUNG($Tabelle1.$B$9;MIN(WENN(ISTFEHLER(SUCHEN($Tabelle1.$A$9:$A$13;$Tabelle1.A1));100;ZEILE($Tabelle1.$A$9:$A$13)))-9;0)

Noch eine Anmerkung: Ausgangspunkt für die Verschiebung ist die Zelle B9 und nicht der ganze Bereich. DAher reicht

Code: Alles auswählen

=VERSCHIEBUNG($Tabelle1.$B$9;

[ChristianAC]

Ah a danke,alles klar