Wie kann ich vlookup verwenden, um mehrere Werte in einer Zelle in Excel zurückzugeben?
Wenn Sie in Excel die VLOOKUP-Funktion verwenden und mehrere Werte für die Kriterien vorhanden sind, können Sie normalerweise nur den ersten abrufen. Aber manchmal möchten Sie alle entsprechenden Werte, die die Kriterien erfüllen, in einer Zelle zurückgeben, wie im folgenden Screenshot gezeigt. Wie können Sie das lösen?
- Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben
- Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben
Vlookup, um mehrere Werte mit einer benutzerdefinierten Funktion in eine Zelle zurückzugeben
- Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben
- Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben
Vlookup, um mehrere Werte mit einer nützlichen Funktion in eine Zelle zurückzugeben
Vlookup zur Rückgabe mehrerer Werte in eine Zelle mit der Funktion TEXTJOIN (Excel 2019 und Office 365)
Wenn Sie die höhere Version von Excel wie Excel 2019 und Office 365 haben, gibt es eine neue Funktion: TEXTVERBINDENMit dieser leistungsstarken Funktion können Sie schnell alle übereinstimmenden Werte in einer Zelle anzeigen und zurückgeben.
Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben
Bitte wenden Sie die folgende Formel in eine leere Zelle an, in die Sie das Ergebnis einfügen möchten, und drücken Sie dann Strg + Umschalt + Enter Geben Sie die Tasten zusammen, um das erste Ergebnis zu erhalten, und ziehen Sie dann den Füllpunkt nach unten in die Zelle, in der Sie diese Formel verwenden möchten. Sie erhalten alle entsprechenden Werte wie im folgenden Screenshot gezeigt:
Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben
Wenn Sie alle übereinstimmenden Werte basierend auf den Suchdaten ohne Duplikate zurückgeben möchten, kann die folgende Formel hilfreich sein.
Bitte kopieren Sie die folgende Formel und fügen Sie sie in eine leere Zelle ein. Drücken Sie dann Strg + Umschalt + Enter Geben Sie die Tasten zusammen, um das erste Ergebnis zu erhalten, und kopieren Sie diese Formel, um andere Zellen zu füllen. Sie erhalten dann alle entsprechenden Werte ohne die doppelten Werte, wie im folgenden Screenshot gezeigt:
Vlookup, um mehrere Werte mit einer benutzerdefinierten Funktion in eine Zelle zurückzugeben
Die obige TEXTJOIN-Funktion ist nur für Excel 2019 und Office 365 verfügbar. Wenn Sie andere niedrigere Excel-Versionen haben, sollten Sie zum Beenden dieser Aufgabe einige Codes verwenden.
Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben
1. Halten Sie die Taste gedrückt ALT + F11 Tasten, und es öffnet die Microsoft Visual Basic für Applikationen Fenster.
2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modulfenster.
VBA-Code: Vlookup, um mehrere Werte in eine Zelle zurückzugeben
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function
3. Speichern und schließen Sie diesen Code, kehren Sie zum Arbeitsblatt zurück und geben Sie die folgende Formel ein: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") in eine bestimmte leere Zelle, in der Sie das Ergebnis platzieren möchten, und ziehen Sie dann den Füllpunkt nach unten, um alle entsprechenden Werte in einer gewünschten Zelle zu erhalten (siehe Abbildung):
Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben
Um die Duplikate in den zurückgegebenen übereinstimmenden Werten zu ignorieren, verwenden Sie bitte den folgenden Code.
1. Halten Sie die Taste gedrückt Alt + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.
2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modulfenster.
VBA-Code: Suchen Sie und geben Sie mehrere eindeutige übereinstimmende Werte in einer Zelle zurück
Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
Dim xDic As New Dictionary
Dim xRows As Long
Dim xStr As String
Dim i As Long
On Error Resume Next
xRows = LookupRange.Rows.Count
For i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Next
xStr = ""
MultipleLookupNoRept = xStr
If xDic.Count > 0 Then
For i = 0 To xDic.Count - 1
xStr = xStr & xDic.Keys(i) & ","
Next
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
End If
End Function
3. Klicken Sie nach dem Einfügen des Codes auf Tools > Bibliographie im geöffneten Microsoft Visual Basic für Applikationen Fenster, und dann in der herausgesprungen Referenzen - VBAProject Dialogfeld überprüfen Microsoft Scripting-Laufzeit Option in der Verfügbare Referenzen Listenfeld, siehe Screenshots:
 |
 |
 |
4. Dann klick OK Um das Dialogfeld zu schließen, speichern und schließen Sie das Codefenster, kehren Sie zum Arbeitsblatt zurück und geben Sie die folgende Formel ein: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:
Vlookup, um mehrere Werte mit einer nützlichen Funktion in eine Zelle zurückzugeben
Wenn Sie unsere haben Kutools for ExcelMit seinen Erweiterte Zeilen kombinieren Mit dieser Funktion können Sie die Zeilen basierend auf demselben Wert schnell zusammenführen oder kombinieren und nach Bedarf einige Berechnungen durchführen.
Nach der Installation Kutools for ExcelBitte gehen Sie wie folgt vor:
1. Wählen Sie den Datenbereich aus, in dem Sie eine Spaltendaten basierend auf einer anderen Spalte kombinieren möchten.
2. Klicken Sie Kutoolen > Zusammenführen & Teilen > Erweiterte Zeilen kombinieren, siehe Screenshot:
3. In der herausgesprungen Erweiterte Zeilen kombinieren Dialogbox:
- Klicken Sie auf den Namen der Schlüsselspalte, auf der basierend kombiniert werden soll, und klicken Sie dann auf Primärschlüssel
- Klicken Sie dann auf eine andere Spalte, deren Daten Sie basierend auf der Schlüsselspalte kombinieren möchten, und klicken Sie auf Kombinieren ein Trennzeichen zum Trennen der kombinierten Daten auswählen.
4. Dann klick OK Klicken Sie auf die Schaltfläche, und Sie erhalten die folgenden Ergebnisse:
 |
|
 |
Laden Sie Kutools for Excel jetzt herunter und testen Sie es kostenlos!
Weitere relative Artikel:
- VLOOKUP-Funktion mit einigen grundlegenden und erweiterten Beispielen
- In Excel ist die VLOOKUP-Funktion für die meisten Excel-Benutzer eine leistungsstarke Funktion, mit der nach einem Wert ganz links im Datenbereich gesucht und ein übereinstimmender Wert in derselben Zeile aus einer von Ihnen angegebenen Spalte zurückgegeben wird. In diesem Tutorial wird die Verwendung der VLOOKUP-Funktion anhand einiger grundlegender und erweiterter Beispiele in Excel erläutert.
- Gibt mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurück
- Normalerweise ist es für die meisten von uns einfach, einen bestimmten Wert zu suchen und den passenden Artikel zurückzugeben, indem sie die VLOOKUP-Funktion verwenden. Haben Sie jemals versucht, mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurückzugeben? In diesem Artikel werde ich einige Formeln zur Lösung dieser komplexen Aufgabe in Excel vorstellen.
- Mehrere Werte vertikal anzeigen und zurückgeben
- Normalerweise können Sie die Vlookup-Funktion verwenden, um den ersten entsprechenden Wert abzurufen. Manchmal möchten Sie jedoch alle übereinstimmenden Datensätze basierend auf einem bestimmten Kriterium zurückgeben. In diesem Artikel werde ich darüber sprechen, wie alle übereinstimmenden Werte vertikal, horizontal oder in einer einzelnen Zelle angezeigt und zurückgegeben werden.
- Anzeigen und Zurückgeben mehrerer Werte aus der Dropdown-Liste
- Wie können Sie in Excel mehrere entsprechende Werte aus einer Dropdown-Liste anzeigen und zurückgeben? Wenn Sie also ein Element aus der Dropdown-Liste auswählen, werden alle relativen Werte gleichzeitig angezeigt. In diesem Artikel werde ich die Lösung Schritt für Schritt vorstellen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor. Kutools für Excel bietet über 300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...
Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
- Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
