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!


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 emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • 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 en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture 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 le nombre de clics de souris pour vous chaque jour!
fond officetab
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.
    sym-john · 3 months ago
    Is there any way to get the unique "name" for "class1"
  • To post as a guest, your comment is unpublished.
    Russell · 7 months 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 F · 3 months 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.
  • To post as a guest, your comment is unpublished.
    jeff · 8 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 · 8 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 · 1 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!!
  • 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
  • To post as a guest, your comment is unpublished.
    Brett Bieker · 2 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
    • To post as a guest, your comment is unpublished.
      Anand · 1 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
  • To post as a guest, your comment is unpublished.
    slohman · 3 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
  • To post as a guest, your comment is unpublished.
    Deepan Saha · 3 years ago
    How to get the result. Please help.

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