Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1784to1788
1784to1788
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

XVERWEIS / SVERWEIS / SUCHEN

Forumthread: XVERWEIS / SVERWEIS / SUCHEN

XVERWEIS / SVERWEIS / SUCHEN
29.09.2020 13:14:36
Ella
Hallo Zusammen,
ich bräuchte mal wieder Hilfe. Ich würde gerne per XVERWEIS zwei Infos verknüpfen. Allerdings sind die Informationen Suchkrieterium und Suchmatrix nicht exakt identisch.
Siehe Datei für meine Überlegungen: https://www.herber.de/bbs/user/140514.xlsx
Kann jemand helfen?
Vielen Dank schonmal und LG
Anzeige

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: z.B. mit AGGREGAT() von SUCHEN() ...
29.09.2020 13:49:03
SUCHEN()
Hallo Ella,
... in B2: =WENNFEHLER(AGGREGAT(15;6;B!B$2:B$25/SUCHEN(B!A$2:A$25;A2)^0;1);"")
und nach unten kopieren.
Gruß Werner
.. , - ...
AW: z.B. mit AGGREGAT() von SUCHEN() ...
29.09.2020 14:00:50
SUCHEN()
Super, vielen Dank Werner! Ich probiere es gleich mal aus.
Kannst Du mir bei Gelegenheit noch erklären, was geanu hier gemacht wird? Ich würde es gern verstehen.
Anzeige
AW: z.B. mit AGGREGAT() von SUCHEN() ...
29.09.2020 14:17:15
SUCHEN()
Hallo Ella,
... mit der Teilformel SUCHEN(B!A$2:A$25;A2) wird nicht A2 in B!A$2:A$25 gesucht sondern die Matrix B!A$2:A$25 sucht in A2 Übereinstimmung. Dessen Ergebnis ist eine Matrix aus Fehlerwerten und evtl. einem Zahlenwert. Im vorliegenden Beispiel immer 1 könnte aber auch mal größer 1 sein. Damit aber der Divisor auch dann eine 1 ergibt potenziere ich dies mit 0.
Da wo der Divisor der Matrix eine 1 ist, ergibt sich für den Quotienten die Zahl aus der Matrix B!B$2:B$25 für alle anderen natürlich auch einen Fehlerwert. Fehlerwerte werden durch das 2. Argument der AGGREGAT()-Funktion in Formel ignoriert, so dass damit der zutreffende Wert gefunden wird, egal ob das 1. Argument der Funktion eine 15 wie verwendet oder eine 14 ist.
Gruß Werner
.. , - ...
Anzeige
AW: z.B. mit AGGREGAT() von SUCHEN() ...
29.09.2020 14:27:17
SUCHEN()
Wow, mega! Danke dir! Ich werde es Schritt für Schritt nachbauen, um es besser nachvollziehen zu können. Aber es funktioniert auf jeden Fal.
AW: bitteschön, gerne owT
29.09.2020 14:38:17
neopa
Gruß Werner
.. , - ...
8631 Natural 1 A ist Nr. 1240 - nicht 1239
29.09.2020 15:23:35
WF
also:
... AGGREGAT(14;6; ...
WF
Anzeige
man muss nach ZEICHEN(10) suchen
29.09.2020 16:17:25
WF
Hi,
folgende Arrayformel in B2:
{=WENNFEHLER(INDEX(B!B:B;VERGLEICH(LINKS(A2;FINDEN(ZEICHEN(10);A2&ZEICHEN(10))-1);TEXT(B!A$1:A$25; "@");0));"") }
runterkopieren
WF
AW: auch das reicht nicht, denn ...
29.09.2020 16:29:16
neopa
Hallo WF,
... hab mir jetzt erst die Daten näher angeschaut als vorhin.
=AGGREGAT(14,6; würde für die vorliegenden Daten mit Leerzeichen ausreichend sein. Aber die zugeordneten Werte könnten ja auch einmal kleiner sein. Auch die Zeile abzufragen reicht nicht, weil die Daten ja auch anders angeordnet sein können.
Der Zeilenumbruch allein reicht nicht, weil ja in A19:A20 teilweise nur Trennung durch Leerzeichen vorliegen. Dies könnte man zwar durch WECHSELN(A19;" ";ZEICHEN(010)) abfangen, aber ...
Es gibt dann noch die gleichen Werte in A!A13:A14 mit unterschiedlichen Werten in A!B13:B14
Das kann jetzt erst einmal nur Ella klären.
Gruß Werner
.. , - ...
Anzeige
AW: auch das reicht nicht, denn ...
29.09.2020 17:48:50
Ella
Stimmt, das Formelergebnis war nicht immer richtig.
Du hast Recht, Werner. Im Beispieldokument ist es tatsächlich falsch. Wenn ein Wert in Spalte A mehrfach vorkommt (identische Werte), dann wird er auch immer die gleiche NR (Spalte B / Blatt B) haben. Und ja, es kommt per Leerzeichen oder Umbruch getrennt sind.
Vielen Dank für Eure Mithilfe :)
LG Ella
Anzeige
AW: auch das reicht nicht, denn ...
29.09.2020 18:03:49
Daniel
wobei man beachten sollte, dass wenn der Wert am Ende steht, in der Regel kein Umbruch oder Leerzeichen folgt, was dann u.U. zu Problemen führt oder man muss diese innerhalb der Funktion hinzufügen.
der andere Weg ist dass man über die Sortierung der Liste abgestimmt mit der verwendeten Aggregat-Funktion (14 oder 15) dafür sorgt, dass der richitige Wert ausgegeben wird.
Gruß Daniel
Anzeige
AW: hierzu noch ergänzt ...
30.09.2020 14:21:58
neopa
Hallo Ella,
... aus Deiner Beispieldatei ist ersichtlich, dass Du scheinbar immer nur den ersten Wert einer Doppelwertzelle in A!A:A auswerten willst/musst.
Für diesen Fall würde ich dann folgende Formel vorschlagen:
=WENNFEHLER(INDEX(B!B:B;VERGLEICH(LINKS(A2;SUCHEN(ZEICHEN(10);WECHSELN(A2;" ";ZEICHEN(10))&ZEICHEN(10))-1);INDEX(B!A$1:A$99&"";);));"")

und nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: Datenklärung notwendig ...
29.09.2020 16:36:19
neopa
Hallo Ella,
... WF hat einen berechtigten Einwand gebracht, das AGGREGAT(15; ... für Deinen unzureichend ist.
Nachdem ich mir jetzt Deine Daten näher angeschaut habe bin ich auf Deine identischen Werte in A!A13:A14 gestoßen, denen unterschiedliche Werte in A!B13:B14 zugewiesen sind.
Wenn das geklärt ist, müsstest Du noch aussagen, ob Deine Daten in A!A2:B25 nach Spalte A aufwärts sortiert werden können. In Deiner XL-Version (offensichtlich hast Du Office365) gibt es zwar SORTIEREN(), die steht mir aber in XL2016 nicht zur Verfügung.
Gruß Werner
.. , - ...
Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige