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 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.
Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule avec Kutools for Excel
Si vous avez Kutools pour 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 pour Excel : avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation en 30 jours. |
Après l'installation de Kutools pour 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: Vlookup et concaténer plusieurs valeurs correspondantes dans une cellule avec Kutools for Excel
Les meilleurs outils de productivité de bureau
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité en
80 %
- Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
- Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtréeplus
- Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammesplus
- Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
- Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
- Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellulesplus
- Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
- Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDFplus
- Plus que
300Caractéristiques puissantes. Prend en charge Office/Excel2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes30-jour d'essai gratuit. Garantie de remboursement de 60 jours.
Office Tab apporte une interface à onglets à Office et simplifie considérablement 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é en
50 %, et réduit chaque jour des centaines de clics de souris !
