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

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

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:

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.

2. Cliquez 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.

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.

Aller au téléchargement
Essai gratuit 30 jours Je m'abonne!
PayPal / Mon commerce

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!

Kutools pour Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!

Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 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ée...
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 gammes...
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 cellules...
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 PDF...
Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.

Lire la suite... Téléchargement Gratuit... Achat...

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é de 50% et réduit des centaines de clics de souris chaque jour!

Lire la suite... Téléchargement Gratuit... Achat...

16 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
sym-john · 2 years ago
Is there any way to get the unique "name" for "class1"
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hello, sym-john,
  Maybe the below article can solve your problem, please view it:
  https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
  
  Reply
To post as a guest, your comment is unpublished.
Russell · 2 years ago
This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

E.g:

Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
- Reply
- To post as a guest, your comment is unpublished.
  Jeff F · 2 years ago
  
  you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
  
  Reply
To post as a guest, your comment is unpublished.
jeff · 2 years ago
The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 2 years ago
  
  Hi, jeff,
  Thanks for your sharing, you must be a warmhearted man.
  
  Reply
To post as a guest, your comment is unpublished.
Dana Rohde · 3 years ago
I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

Thank you so much for your help!!
- Reply
To post as a guest, your comment is unpublished.
Jacob Nelson · 3 years ago
Is there a way to delete the duplicate values in the concatenate?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hello, Jacob,
  May be the following article can help you to solve your problem.
  https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
  
  Please try, hope it can help you!
  
  Reply
  
  To post as a guest, your comment is unpublished.
  carmela · 2 years ago
  
  Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Jerry · 9 months ago
  
  you can add two extra condition to skip blank cells and to skip duplicates:
  For i = 1 To CriteriaRange.Count
  If CriteriaRange.Cells(i).Value = Condition Then
  If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
  If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
  xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
  End If
  End If
  End If
  Next i
  
  Reply
To post as a guest, your comment is unpublished.
sreetechnical@gmail.com · 3 years ago
This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
- Reply
To post as a guest, your comment is unpublished.
Brett Bieker · 4 years ago
Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values

Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :)

Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long)
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
If rng = pValue Then
xResult = xResult & ";" & rng.Offset(0, pIndex - 1)
If Left(xResult, 1) = ";" Then
xResult = MID(xResult,2,255)
End If
End If
Next
CusVlookup = xResult
End Function
- Reply
- To post as a guest, your comment is unpublished.
  Anand · 3 years ago
  
  Make if condition for result if empty.
  
  Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
  'updateby Extendoffice 20151118
  Dim x As Range
  Dim result As String
  result = ""
  For Each x In lookuprange
  If x = lookupval Then
  If Not result = "" Then
  result = result & " " & x.Offset(0, indexcol - 1)
  Else
  result = x.Offset(0, indexcol - 1)
  End If
  Next x
  CusVlookup = result
  End Function
  
  Reply
To post as a guest, your comment is unpublished.
slohman · 5 years ago
When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
- Reply
To post as a guest, your comment is unpublished.
Deepan Saha · 5 years ago
How to get the result. Please help.

data data1 result
a 1 a1
b 2 a2
c b1
b2
c1
c2
- Reply

Connectez-vous à votre compte

Réinitialiser mot de passe