Comment effectuer une recherche VLOOKUP et concaténer plusieurs valeurs correspondantes dans Excel ?

Lorsque vous utilisez VLOOKUP dans Excel, la fonction retourne généralement uniquement la première valeur correspondante qu’elle trouve pour un critère de recherche donné. Cependant, il existe de nombreux cas courants où vous pourriez avoir besoin de récupérer et combiner toutes les valeurs correspondantes associées à une clé particulière, comme lister tous les étudiants d'une classe ou tous les produits associés à une certaine catégorie. Comme la fonction VLOOKUP standard est limitée à cet égard, vous pourriez vous demander comment parvenir à rechercher et concaténer plusieurs résultats correspondants dans une seule cellule. Ci-dessous, nous explorerons plusieurs méthodes pratiques et efficaces pour accomplir cette tâche, adaptées aux différentes versions d'Excel et aux préférences des utilisateurs.

Recherche VLOOKUP et concaténation de plusieurs valeurs correspondantes dans Excel

• Utilisation des fonctions TEXTJOIN et FILTER (Excel 365/2021)
• Utilisation de Kutools pour Excel
• Utilisation d'une Fonction Définie par l'Utilisateur
• Utilisation du code VBA pour concaténer plusieurs fois

Recherche VLOOKUP et concaténation de plusieurs valeurs correspondantes avec les fonctions TEXTJOIN et FILTER

Si vous utilisez Excel 365 ou Excel 2021, la combinaison des fonctions TEXTJOIN et FILTER offre une approche efficace basée sur des formules pour effectuer une recherche VLOOKUP et concaténer toutes les valeurs correspondantes. Cette solution est particulièrement adaptée aux ensembles de données dynamiques et mis à jour, car elle actualisera automatiquement le résultat lorsque les données sources changent. Elle est mieux appliquée lorsque votre version d’Excel prend en charge la fonction FILTER, qui est exclusive aux versions récentes d’Office.

Dans la cellule cible, entrez la formule suivante, puis faites glisser la formule vers le bas si vous souhaitez l'appliquer à d'autres lignes également. Toutes les valeurs correspondantes sont extraites et combinées dans une seule cellule. Voir capture d'écran :

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Recherche VLOOKUP et concaténation de plusieurs valeurs correspondantes avec Kutools pour Excel

Si vous trouvez que les méthodes de formules intégrées sont difficiles ou que votre version d’Excel ne prend pas en charge les fonctions avancées comme TEXTJOIN et FILTER, Kutools pour Excel offre une solution graphique conviviale. La fonctionnalité Recherche un-à-plusieurs de Kutools vous permet de rechercher et concaténer plusieurs résultats correspondants en quelques étapes seulement, ce qui la rend adaptée aussi bien pour les débutants que pour les utilisateurs avancés. Avec Kutools, il n'est pas nécessaire d'écrire des formules ou des codes compliqués, et c'est particulièrement pratique lorsqu'il s'agit de grands ensembles de données variables nécessitant des recherches et des agrégations répétées.

Après avoir installé Kutools pour Excel, suivez les étapes ci-dessous :

Cliquez sur Kutools > Super RECHERCHEV > Recherche un-à-plusieurs (retourner plusieurs résultats) pour ouvrir la boîte de dialogue de configuration. Dans cette boîte de dialogue, vous pouvez rapidement configurer vos paramètres de recherche et de sortie en suivant les étapes suivantes :

1. Sélectionnez vos cellules cibles de sortie pour les résultats concaténés, ainsi que les cellules contenant les valeurs que vous souhaitez rechercher ;
2. Indiquez la plage du tableau qui contient à la fois la clé de recherche et les colonnes de résultats ;
3. Précisez quelle colonne contient les clés de recherche (Colonne Clé) et la colonne dont les valeurs seront concaténées (Colonne de Retour) ;
4. Cliquez sur le bouton OK pour confirmer vos paramètres et traiter les données.
   

Résultat: Kutools affichera désormais toutes les valeurs correspondantes et concaténées dans votre cellule de sortie sélectionnée. Voir capture d'écran :

Cette méthode est fortement recommandée pour ceux qui préfèrent travailler depuis l'interface Excel sans formules complexes ou code. Elle réduit également la probabilité d'erreurs de formule et améliore la productivité dans la gestion des tâches de recherche et de concaténation répétitives.

Recherche VLOOKUP et concaténation de plusieurs valeurs correspondantes avec une Fonction Définie par l'Utilisateur

Pour les utilisateurs qui maîtrisent VBA (Visual Basic pour Applications), ou ceux qui utilisent des versions plus anciennes d’Excel qui manquent de support pour les tableaux dynamiques ou la fonction FILTER, vous pouvez créer une Fonction Définie par l'Utilisateur (UDF) personnalisée pour réaliser une concaténation flexible de plusieurs résultats. Cette méthode est universellement compatible avec toutes les versions d'Excel et peut être adaptée à des symboles de séparation ou conditions particulières.

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

2. Cliquez sur Insertion > Module, puis collez le code suivant dans la Fenêtre Module.

Code VBA : Recherche VLOOKUP et concaténation de plusieurs valeurs correspondantes dans une cellule

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. Enregistrez et fermez l'éditeur VBA. Retournez à votre feuille de calcul, et utilisez cette UDF en entrant la formule : =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) dans une cellule vide où vous souhaitez obtenir votre résultat. Faites glisser la poignée de remplissage vers le bas pour copier la formule dans d'autres cellules si nécessaire. Toutes les valeurs correspondantes basées sur une valeur de recherche spécifique seront retournées et concaténées dans une seule cellule, séparées par une virgule et un espace. Voir capture d'écran :

Recherche VLOOKUP et concaténation de plusieurs valeurs correspondantes avec du code VBA

Pour les scénarios nécessitant une utilisation répétitive ou pour ceux souhaitant éviter les fonctions personnalisées dans les cellules de la feuille de calcul, vous pouvez utiliser une macro VBA prête à l'emploi pour concaténer directement les résultats. Cette méthode fonctionne bien dans des environnements partagés où tous les utilisateurs n'ont peut-être pas la même version ou les mêmes add-ins.

1. Cliquez sur Outils de développement > Visual Basic pour ouvrir l'éditeur VBA.

2. Dans la fenêtre VBA, cliquez sur Insertion > Module, puis collez ce code dans le module :

Sub VLookupAndConcatenate()
    Dim ws As Worksheet
    Dim dataRange As Range, lookupRange As Range, resultRange As Range
    Dim dict As Object
    Dim i As Long, lastRow As Long
    Dim lookupValue As Variant, result As String
    Dim delimiter As String
    delimiter = ", "
    Set dict = CreateObject("Scripting.Dictionary")
    Set ws = ActiveSheet
    On Error Resume Next
    Set dataRange = Application.InputBox( _
        Prompt:="Please select the data range (contains lookup column and result column)", _
        Title:="Select Data Range", _
        Type:=8)
    On Error GoTo 0
    If dataRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set lookupRange = Application.InputBox( _
        Prompt:="Please select the lookup range (single column)", _
        Title:="Select Lookup Range", _
        Type:=8)
    On Error GoTo 0
    If lookupRange Is Nothing Then Exit Sub
    On Error Resume Next
    Set resultRange = Application.InputBox( _
        Prompt:="Please select the starting cell for results output", _
        Title:="Select Output Location", _
        Type:=8)
    On Error GoTo 0
    If resultRange Is Nothing Then Exit Sub
    resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
    For i = 1 To dataRange.Rows.Count
        lookupValue = dataRange.Cells(i, 1).Value
        If Not dict.Exists(lookupValue) Then
            dict.Add lookupValue, dataRange.Cells(i, 2).Value
        Else
            dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
        End If
    Next i
    For i = 1 To lookupRange.Rows.Count
        lookupValue = lookupRange.Cells(i, 1).Value
        If dict.Exists(lookupValue) Then
            resultRange.Cells(i, 1).Value = dict(lookupValue)
        Else
            resultRange.Cells(i, 1).Value = "Not Found"
        End If
    Next i
    MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub

3. Cliquez sur le bouton pour exécuter la macro. Les boîtes de dialogue vous inviteront à sélectionner votre plage de données, votre plage de recherche, et votre plage de résultats. Le résultat concaténé est ensuite affiché directement dans les cellules de sortie sélectionnées.

Cette approche par macro est particulièrement utile si vous effectuez fréquemment des recherches de concaténation multiples avec différentes valeurs, car elle évite d'encombrer la feuille de calcul avec des appels de fonction définie par l'utilisateur (UDF).

Vous pouvez facilement ajuster le délimiteur dans le code si nécessaire, et étendre la macro pour afficher les résultats dans une cellule ou un fichier selon votre flux de travail.

La concaténation de plusieurs valeurs correspondantes dans Excel est possible à l'aide de diverses approches, chacune ayant des avantages spécifiques selon votre situation. Que vous choisissiez des formules de tableaux dynamiques, des add-ins comme Kutools pour Excel ou des méthodes basées sur VBA, vous améliorerez votre capacité à analyser et afficher des données groupées de manière efficace. Selon la taille et la complexité de votre jeu de données, considérez quelle approche offre les meilleures performances et facilité de maintenance pour vous ou votre équipe. Dans les opérations quotidiennes, vérifiez la cohérence des données, évitez les cellules fusionnées et vérifiez les plages de référence pour de meilleurs résultats. Si vous rencontrez des erreurs dans les calculs de formules, revérifiez que vos plages correspondent aux données et que vous utilisez la méthode d'entrée de formule correcte pour votre version d'Excel.

Pour plus de techniques avancées sur Excel et une large gamme de guides pratiques, visitez notre bibliothèque de tutoriels complète.