Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

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 y a plusieurs données correspondantes. Dans cet article, je vais parler de comment rechercher et concaténer plusieurs valeurs correspondantes dans une seule cellule ou une liste verticale.

Vlookup et retourner 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


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

Avec Kutools for Excel's Lignes de combinaison avancées Utiltiy, vous pouvez rapidement combiner plusieurs lignes dupliquées en un seul enregistrement basé sur des colonnes clés, et il peut également appliquer certains calculs tels que somme, moyenne, nombre et ainsi de suite pour les autres colonnes.

  • 1. Spécifiez la colonne clé que vous souhaitez combiner d'autres colonnes en fonction de;
  • 2. Choisissez le séparateur pour vos données combinées.

doc combine le texte basé sur les critères 10

Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!



Supposons que j'ai 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.

doc vlookup concaténer 1

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 voulez mettre 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:

doc vlookup concaténer 2

Note: Dans la formule ci-dessus:

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

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

B1: B16 est la plage de colonnes à partir de laquelle vous voulez 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 le bas jusqu'aux cellules jusqu'à ce que vous obteniez des cellules vides, et toutes les valeurs correspondantes sont répertoriées dans la colonne comme suit capture d'écran:

doc vlookup concaténer 3


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 20151118
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 une donnée spécifique ont été retournées dans une cellule avec un séparateur d'espace, voir capture d'écran:

doc vlookup concaténer 4

Note: Dans la formule ci-dessus: D2 indique les valeurs de cellule que vous voulez rechercher, A1: B16 est la plage de données que vous voulez 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 à votre besoin.


Si vous avez Kutools for Excel, Avec son Lignes de combinaison avancées fonctionnalité, vous pouvez rapidement terminer ce travail avec facilité. Cette fonctionnalité peut vous aider à combiner toutes les valeurs correspondantes avec un délimiteur spécifique basé sur les mêmes données dans une autre colonne.

Kutools for Excel : avec plus de 300 compléments Excel pratiques, libre d'essayer sans limitation dans 60 jours.

Après l'installation de Kutools for Excel, s'il vous plaît faites 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 > Contenu > Lignes de combinaison avancées, voir capture d'écran:

doc vlookup concaténer 5

3. Dans le Lignes de combinaison avancées Cliquez sur le nom de la colonne que vous souhaitez combiner en fonction de, puis cliquez sur Clé primaire bouton, voir capture d'écran:

doc vlookup concaténer 6

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

doc vlookup concaténer 7

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:

doc vlookup concaténer 8  2 doc vlookup concaténer 9

Télécharger et tester gratuitement Kutools for Excel maintenant!


Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. Téléchargez et essai gratuit maintenant!



Outils de productivité recommandés

Office Tab

étoile d'or1 Apportez des onglets pratiques à Excel et à d'autres logiciels Office, tout comme Chrome, Firefox et Internet Explorer.

Kutools for Excel

étoile d'or1 Incroyable! Augmentez votre productivité dans les minutes 5. Ne nécessite pas de compétences particulières, économisez deux heures par jour!

étoile d'or1 300 Nouvelles fonctionnalités pour Excel, rendent Excel facile et puissant:

  • Fusionner des cellules / lignes / colonnes sans perdre de données.
  • Combiner et consolider plusieurs feuilles et classeurs.
  • Comparez les gammes, copiez plusieurs gammes, convertissez le texte en date, l'unité et la conversion de devise.
  • Compter par couleurs, sous-totaux de recherche, tri avancé et super filtre,
  • Plus Sélectionner / Insérer / Supprimer / Texte / Format / Lier / Commenter / Classeurs / Feuilles de calcul Outils ...

Capture d'écran de Kutools pour Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Russell · 23 days 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 |
  • To post as a guest, your comment is unpublished.
    jeff · 2 months 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)," "," / ")
    • To post as a guest, your comment is unpublished.
      skyyang · 1 months ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 9 months 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!!
  • To post as a guest, your comment is unpublished.
    Jacob Nelson · 1 years ago
    Is there a way to delete the duplicate values in the concatenate?
  • To post as a guest, your comment is unpublished.
    Sree Kanth · 1 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