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
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 avez 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.
| Kutools for Excel : avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation en 30 jours. |
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
Meilleurs outils de productivité bureautique
Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la 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 facilite grandement votre travail
- Activer 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 réduit des centaines de clics de souris chaque jour!
