Comment effectuer une recherche verticale et concaténer toutes les valeurs correspondantes dans Excel ?
Lorsque vous utilisez la fonction RECHERCHEV (VLOOKUP) dans Excel, celle-ci renvoie généralement uniquement la première valeur correspondante trouvée pour un critère donné. Pourtant, de nombreuses situations courantes exigent de récupérer et de combiner **toutes** les valeurs associées à une même clé — par exemple, lister tous les élèves d’une classe ou tous les produits d’une même catégorie. Face aux limites de la fonction RECHERCHEV standard, vous vous demandez probablement comment effectuer **à la fois une recherche et une concaténation de plusieurs résultats correspondants dans une seule cellule**. Découvrez ci-dessous plusieurs méthodes pratiques et efficaces pour y parvenir, adaptées aux différentes versions d’Excel et aux préférences des utilisateurs.
Recherche verticale et concaténation de plusieurs valeurs correspondantes dans Excel
Recherche verticale 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 constitue une solution efficace, entièrement basée sur des formules, pour réaliser une recherche verticale et concaténer toutes les valeurs correspondantes. Idéale pour les jeux de données dynamiques et fréquemment mis à jour, cette approche actualise automatiquement les résultats dès que les données sources changent. Elle est particulièrement adaptée si votre version d’Excel prend en charge la fonction FILTER, disponible exclusivement dans les versions récentes d’Office.
Dans la cellule cible, saisissez la formule suivante, puis étirez-la vers le bas pour l’appliquer aux autres lignes si nécessaire. Toutes les valeurs correspondantes sont alors extraites et combinées en une seule cellule. Voir la capture d’écran :
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, «»)Cette partie de la formule examine chaque valeur dans la plage $A$2:$A$16 ; lorsqu’elle correspond à la valeur située en D2, la valeur associée dans $B$2:$B$16 est incluse dans le tableau de résultats.
- $B$2:$B$16La plage à partir de laquelle les valeurs correspondantes seront extraites.
- $A$2:$A$16=D2La condition de sélection des valeurs : seules les lignes pour lesquelles la plage $A$2:$A$16 correspond au contenu de D2 seront prises en compte.
- TEXTJOIN(", ", TRUE, ...): Cette fonction prend la sortie de la fonction FILTER (un tableau de correspondances) et les concatène en une seule chaîne de texte, séparée par le délimiteur spécifié (virgule et espace), tout en ignorant automatiquement les entrées vides.
- "," : utilise la virgule suivie d’un espace comme séparateur ; vous pouvez modifier ce symbole selon vos besoins, par exemple opter pour des points-virgules ou des sauts de ligne.
- TRUE : permet d’ignorer les cellules vides lors de la combinaison, pour un résultat proprement formaté.
Remarque importante : Cette méthode nécessite Excel 365 ou Excel 2021 et n’est pas compatible avec les versions antérieures (par exemple, Excel 2019, Excel 2016 ou plus anciennes). Vérifiez toujours votre version d’Excel avant d’appliquer cette solution.
Conseil : Si votre valeur recherchée (par exemple, D2) change ou si de nouveaux éléments correspondants sont ajoutés à la plage de données, le résultat se met à jour automatiquement, sans aucune intervention supplémentaire.
Limites potentielles : Sur des jeux de données très volumineux, le temps de calcul de la formule peut augmenter. De plus, l’utilisateur doit s’assurer qu’il n’y a aucune cellule fusionnée dans les plages de recherche ou de résultats, car cela peut entraîner des erreurs de formule.
Recherche verticale et concaténation de plusieurs valeurs correspondantes avec Kutools pour Excel
Si les méthodes intégrées vous paraissent complexes ou si votre version d’Excel ne prend pas en charge des fonctions avancées comme TEXTJOIN et FILTER, Kutools pour Excel vous offre une alternative graphique intuitive. Grâce à sa fonction **Recherche un-à-plusieurs**, vous pouvez rechercher et concaténer tous les résultats correspondants en quelques clics seulement — une solution aussi simple pour les débutants que puissante pour les utilisateurs expérimentés. Avec Kutools, fini les formules ou les codes complexes : gagnez du temps, surtout lorsque vous travaillez avec de grands jeux de données variables exigeant des recherches et 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 fenêtre, vous pouvez rapidement définir vos paramètres de recherche et de sortie en suivant les étapes suivantes :
- Sélectionnez les cellules cibles où afficher les résultats concaténés ainsi que les cellules contenant les valeurs à rechercher ;
- Indiquez la plage du tableau contenant à la fois les clés de recherche et les colonnes de résultats ;
- Précisez quelle colonne contient les clés de recherche (Colonne clé) et quelle colonne contient les valeurs à concaténer (Colonne de retour) ;
- Cliquez sur le bouton OK pour valider vos paramètres et lancer le traitement des données.
Résultat : Kutools affiche désormais toutes les valeurs correspondantes concaténées dans la cellule de sortie sélectionnée. Voir la capture d’écran :
Cette méthode est particulièrement recommandée aux utilisateurs qui préfèrent travailler directement depuis l’interface Excel, sans avoir recours à des formules ou à du code complexes. Elle minimise les risques d’erreurs liées aux formules et booste la productivité lors des tâches répétitives de recherche et de concaténation.
Recherche verticale et concaténation de plusieurs valeurs correspondantes à l’aide d’une fonction définie par l’utilisateur
Pour les utilisateurs maîtrisant VBA (Visual Basic for Applications) ou ceux qui utilisent des versions plus anciennes d’Excel ne prenant pas en charge les tableaux croisés dynamiques ni la fonction FILTRE, il est possible de créer une fonction personnalisée (UDF) permettant de concaténer de façon flexible plusieurs résultats. Cette approche est compatible avec toutes les versions d’Excel et peut être adaptée à divers séparateurs ou à des conditions spécifiques.
1. Maintenez enfoncées les touches ALT + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Cliquez sur Insertion > Module, puis collez le code suivant dans la fenêtre du module.
Code VBA : Recherche verticale 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. Sauvegardez et fermez l’éditeur VBA. Retournez à votre feuille de calcul, puis utilisez cette fonction personnalisée en saisissant la formule suivante : =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) dans une cellule vide où vous souhaitez afficher le résultat. Faites glisser la poignée de recopie vers le bas pour appliquer la formule à d’autres cellules si nécessaire. Toutes les valeurs correspondantes, identifiées à partir de la valeur recherchée, seront automatiquement regroupées et concaténées dans une seule cellule, séparées par une virgule et un espace. Voir la capture d’écran :
- D2 : La valeur à rechercher et à faire correspondre dans votre jeu de données (LookupValue).
- A2:A16 : la plage dans laquelle la fonction recherche la valeur recherchée (LookupRange).
- B2:B16 : la plage contenant les valeurs à concaténer lorsque la valeur recherchée correspond (ReturnRange).
Recherche verticale et concaténation de plusieurs valeurs correspondantes à l’aide d’un code VBA
Pour les scénarios exigeant une utilisation répétée ou pour ceux qui préfèrent éviter d’insérer des fonctions personnalisées directement dans les cellules de la feuille de calcul, une macro VBA prête à l’emploi permet de concaténer les résultats en toute simplicité. Cette approche s’avère particulièrement efficace dans les environnements partagés, où tous les utilisateurs ne disposent pas nécessairement de la même version d’Excel ni des mêmes compléments.
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. Des boîtes de dialogue s’ouvriront successivement pour vous permettre de sélectionner votre plage de données, votre plage de recherche et votre plage de résultats. Le résultat concaténé apparaîtra alors directement dans les cellules de sortie choisies.
Cette approche par macro s’avère particulièrement utile si vous effectuez fréquemment des recherches de concaténation multiples avec des valeurs différentes, car elle évite d’encombrer la feuille de calcul avec des appels à des fonctions personnalisées.
Vous pouvez facilement modifier le délimiteur directement dans le code si nécessaire, et adapter la macro pour exporter les résultats vers une cellule ou un fichier, en parfaite adéquation avec votre flux de travail.
La concaténation de plusieurs valeurs correspondantes dans Excel peut s’effectuer selon différentes approches, chacune présentant des avantages adaptés à votre contexte. Que vous choisissiez les formules de tableau dynamique, des compléments comme Kutools pour Excel ou des solutions basées sur VBA, vous renforcerez votre capacité à analyser et à présenter efficacement des données groupées. En fonction de la taille et de la complexité de votre jeu de données, privilégiez la méthode offrant les meilleures performances et la plus grande facilité de maintenance pour vous ou votre équipe. Dans vos opérations quotidiennes, veillez à la cohérence des données, évitez les cellules fusionnées et validez toujours vos plages de référence pour des résultats optimaux. En cas d’erreurs dans les calculs de formules, assurez-vous que vos plages correspondent bien à vos données et que vous utilisez la méthode de saisie adaptée à votre version d’Excel.
Pour découvrir des techniques Excel plus avancées et un large éventail de guides pratiques étape par étape, consultez notre vaste bibliothèque de tutoriels.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels