So führen Sie eine Vlookup-Suche durch, um mehrere Werte in einer Zelle zurückzugeben Excel?
Normalerweise in ExcelWenn Sie die VLOOKUP-Funktion verwenden und mehrere Werte vorhanden sind, die den Kriterien entsprechen, können Sie einfach 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, um mit der TEXTJOIN-Funktion mehrere Werte in eine Zelle zurückzugeben (Excel 2019 und Office 365)
Wenn Sie die höhere Version von haben Excel sowie Excel 2019 und Office 365 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 verfügbar für Excel 2019 und Office 365, falls Sie andere niedrigere Versionen haben Excel Versionen verwenden, sollten Sie einige Codes zum Abschließen dieser Aufgabe 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 > Referenzen 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 Kutools > 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:
 |
|
 |
Download und kostenlose Testversion Kutools for Excel Jetzt !
Weitere relative Artikel:
- VLOOKUP-Funktion mit einigen grundlegenden und erweiterten Beispielen
- In Excel, die SVERWEIS-Funktion ist für die meisten eine leistungsstarke Funktion Excel Benutzer, die verwendet werden, um nach einem Wert ganz links im Datenbereich zu suchen und einen passenden Wert in derselben Zeile aus einer von Ihnen angegebenen Spalte zurückzugeben. In diesem Tutorial geht es um die Verwendung der SVERWEIS-Funktion anhand einiger grundlegender und fortgeschrittener Beispiele Excel.
- Gibt mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurück
- Normalerweise ist es für die meisten von uns einfach, mit der VLOOKUP-Funktion nach einem bestimmten Wert zu suchen und das entsprechende Element zurückzugeben. Aber haben Sie schon einmal 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 vorstellen Excel.
- 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
- In Excel, wie könnten Sie mehrere entsprechende Werte aus einer Dropdown-Liste nachschlagen 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
Bringen Sie Ihre Tabellen auf den neuesten Stand: Erleben Sie Effizienz wie nie zuvor mit Kutools for Excel
Kutools for Excel verfügt über über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...
Unterstützt Office/Excel 2007–2021 und neuer, einschließlich 365 | Verfügbar in 44 Sprachen | Genießen Sie eine 30-tägige kostenlose Testversion mit vollem Funktionsumfang.
Office Tab Bringt eine Tab-Oberfläche in Office und erleichtert Ihnen die Arbeit erheblich
- Aktivieren Sie das Bearbeiten und Einlesen mit Registerkarten Word, Excel, Power Point, 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!
