Suchen Sie nach mehreren Werten in einer anderen Tabelle, wenn Sie eine Zelle mit mehreren durch Trennzeichen getrennten Werten verwenden

430
Lucio

Ich habe zwei Tabellen, die folgendermaßen aussehen:

Suchen Sie nach mehreren Werten in einer anderen Tabelle, wenn Sie eine Zelle mit mehreren durch Trennzeichen getrennten Werten verwenden

Ich möchte, dass Excel den Wert in ID company associatedTabelle 2 (grün) verwendet, danach in Tabelle 1 (orange) sucht und den Wert von Company nameoder mehrere Werte zurückgibt, wenn mehrere Unternehmen derselben Person in Tabelle 2 zugeordnet sind.

Was wäre die beste Lösung?

1

1 Antwort auf die Frage

5
robinCTS

Hier ist eine Formel, die auch in Excel 2016 funktioniert. In früheren Versionen von Excel ist eine UDF mit Poly-Fill TEXTJOIN()erforderlich. (In diesem Beitrag finden Sie einen grundlegenden Beitrag .)


Arbeitsblatt-Screenshot

Geben Sie ( Ctrl+ Shift+ Enter) die folgende Formel ein F2und fügen Sie sie in den Rest der Spalte ein:

{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--MID(SUBSTITUTE(E2,";",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1)))=1),99),A:A,0)))))}

Beachten Sie, dass diese Formel nur funktioniert, wenn die Werte in der Spalte Atatsächlich als Zahlen gespeichert sind. Bei Textwerten muss die --MID(…)in der Formel durch ersetzt werden TRIM(MID(…)).

Die verettete Formel lautet wie folgt:

{= TEXTJOIN( ";", TRUE, INDEX( (B:B), N(IF(1, MATCH( --MID( SUBSTITUTE(E2,";",REPT(" ",99)), 99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1) +(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))), 99 ), (A:A), 0 ) )) ) )}

Anmerkungen:

  • Die verettete Formel funktioniert tatsächlich, wenn sie eingegeben wird.
  • Die Klammern (A:A)in der veretteten Version müssen zwingen A:A, dass sie auf ihrer eigenen Linie bleiben. Gleiches gilt für die (B:B).


Für Excel 2016 (nur Windows) sollte die folgende einfachere Formel funktionieren:

{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))))}

Wie die vorige Formel funktioniert auch diese nur mit Werten, die als Zahlen gespeichert sind. Entfernen Sie für Textwerte einfach die --aus der Formel.

Hallo RobinCTS, danke für die Antwort, es funktioniert enorm. Eine letzte Frage, die sich auf die oben genannten Fragen bezieht: Wie kann ich ein Trennzeichen zwischen Namen hinzufügen, beispielsweise ein Semikolon zwischen Firmennamen? Sehr geschätzt Lucio vor 6 Jahren 0
@ Lucio Umm, das tut es schon. Dafür gibt es das erste ";" in der Formel. Sie können es auch in eine beliebige andere Trennzeichenfolge ändern. PS Wenn Sie meine Hilfe wirklich zu schätzen gewusst haben, sollten Sie, wenn Sie mehr als 15 Jahre alt sind, nicht vergessen, meine Antwort zu bestätigen. robinCTS vor 6 Jahren 0
Auf den ersten Blick scheint es, als würde ich nur einen Wert für jede Zelle in Spalte F erhalten (1 Firmenname pro Zelle in Spalte F). Ich erwartete mindestens ein paar Zellen mit mehreren Werten, die durch ein Semikolon voneinander getrennt waren. Es ist möglich, aber angesichts des Datensatzes, mit dem ich arbeite, sehr unwahrscheinlich. Ich werde die Akte gründlich untersuchen und mich mit Ihnen in Verbindung setzen. PS: Wird es tun Lucio vor 6 Jahren 0
@ Lucio Sie müssen die Formel mit `Strg` + Shift` + Enter eingeben, statt nur Enter. (Deshalb sind die Zahnspangen da - um Sie daran zu erinnern ;-)) PS Es ist nicht die Ursache Ihres Problems, aber als Referenz habe ich der Antwort Informationen hinzugefügt, die sich auf Zahlen beziehen, die als Text gespeichert sind, und als tatsächlich gespeicherte Zahlen Zahlen PPS Ich habe auch die nicht versteinerte erste Formel etwas vereinfacht. (Die abgespeckte Version war bereits die einfachere Version.) robinCTS vor 6 Jahren 0
100% gearbeitet. Danke für die Klarstellung. Lucio vor 6 Jahren 0
Gibt es eine Möglichkeit, dass diese Formel einen in Spalte E befindlichen Textwert zurückgeben kann? Wenn die Spalte E eine Mischung aus Zahlen und Textwerten enthält, kann die Formel sowohl den Namen als auch den Textwert zurückgeben? Beispiel: E3 enthält "2; 3; Johnny" -> Formel gibt "B; C; Johnny" zurück. Danke im Voraus. Lucio vor 6 Jahren 0
@ Lucio Ja. Die Lösung ist jedoch zu lang, um in einen Kommentar zu passen, und ist nicht wirklich dazu geeignet, diese Antwort zu ergänzen. Sie müssen eine neue Frage posten und diese für den Kontext mit dieser Frage verknüpfen. Stellen Sie sicher, dass die neue Frage in sich abgeschlossen ist. Wenn Sie das getan haben, können Sie mir einen Ping senden, indem Sie hier einen Kommentar posten. robinCTS vor 6 Jahren 0
Vielen Dank, ich habe hier eine neue Frage gestellt: https://superuser.com/questions/1334872/excel-textjoin-formula Lucio vor 6 Jahren 0

Verwandte Probleme