Comment vlookup pour renvoyer plusieurs valeurs dans une cellule dans Excel?

Normalement, dans Excel, lorsque vous utilisez la fonction RECHERCHEV, s'il existe plusieurs valeurs correspondant aux critères, vous pouvez simplement obtenir la première. Mais, parfois, vous souhaitez renvoyer toutes les valeurs correspondantes qui répondent aux critères dans une cellule comme illustré ci-dessous, comment pouvez-vous le résoudre?

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction TEXTJOIN (Excel 2019 et Office 365)

• Vlookup pour renvoyer toutes les valeurs correspondantes dans une cellule
• Vlookup pour renvoyer toutes les valeurs correspondantes sans doublons dans une cellule

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction définie par l'utilisateur

• Vlookup pour renvoyer toutes les valeurs correspondantes dans une cellule
• Vlookup pour renvoyer toutes les valeurs correspondantes sans doublons dans une cellule

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec une fonctionnalité utile

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction TEXTJOIN (Excel 2019 et Office 365)

Si vous disposez de la version supérieure d'Excel telle que Excel 2019 et Office 365, il existe une nouvelle fonction - TEXTEJOINDRE, avec cette fonction puissante, vous pouvez rapidement vlookup et renvoyer toutes les valeurs correspondantes dans une cellule.

  Vlookup pour renvoyer toutes les valeurs correspondantes dans une cellule

Veuillez appliquer la formule ci-dessous dans une cellule vide où vous souhaitez mettre le résultat, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le premier résultat, puis faites glisser la poignée de remplissage vers la cellule dans laquelle vous souhaitez utiliser cette formule, et vous obtiendrez toutes les valeurs correspondantes, comme illustré ci-dessous:

  Vlookup pour renvoyer toutes les valeurs correspondantes sans doublons dans une cellule

Si vous souhaitez renvoyer toutes les valeurs correspondantes basées sur les données de recherche sans doublons, la formule ci-dessous peut vous aider.

Veuillez copier et coller la formule suivante dans une cellule vide, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le premier résultat, puis copiez cette formule pour remplir d'autres cellules, et vous obtiendrez toutes les valeurs correspondantes sans les dulpiquées, comme illustré ci-dessous:

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction définie par l'utilisateur

La fonction TEXTJOIN ci-dessus n'est disponible que pour Excel 2019 et Office 365, si vous avez d'autres versions d'Excel inférieures, vous devez utiliser certains codes pour terminer cette tâche.

  Vlookup pour renvoyer toutes les valeurs correspondantes dans une cellule

1. Maintenez le ALT + F11 clés, et il ouvre le Microsoft Visual Basic pour applications fenêtre.

2Cliquez sur insérer > Moduleet collez le code suivant dans le Fenêtre du module.

Code VBA: Vlookup pour renvoyer plusieurs valeurs dans une cellule

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. Ensuite, enregistrez et fermez ce code, revenez à la feuille de calcul et entrez cette formule: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") dans une cellule vide spécifique où vous souhaitez placer le résultat, puis faites glisser la poignée de recopie vers le bas pour obtenir toutes les valeurs correspondantes dans une cellule souhaitée, voir capture d'écran:

  Vlookup pour renvoyer toutes les valeurs correspondantes sans doublons dans une cellule

Pour ignorer les doublons dans les valeurs correspondantes renvoyées, veuillez utiliser le code ci-dessous.

1. Maintenez le Alt + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2Cliquez sur insérer > Moduleet collez le code suivant dans le Fenêtre du module.

Code VBA: Vlookup et retourne plusieurs valeurs correspondantes uniques dans une cellule

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. Après avoir inséré le code, cliquez sur Outils > Références en plein air Microsoft Visual Basic pour applications fenêtre, puis, dans le sauté Références - VBAProject boîte de dialogue, cochez Exécution de scripts Microsoft option dans la Références disponibles zone de liste, voir les captures d'écran:

4. Puis clique OK pour fermer la boîte de dialogue, enregistrez et fermez la fenêtre de code, revenez à la feuille de calcul et entrez cette formule: =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 pour renvoyer plusieurs valeurs dans une cellule avec une fonctionnalité utile

 Si vous avez notre Kutools for Excel, Avec son Lignes de combinaison avancées fonctionnalité, vous pouvez rapidement fusionner ou combiner les lignes en fonction de la même valeur et effectuer certains calculs selon vos besoins.

Après l'installation de Kutools for Excel, procédez comme suit:

1. Sélectionnez la plage de données dans laquelle vous souhaitez combiner les données d'une colonne en fonction d'une autre colonne.

2Cliquez sur Kutools > Fusionner et fractionner > Lignes de combinaison avancées, voir capture d'écran:

3. Dans le sauté Lignes de combinaison avancées boite de dialogue:

• Cliquez sur le nom de la colonne clé à combiner en fonction, puis cliquez sur Clé primaire
• Cliquez ensuite sur une autre colonne pour laquelle vous souhaitez combiner ses données en fonction de la colonne clé, puis cliquez sur Combiner pour choisir un séparateur pour séparer les données combinées.

4. Puis clique OK bouton, et vous obtiendrez les résultats suivants:

Téléchargez et essayez gratuitement Kutools for Excel Now!

Articles plus relatifs: