Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.06.2024 19:56:24
17.06.2024 19:39:46
Anzeige
Archiv - Navigation
1804to1808
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formel Finden mehrere Suchtexte und Leer

Formel Finden mehrere Suchtexte und Leer
23.01.2021 22:54:32
MarC
Abend =)
ich habe mir eine Formel gebastelt die mir die Zeilennummer ermittelt in der mein gesuchter Kunde steht und dann den Wert aus der Spalte B ausgibt. Das funktioniert. Jetzt möchte ich aber nicht nur einen Suchbegriff angeben sondern mehrere und zusätzlich eine zweite Bedingung und zwar das die Zelle P in der Zeile in der der Kunde gefunden wurde leer ist. Leider funktioniert es nicht so wie ich es mir überlegt habe. Kann mir jemand an der Stelle weiterhelfen?
Formelausschnitt
FINDEN(AW7:AW12&"";'Kunde'!J:J &'Kunde'!P:P;1)
Komplette Formel
=WENNFEHLER(INDEX('Kunde'!B:B;AGGREGAT(15;6;ZEILE('Kunde'!J:J)/(FINDEN(AW7:AW12&""; 'Kunde'!J:J &'Kunde'!P:P;1)>0);ZEILE()-19);1);"") 

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel Finden mehrere Suchtexte und Leer
23.01.2021 23:07:32
SF
Hola,
eine Beispieldatei könnte helfen.
Gruß,
steve1da
AW: Formel Finden mehrere Suchtexte und Leer
23.01.2021 23:31:01
MarC
Hallo Steve,
ich habe eine Beispieldatei gemacht. Mit einem Suchttext funktioniert es. Mit zwei bzw. mehreren Suchtexten und einer zweiten Bedingung und zwar das die Zelle C leer ist leider nicht.
https://www.herber.de/bbs/user/143277.xlsx
AW: Formel Finden mehrere Suchtexte und Leer
23.01.2021 23:36:53
SF
Hola,
deine Beschreibung weicht von der Datei ab. Spalte P ist immer leer.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$21)/(($B$2:$B$21=$G$2)+($B$2:$B$21=$G$3));ZEILE(A1) ));"")
Die leere Spalte ist natürlich nicht berücksichtigt.
Gruß,
steve1da
Anzeige
AW: Formel Finden mehrere Suchtexte und Leer
23.01.2021 23:56:43
MarC
Danke Steve, wenn ich jetzt noch mehr Suchtexte hätte müsste ich die Formel immer um das

+($B$2:$B$21=$G$3)
erweitern oder?.
Meine Beschreibung hat sich vorher auf die Originaldatei bezogen. Bei der Beispieldatei wäre es die Spalte C sorry.
AW: Formel Finden mehrere Suchtexte und Leer
24.01.2021 00:36:10
SF
Jap.
AW: Formel Finden mehrere Suchtexte und Leer
24.01.2021 00:44:21
MarC
Damit kann ich glaube ich leben^^. Aber hast du eine Idee wie ich die Abhängigkeit von der Spalte C dazu bringe also das C leer sein muss?
AW: Formel Finden mehrere Suchtexte und Leer
24.01.2021 00:57:21
SF
Hola,
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$21)/($C$2:$C$21="")*((($B$2:$B$21=$G$2) +($B$2:$B$21=$G$3)));ZEILE(A1) ));"")
Gruß,
steve1da
Anzeige
Warum so kompliziert, ...
24.01.2021 02:45:07
Luc:-?
…Steve (& MarC)?
Mit einer pluralen MatrixFml komme ich schon mit 2 UDFs auf eine etwas kürzere Fml als diese HptFml (von der entfallenden 2. für die KundenNamen ganz zu schweigen) und somit auf nur 3 Fktseinsätze statt bei eurer Version nun insgesamt 7. Aber es geht so noch kürzer, sogar ganz ohne UDFs:
I3:J4: {=INDEX(A2:B21;VERGLEICH(G2:G3;B2:B21&C2:C21;0);{1.2})}
Das liefert sowohl ID als auch Kunde lt Bedingung und das auf einmal für beliebig viele SuchBegriffe zu gleichen Bedingungen (allerdings in der OriginalReihenfolge der SuchBegriffe!). Man muss nur Spalte G (und somit das 1.Argument von VERGLEICH) entsprd ergänzen.
Morhn, Luc :-?
Anzeige
AW: ein Nachteil der pluralen Matrixfrormel ...
24.01.2021 13:28:46
neopa
Hallo Luc,
... wird aber offenbar, wenn in der Beispieldatei z.B. auch C4 leer ist.
Die Formel die SF aufgezeigt hat, lässt sich verkürzen zu:
=WENNFEHLER(AGGREGAT(15;6;A$2:A$99/(C$2:C$99="")/((B$2:$B$99=G$2)+(B$2:B$99=G$3));ZEILE(A1));"")
Und um eine beliebige Anzahl an Suchwerten auszuwerten, würde ich folgende Matrixformel vorschlagen:
{=WENNFEHLER(AGGREGAT(15;6;A$2:A$99/FINDEN(MTRANS(G$2:INDEX(G:G;ANZAHL2(G:G)));B$2:B$99)^0/(C$2:C$99="");ZEILE(I1));"")}
Gruß Werner
.. , - ...
Danke Werner, Luc und SF
24.01.2021 13:47:11
MarC
Guten Morgen Jungs,
oder eher Mahlzeit. Vielen Dank für eure Hilfe, aber jetzt habt ihr mich mit den Formeln total abgehängt, aber ich bin euch sehr dankbar das ihr mir geholft habt =)
Die Formeln funktionieren wunderbar. Die Funktion MTRANS kannte ich noch nicht. Euch noch einen guten Sonntag ;-)
Anzeige
Finde den Fehler nicht =/
24.01.2021 16:18:27
MarC
Hallo Werner,
ich bin es nochmal. Ich habe die Formel in die original Datei kopiert und die Pfade angepasst, aber leider erhalte ich kein Ergebnis mehr. Kann es sein das die Formel nicht mehr geht wenn sich die Daten auf anderen Tabellenblättern und an anderen Positionen befinden? Könntest du mir bitte helfen?
https://www.herber.de/bbs/user/143287.xlsx
AW: da sind mehrere ...
24.01.2021 16:50:42
neopa
Hallo MarC,
... zunächst ist festzustellen, dass die auszuwertenden Bereiche nicht richtig angepasst sind, der Zähler (=ZEILE(...)) muss natürlich bei 1 beginnen und grundsätzlich sollten Matrixformel nicht über den gesamten Zeilenbereich definiert werden (bei der INDEX()-Teilformel ist das dagegen möglich).
Die Formel in G47 lautet also z.B. so:
=WENNFEHLER(AGGREGAT(15;6;Daten!B$10:B$99/FINDEN(MTRANS(DB!AU$7:INDEX(DB!AU:AU;ANZAHL2(DB!AU:AU)+ZEILE(DB!AU$5)));Daten!C$10:C$99)^0/(Daten!D$10:D$99="");ZEILE(G1));"")
Gruß Werner
.. , - ...
Anzeige
AW: da sind mehrere ...
24.01.2021 17:44:40
MarC
Oh mei jetzt habe ich nach ewigen hin und her den Fehler gefunden. In der Spalte Daten!B$10:B$99 stehen keine Zahlen sondern ein Text der aus Usernamen und Datum besteht z.B. NDE, 2211-1082020233618. Ich wusste nicht das man hier nur Zahlen verwenden darf im Tutorial wurde darauf nicht hingewesen. Hätte ich das gewusste hätte ich keine Beispieldatei mit Zahlen in der Spalte B sondern mit Text hochgeladen sorry.
Den Inhalt in der Spalte B darf ich leider nicht ändern. Gibt es dennoch eine Möglichkeit das die Formel funktioniert aber mit Text?
Grüße Marc
AW: dann doch mit INDEX() ...
24.01.2021 17:53:26
neopa
Hallo Marc,
... (ungeprüft) wie folgt:
=WENNFEHLER(INDEX(Daten!B:B(AGGREGAT(15;6;ZEILE(Daten!B$10:B$99)/...
Gruß Werner
.. , - ...
Anzeige
AW: dann doch mit INDEX() ...
24.01.2021 18:36:16
MarC
Hi Werner,
ich bekomm jetzt die Meldung das zu wenig Argumente vorhanden sind. Alles was nach dem Index kommt wird als Matrix angesehen.
https://www.herber.de/bbs/user/143292.xlsx
Gruß Marc
AW: offensichtlich ist, ...
24.01.2021 19:26:42
neopa
Hallo Marc,
... dass Du meinen Formeländerungsvorschlag nicht richtig umgesetzt hast. Du hast nicht nur WENNFEHLER() weggelassen, (was nur dazu führt, dass ab einer bestimmten Zeile Fehlerwerte angezeigt werden, wenn wenigstens nachfolgendes beachtet wird), wesentlicher ist aber, dass Du auch die notwendige "Klammerung" von Daten!B$10:B$99 mit ZEILE() nicht realisiert hast, obwohl ich diese extra fett markiert dargestellt hatte. Wenn Du das so realisierst wie angegeben, solltest Du auch zu den angestrebten Ergebnissen gelangen.
Gruß Werner
.. , - ...
Anzeige
Läuft ;-)
24.01.2021 19:52:17
MarC
Danke danke danke, du hattest zu 99% recht =) Ich habe den Wennfehler nur temporär rausgenommen damit die formel etwas kürzer und übersichtlicher ist für mich. Das mit der Zeile() habe ich nicht gesehn. Du hast es kenntlich gemacht aber für mich hat es genauso ausgesehen sorry. Was noch gefehlt hat war ein ; vor der dritten Klammer. Aber supi jetzt geht es auch in der echten Datei schön.
VG MarC
AW: ... Hauptsache nicht weg ;-) owT
24.01.2021 19:59:43
neopa
Gruß Werner
.. , - ...

324 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige