Comment utiliser la fonction RECHERCHEV pour retourner plusieurs valeurs dans une seule cellule dans Excel ?

La fonction RECHERCHEV est une fonction puissante dans Excel, mais par défaut, elle ne retourne que la première valeur correspondante. Que faire si vous avez besoin de récupérer toutes les valeurs correspondantes et de les combiner dans une seule cellule ? C'est une exigence courante lors de l'analyse de jeux de données ou de la synthèse d'informations. Dans ce guide, nous allons vous guider étape par étape à travers des méthodes permettant de retourner plusieurs valeurs dans une seule cellule en utilisant des formules ainsi qu'une fonctionnalité utile.

Retourner plusieurs valeurs dans une seule cellule avec la fonction TEXTJOIN (Excel 2019 et Office 365)

• Retourner toutes les valeurs correspondantes dans une seule cellule
• Retourner toutes les valeurs correspondantes sans doublons dans une seule cellule

Retourner plusieurs valeurs dans une seule cellule avec Kutools

Retourner plusieurs valeurs dans une seule cellule avec une fonction définie par l'utilisateur

• Retourner toutes les valeurs correspondantes dans une seule cellule
• Retourner toutes les valeurs correspondantes sans doublons dans une seule cellule

Retourner plusieurs valeurs dans une seule cellule avec la fonction TEXTJOIN (Excel 2019 et Office 365)

Si vous disposez d'une version plus récente d'Excel, comme Excel 2019 et Office 365, il existe une nouvelle fonction - TEXTJOIN, avec cette fonction puissante, vous pouvez rapidement effectuer une recherche et retourner toutes les valeurs correspondantes dans une seule cellule.

  Retourner toutes les valeurs correspondantes dans une seule cellule

Veuillez appliquer la formule ci-dessous dans une cellule vide où vous souhaitez placer le résultat, puis appuyez simultanément sur Ctrl + Maj + Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers le bas jusqu'à la cellule où vous souhaitez utiliser cette formule, et vous obtiendrez toutes les valeurs correspondantes comme indiqué dans la capture d'écran ci-dessous :

  Retourner toutes les valeurs correspondantes sans doublons dans une seule cellule

Si vous souhaitez retourner 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 simultanément sur Ctrl + Maj + Entrée pour obtenir le premier résultat, puis copiez cette formule pour remplir les autres cellules, et vous obtiendrez toutes les valeurs correspondantes sans les doublons comme indiqué dans la capture d'écran ci-dessous :

Retourner plusieurs valeurs dans une seule cellule avec Kutools

Avec la fonctionnalité « Fusion avancée des lignes » de Kutools pour Excel, vous pouvez facilement récupérer plusieurs valeurs correspondantes dans une seule cellule — aucune formule complexe n'est requise ! Dites adieu aux solutions manuelles et découvrez un moyen plus efficace de gérer vos tâches de recherche dans Excel. Découvrons comment Kutools pour Excel rend cela possible !

Après avoir installé Kutools pour Excel, veuillez procéder comme suit :

1. Sélectionnez la plage de données que vous souhaitez combiner en une colonne basée sur une autre colonne.

2. Cliquez sur « Kutools » > « Fusionner et diviser » > « Fusion avancée des lignes », voir capture d'écran :

3. Dans la boîte de dialogue « Fusion avancée des lignes » qui s'affiche :

• Cliquez sur le nom de la colonne clé à combiner, puis cliquez sur « Clé principale ».
• Ensuite, cliquez sur une autre colonne dont vous souhaitez combiner les données en fonction de la colonne clé, et cliquez sur la liste déroulante du champ « Calcul » pour choisir un séparateur afin de séparer les données combinées dans la section « Consolider ».
• Ensuite, cliquez sur le bouton OK.

Toutes les valeurs correspondantes d'une autre colonne, basées sur la même valeur, sont combinées dans une seule cellule. Voir captures d'écran :

Astuce : Si vous souhaitez supprimer le contenu en double tout en fusionnant les cellules, cochez simplement l'option « Supprimer les valeurs en double » dans la boîte de dialogue. Cela garantit que seules les entrées uniques sont combinées dans une seule cellule, rendant vos données plus propres et mieux organisées sans effort supplémentaire. Voir captures d'écran :

Téléchargez et essayez gratuitement Kutools pour Excel maintenant !

Retourner plusieurs valeurs dans une seule cellule avec une fonction définie par l'utilisateur

La fonction TEXTJOIN ci-dessus n'est disponible que pour Excel 2019 et Office 365, si vous utilisez d'autres versions antérieures d'Excel, vous devez utiliser du code pour accomplir cette tâche.

  Retourner toutes les valeurs correspondantes dans une seule cellule

1. Maintenez les touches « ALT + F11 » enfoncées, et cela ouvre la fenêtre « Microsoft Visual Basic pour Applications ».

2. Cliquez sur « Insertion » > « Module », et collez le code suivant dans la fenêtre Module.

Code VBA : RECHERCHEV pour retourner plusieurs valeurs dans une seule 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 seule cellule que vous souhaitez, voir capture d'écran :

  Retourner toutes les valeurs correspondantes sans doublons dans une seule cellule

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

1. Maintenez les touches « Alt + F11 » enfoncées pour ouvrir la fenêtre « Microsoft Visual Basic pour Applications ».

2. Cliquez sur « Insertion » > « Module », et collez le code suivant dans la fenêtre Module.

Code VBA : RECHERCHEV et retourner plusieurs valeurs uniques correspondantes dans une seule 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 » dans la fenêtre « Microsoft Visual Basic pour Applications » ouverte, puis, dans la boîte de dialogue « Références – Projet VBA » qui s'affiche, cochez l'option « Microsoft Scripting Runtime » dans la liste des « Références disponibles », voir captures d'écran :

4. Cliquez ensuite sur 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) dans une cellule vide où vous souhaitez afficher le résultat, puis faites glisser la poignée de recopie vers le bas pour obtenir toutes les valeurs correspondantes, voir capture d'écran :

Que vous optiez pour des formules comme TEXTJOIN combinée avec des fonctions matricielles, utilisiez des outils comme Kutools pour Excel ou une fonction définie par l'utilisateur, toutes ces approches aident à simplifier les tâches de recherche complexes. Choisissez la méthode qui convient le mieux à vos besoins. Si vous êtes intéressé par l'exploration de plus d'astuces et conseils Excel, notre site web propose des milliers de tutoriels.

Plus d'articles relatifs :