Comment vlookup et concaténer plusieurs valeurs correspondantes dans Excel?

Comme nous le savons tous, le Vlookup La fonction dans Excel peut nous aider à rechercher une valeur et à renvoyer les données correspondantes dans une autre colonne, mais en général, elle ne peut obtenir la première valeur relative que s'il existe plusieurs données correspondantes. Dans cet article, je vais expliquer comment vlookup et concaténer plusieurs valeurs correspondantes dans une seule cellule ou une liste verticale.

Vlookup et renvoyer plusieurs valeurs correspondantes verticalement avec la formule

Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule avec la fonction définie par l'utilisateur

Rechercher et concaténer plusieurs valeurs correspondantes dans une cellule avec Kutools for Excel

Vlookup et renvoyer plusieurs valeurs correspondantes verticalement avec la formule

En supposant que je dispose de la plage de données suivante, pour obtenir toutes les valeurs correspondantes en fonction d'une valeur spécifique verticalement, comme illustré ci-dessous, vous pouvez appliquer une formule matricielle.

1. Entrez cette formule: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") dans une cellule vide où vous souhaitez placer le résultat, par exemple, E2, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir la base de valeur relative sur un critère spécifique, voir capture d'écran:

Notes: Dans la formule ci-dessus:

A1: A16 est la plage de colonnes qui contient la valeur spécifique que vous souhaitez rechercher;

D2 indique la valeur spécifique que vous souhaitez vlookup;

B1: B16 est la plage de colonnes à partir de laquelle vous souhaitez renvoyer les données correspondantes;

$ 1: $ 16 indique la référence des lignes dans la plage.

2. Sélectionnez ensuite la cellule E2 et faites glisser la poignée de remplissage vers les cellules jusqu'à ce que vous obteniez des cellules vides, et toutes les valeurs correspondantes sont répertoriées dans la colonne comme illustré ci-dessous:

Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule avec la fonction définie par l'utilisateur

Au lieu d'obtenir les valeurs relatives verticalement, parfois, vous voulez les valeurs correspondantes dans une cellule et les concaténer avec un séparateur spécifique. Dans ce cas, la fonction définie par l'utilisateur suivante peut vous rendre service.

1. Maintenez la touche 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 Module Fenêtre.

Code VBA: Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Ensuite, enregistrez et fermez ce code, revenez à la feuille de calcul et entrez cette formule: = cusvlookup (D2, A1: B16,2) dans une cellule vide où vous voulez mettre le résultat, et appuyez sur Entrer clé, toutes les valeurs correspondantes basées sur des données spécifiques ont été renvoyées dans une cellule avec un séparateur d'espace, voir capture d'écran:

Notes: Dans la formule ci-dessus: D2 indique les valeurs de cellule que vous souhaitez rechercher, A1 : B16 est la plage de données dans laquelle vous souhaitez récupérer les données, le nombre 2 est le numéro de colonne à partir duquel la valeur correspondante doit être renvoyée, vous pouvez modifier ces références selon vos besoins.

Rechercher et concaténer plusieurs valeurs correspondantes dans une cellule avec Kutools for Excel

Si vous n'avez pas encore suivi Kutools for Excel, Avec son Lignes de combinaison avancées fonction, vous pouvez rapidement terminer ce travail avec facilité. Cette fonctionnalité peut vous aider à combiner toutes les valeurs correspondantes avec des délimiteurs spécifiques basés sur les mêmes données dans une autre colonne.

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

1. Sélectionnez la plage de données pour laquelle vous souhaitez obtenir les valeurs correspondantes en fonction des données spécifiques.

2. Puis clique Kutools > Fusionner et fractionner > Lignes de combinaison avancées, voir capture d'écran:

3. Dans le Lignes de combinaison avancées boîte de dialogue, cliquez sur le nom de la colonne sur laquelle vous souhaitez combiner, puis cliquez sur Clé primaire bouton, voir capture d'écran:

4. Cliquez ensuite sur un autre nom de colonne pour lequel vous souhaitez renvoyer les valeurs correspondantes, puis cliquez sur Combiner pour choisir un séparateur pour séparer les valeurs combinées, voir capture d'écran:

5. Et puis cliquez Ok bouton, toutes les valeurs correspondantes basées sur les mêmes valeurs ont été combinées avec un séparateur spécifique, voir les captures d'écran:

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

Démo : rechercher et concaténer plusieurs valeurs correspondantes dans une cellule avec Kutools for Excel