Abrufen mehrerer Lookup-Werte in einer einzelnen Zelle (mit und ohne Wiederholung)

Können wir mehrere Werte in einer Zelle in Excel (durch Komma oder Leerzeichen getrennt) nachschlagen und zurückgeben?

Diese Frage wurde mir von vielen meiner Kollegen und Leser mehrmals gestellt.

Excel verfügt über einige erstaunliche Nachschlageformeln wie SVERWEIS, INDEX/VERGLEICH (und jetzt XVERWEIS), aber keine davon bietet eine Möglichkeit, mehrere übereinstimmende Werte zurückzugeben. All dies funktioniert, indem die erste Übereinstimmung identifiziert und diese zurückgegeben wird.

Also habe ich ein bisschen VBA-Codierung gemacht, um eine benutzerdefinierte Funktion (auch als benutzerdefinierte Funktion bezeichnet) in Excel zu entwickeln.

Aktualisieren: Nachdem Excel dynamische Arrays und großartige Funktionen wie UNIQUE und TEXTJOIN veröffentlicht hat, ist es jetzt möglich, eine einfache Formel zu verwenden und alle übereinstimmenden Werte in einer Zelle zurückgeben (in diesem Tutorial behandelt).

In diesem Tutorial zeige ich Ihnen, wie das geht (wenn Sie die neueste Version von Excel verwenden - Microsoft 365 mit all den neuen Funktionen) sowie eine Möglichkeit, dies zu tun, falls Sie ältere Versionen verwenden ( mit VBA).

Also lasst uns anfangen!

Mehrere Werte in einer Zelle suchen und zurückgeben (mit Formel)

Wenn Sie Excel 2016 oder frühere Versionen verwenden, gehen Sie zum nächsten Abschnitt, in dem ich zeige, wie dies mit VBA geht.

Mit dem Microsoft 365-Abonnement verfügt Ihr Excel jetzt über viel leistungsfähigere Funktionen und Features, die in früheren Versionen nicht vorhanden waren (wie XLOOKUP, Dynamic Arrays, UNIQUE/FILTER-Funktionen usw.)

Wenn Sie also Microsoft 365 (früher bekannt als Office 365) verwenden, können Sie die in diesem Abschnitt behandelten Methoden verwenden, um mehrere Werte in einer einzelnen Zelle in Excel nachzuschlagen und zurückzugeben.

Und wie Sie sehen werden, ist es eine wirklich einfache Formel.

Unten habe ich einen Datensatz, in dem ich die Namen der Personen in Spalte A und die Ausbildung, die sie in Spalte B absolviert haben, habe.

Klicken Sie hier, um die Beispieldatei herunterzuladen und mitzumachen

Für jede Person möchte ich herausfinden, welche Ausbildung sie absolviert hat. In Spalte D habe ich die Liste der eindeutigen Namen (aus Spalte A), und ich möchte schnell alle Schulungen, die jede Person durchgeführt hat, nachschlagen und extrahieren und diese in einem einzigen Satz (durch Komma getrennt) abrufen.

Unten ist die Formel, die dies tut:

=TEXTJOIN(", ",TRUE,IF(D2=$A$2:$A$20,$B$2:$B$20,""))

Nachdem Sie die Formel in Zelle E2 eingegeben haben, kopieren Sie sie für alle Zellen, in denen die Ergebnisse angezeigt werden sollen.

Wie funktioniert diese Formel?

Lassen Sie mich diese Formel zerlegen und jeden Teil erklären, wie er zusammenkommt und uns das Ergebnis liefert.

Der logische Test in der WENN-Formel (D2=$A$2:$A$20) prüft, ob die Namenszelle D2 mit der im Bereich A2:A20 übereinstimmt.

Es geht jede Zelle im Bereich A2:A20 durch und prüft, ob der Name in Zelle D2 gleich ist oder nicht. wenn es derselbe Name ist, wird TRUE zurückgegeben, andernfalls wird FALSE zurückgegeben.

Dieser Teil der Formel gibt Ihnen also ein Array wie unten gezeigt:

{WAHR; FALSCH; FALSCH; WAHR; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH; FALSCH}

Da wir nur das Training für Bob (den Wert in Zelle D2) abrufen möchten, müssen wir das gesamte entsprechende Training für die Zellen abrufen, die im obigen Array WAHR zurückgeben.

Dies ist leicht zu bewerkstelligen, indem der Teil [value_if_true] der IF-Formel als Bereich angegeben wird, der das Training enthält. Dadurch wird sichergestellt, dass, wenn der Name in Zelle D2 mit dem Namen im Bereich A2:A20 übereinstimmt, die IF-Formel alle Schulungen dieser Person zurückgibt.

Und wo immer das Array FALSE zurückgibt, haben wir den [value_if_false]-Wert als „“ (leer) angegeben, also gibt es ein Leerzeichen zurück.

Der IF-Teil der Formel gibt das Array wie unten gezeigt zurück:

{"Excel";"";"";"PowerPoint";"";"";"";"";"";"";"";"";"";"";"";"";””;”””;””}

Wo es den Namen des Trainings hat, das Bob genommen hat, und Leerzeichen, wo der Name nicht Bob war.

Jetzt müssen wir nur noch diese Trainingsnamen (durch Komma getrennt) kombinieren und in einer Zelle zurückgeben.

Und das geht ganz einfach mit der neuen TEXTJOIN-Formel (verfügbar in Excel2021-2022 und Excel in Microsoft 365)

Die TEXTJOIN-Formel verwendet drei Argumente:

• das Trennzeichen - in unserem Beispiel ", ", da ich möchte, dass das Training durch ein Komma und ein Leerzeichen getrennt wird
• TRUE - was der TEXTJOIN-Formel mitteilt, leere Zellen zu ignorieren und nur solche zu kombinieren, die nicht leer sind
• Die If-Formel, die den zu kombinierenden Text zurückgibt

Wenn Sie Excel in Microsoft 365 verwenden, das bereits über dynamische Arrays verfügt, können Sie einfach die obige Formel eingeben und die Eingabetaste drücken. Und wenn Sie Excel2021-2022 verwenden, müssen Sie die Formel eingeben, die Strg- und die Umschalttaste gedrückt halten und dann die Eingabetaste drücken

Klicken Sie hier, um die Beispieldatei herunterzuladen und mitzumachen

Abrufen mehrerer Lookup-Werte in einer einzelnen Zelle (ohne Wiederholung)

Da die UNIQUE-Formel nur für Excel in Microsoft 365 verfügbar ist, können Sie diese Methode in Excel2021-2022 nicht verwenden

Falls es Wiederholungen in Ihrem Datensatz gibt, wie unten gezeigt, müssen Sie die Formel ein wenig ändern, damit Sie nur eine Liste mit eindeutigen Werten in einer einzelnen Zelle erhalten.

Im obigen Datensatz haben einige Personen mehrmals trainiert. Bob und Stan haben zum Beispiel zweimal an der Excel-Schulung teilgenommen, und Betty hat zweimal an der MS Word-Schulung teilgenommen. Aber in unserem Ergebnis wollen wir keinen Trainingsnamen wiederholen.

Dazu können Sie die folgende Formel verwenden:

=TEXTJOIN(", ",TRUE,UNIQUE(IF(D2=$A$2:$A$20,$B$2:$B$20,"")))

Die obige Formel funktioniert mit einer geringfügigen Änderung auf die gleiche Weise. Wir haben die IF-Formel innerhalb der UNIQUE-Funktion verwendet, damit die UNIQUE-Funktion sie bei Wiederholungen im Ergebnis der if-Formel entfernt.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Suchen und Zurückgeben mehrerer Werte in einer Zelle (mit VBA)

Wenn Sie Excel 2016 oder frühere Versionen verwenden, haben Sie keinen Zugriff auf die TEXTJOIN-Formel. Die beste Möglichkeit, dann mehrere übereinstimmende Werte in einer einzelnen Zelle nachzuschlagen und abzurufen, besteht darin, eine benutzerdefinierte Formel zu verwenden, die Sie mit VBA erstellen können.

Um mehrere Lookup-Werte in einer einzelnen Zelle zu erhalten, müssen wir in VBA (ähnlich der SVERWEIS-Funktion) eine Funktion erstellen, die jede Zelle in einer Spalte überprüft und wenn der Lookup-Wert gefunden wird, zum Ergebnis hinzufügt.

Hier ist der VBA-Code, der dies tun kann:

'Code by Sumit Bansal (https://trumpexcel.com) Funktion SingleCellExtract(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," End If Next i SingleCellExtract = Left(Result, Len(Result) - 1) Endfunktion

Wohin mit diesem Code?

1. Öffnen Sie eine Arbeitsmappe und klicken Sie auf Alt + F11 (dadurch wird das VBA-Editor-Fenster geöffnet).
2. In diesem VBA-Editor-Fenster befindet sich auf der linken Seite ein Projekt-Explorer (in dem alle Arbeitsmappen und Arbeitsblätter aufgelistet sind). Klicken Sie mit der rechten Maustaste auf ein beliebiges Objekt in der Arbeitsmappe, in dem dieser Code funktionieren soll, und gehen Sie zu Einfügen -> Modul.
3. Kopieren Sie im Modulfenster (das rechts angezeigt wird) den obigen Code und fügen Sie ihn ein.
4. Jetzt sind Sie fertig. Gehen Sie zu einer beliebigen Zelle in der Arbeitsmappe und geben Sie ein =Einzelzellextrakt und fügen Sie die erforderlichen Eingabeargumente ein (d. h. LookupValue, LookupRange, ColumnNumber).

Wie funktioniert diese Formel?

Diese Funktion funktioniert ähnlich wie die SVERWEIS-Funktion.

Es benötigt 3 Argumente als Eingaben:

1. Lookup-Wert - Eine Zeichenfolge, die wir in einem Bereich von Zellen nachschlagen müssen.
2. Suchbereich - Ein Array von Zellen, aus denen wir die Daten holen müssen (in diesem Fall $B3:$C18).
3. Spaltennummer - Es ist die Spaltennummer der Tabelle/des Arrays, aus der der passende Wert zurückgegeben werden soll (hier 2).

Wenn Sie diese Formel verwenden, überprüft sie jede Zelle in der Spalte ganz links im Nachschlagebereich und wenn eine Übereinstimmung gefunden wird, wird das Ergebnis in der Zelle hinzugefügt, in der Sie die Formel verwendet haben.

Merken: Speichern Sie die Arbeitsmappe als makroaktivierte Arbeitsmappe (.xlsm oder .xls), um diese Formel erneut zu verwenden. Außerdem wäre diese Funktion nur in dieser Arbeitsmappe und nicht in allen Arbeitsmappen verfügbar.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Erfahren Sie, wie Sie langweilige, sich wiederholende Aufgaben mit VBA in Excel automatisieren. Mach mit Excel VBA-Kurs

Abrufen mehrerer Lookup-Werte in einer einzelnen Zelle (ohne Wiederholung)

Es besteht die Möglichkeit, dass sich die Daten wiederholen.

Wenn Sie den oben verwendeten Code verwenden, erhalten Sie auch Wiederholungen im Ergebnis.

Wenn Sie das Ergebnis ohne Wiederholungen erhalten möchten, müssen Sie den Code ein wenig ändern.

Hier ist der VBA-Code, der Ihnen mehrere Lookup-Werte in einer einzelnen Zelle ohne Wiederholungen liefert.

'Code by Sumit Bansal (https://trumpexcel.com) Funktion MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells .Count If LookupRange.Cells(i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells(J, 1) = Lookupvalue Then If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then GoTo Skip End If End If Next J Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left(Result, Len(Result) - 1) End Funktion

Sobald Sie diesen Code im VB-Editor platziert haben (wie oben im Tutorial gezeigt), können Sie den MultipleLookupNoRept Funktion.

Hier ist eine Momentaufnahme des Ergebnisses, das Sie damit erhalten MultipleLookupNoRept Funktion.

Klicken Sie hier, um die Beispieldatei herunterzuladen

In diesem Tutorial habe ich behandelt, wie Sie Formeln und VBA in Excel verwenden, um mehrere Nachschlagewerte in einer Zelle in Excel zu finden und zurückzugeben.

Wenn Sie Excel im Microsoft 365-Abonnement verwenden, können Sie dies zwar problemlos mit einer einfachen Formel ausführen. Wenn Sie jedoch frühere Versionen verwenden und keinen Zugriff auf Funktionen wie TEXTJOIN haben, können Sie dies dennoch mit VBA tun, indem Sie Ihre eigene benutzerdefinierte Funktion.