Note: The other languages of the website are Google-translated. Back to English

Comment vlookup pour renvoyer plusieurs valeurs dans une cellule dans Excel?

Normalement, dans Excel, lorsque vous utilisez la fonction RECHERCHEV, s'il existe plusieurs valeurs correspondant aux critères, vous pouvez simplement obtenir la première. Mais, parfois, vous souhaitez renvoyer toutes les valeurs correspondantes qui répondent aux critères dans une cellule comme illustré ci-dessous, comment pouvez-vous le résoudre?

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction TEXTJOIN (Excel 2019 et Office 365)

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction définie par l'utilisateur

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec une fonctionnalité utile


Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction TEXTJOIN (Excel 2019 et Office 365)

Si vous disposez de la version supérieure d'Excel telle que Excel 2019 et Office 365, il existe une nouvelle fonction - TEXTJOIN, avec cette fonction puissante, vous pouvez rapidement vlookup et renvoyer toutes les valeurs correspondantes dans une cellule.

Vlookup pour renvoyer toutes les valeurs correspondantes dans une cellule

Veuillez appliquer la formule ci-dessous dans une cellule vide où vous souhaitez mettre le résultat, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le premier résultat, puis faites glisser la poignée de remplissage vers la cellule dans laquelle vous souhaitez utiliser cette formule, et vous obtiendrez toutes les valeurs correspondantes, comme illustré ci-dessous:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Remarque : Dans la formule ci-dessus, A2: A11 la plage de recherche contient-elle les données de recherche, E2 est la valeur de recherche, C2: C11 est la plage de données à partir de laquelle vous souhaitez renvoyer les valeurs correspondantes, ","est le séparateur pour séparer les enregistrements multiples.

Vlookup pour renvoyer toutes les valeurs correspondantes sans doublons dans une cellule

Si vous souhaitez renvoyer toutes les valeurs correspondantes basées sur les données de recherche sans doublons, la formule ci-dessous peut vous aider.

Veuillez copier et coller la formule suivante dans une cellule vide, puis appuyez sur Ctrl + Maj + Entrée clés ensemble pour obtenir le premier résultat, puis copiez cette formule pour remplir d'autres cellules, et vous obtiendrez toutes les valeurs correspondantes sans les dulpiquées, comme illustré ci-dessous:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Remarque : Dans la formule ci-dessus, A2: A11 la plage de recherche contient-elle les données de recherche, E2 est la valeur de recherche, C2: C11 est la plage de données à partir de laquelle vous souhaitez renvoyer les valeurs correspondantes, ","est le séparateur pour séparer les enregistrements multiples.

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec la fonction définie par l'utilisateur

La fonction TEXTJOIN ci-dessus n'est disponible que pour Excel 2019 et Office 365, si vous avez d'autres versions d'Excel inférieures, vous devez utiliser certains codes pour terminer cette tâche.

Vlookup pour renvoyer toutes les valeurs correspondantes dans une cellule

1. Maintenez le ALT + F11 clés, et il ouvre le Microsoft Visual Basic pour applications fenêtre.

2Cliquez sur insérer > Moduleet collez le code suivant dans le Fenêtre du module.

Code VBA: Vlookup pour renvoyer plusieurs valeurs dans une cellule

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Ensuite, enregistrez et fermez ce code, revenez à la feuille de calcul et entrez cette formule: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") dans une cellule vide spécifique où vous souhaitez placer le résultat, puis faites glisser la poignée de recopie vers le bas pour obtenir toutes les valeurs correspondantes dans une cellule souhaitée, voir capture d'écran:

Remarque : Dans la formule ci-dessus, A2: A11 la plage de recherche contient-elle les données de recherche, E2 est la valeur de recherche, C2: C11 est la plage de données à partir de laquelle vous souhaitez renvoyer les valeurs correspondantes, ","est le séparateur pour séparer les enregistrements multiples.

Vlookup pour renvoyer toutes les valeurs correspondantes sans doublons dans une cellule

Pour ignorer les doublons dans les valeurs correspondantes renvoyées, veuillez utiliser le code ci-dessous.

1. Maintenez le 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 Fenêtre du module.

Code VBA: Vlookup et retourne plusieurs valeurs correspondantes uniques dans une cellule

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Après avoir inséré le code, cliquez sur Outils > Références en plein air Microsoft Visual Basic pour applications fenêtre, puis, dans le sauté Références - VBAProject boîte de dialogue, cochez Exécution de scripts Microsoft option dans la Références disponibles zone de liste, voir les captures d'écran:

4. Puis clique OK pour fermer la boîte de dialogue, enregistrez et fermez la fenêtre de code, revenez à la feuille de calcul et entrez cette formule: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

Remarque : Dans la formule ci-dessus, A2: C11 est la plage de données que vous souhaitez utiliser, E2 est la valeur de recherche, le nombre 3 est le numéro de colonne contenant les valeurs renvoyées.

Vlookup pour renvoyer plusieurs valeurs dans une cellule avec une fonctionnalité utile

 Si vous avez notre Kutools pour Excel, Avec son Lignes de combinaison avancées fonctionnalité, vous pouvez rapidement fusionner ou combiner les lignes en fonction de la même valeur et effectuer certains calculs selon vos besoins.

Remarque :Appliquer cette Lignes de combinaison avancées, tout d'abord, vous devez télécharger le Kutools pour Excel, puis appliquez la fonction rapidement et facilement.

Après l'installation de Kutools pour Excel, procédez comme suit:

1. Sélectionnez la plage de données dans laquelle vous souhaitez combiner les données d'une colonne en fonction d'une autre colonne.

2Cliquez sur Kutools > Fusionner et fractionner > Lignes de combinaison avancées, voir capture d'écran:

3. Dans le sauté Lignes de combinaison avancées boite de dialogue:

  • Cliquez sur le nom de la colonne clé à combiner en fonction, puis cliquez sur Clé primaire
  • Cliquez ensuite sur une autre colonne pour laquelle vous souhaitez combiner ses données en fonction de la colonne clé, puis cliquez sur Combiner pour choisir un séparateur pour séparer les données combinées.

4. Puis clique OK bouton, et vous obtiendrez les résultats suivants:

Téléchargez et essayez gratuitement Kutools for Excel Now!


Articles plus relatifs:

  • Fonction RECHERCHEV avec quelques exemples basiques et avancés
  • Dans Excel, la fonction RECHERCHEV est une fonction puissante pour la plupart des utilisateurs d'Excel, qui est utilisée pour rechercher une valeur dans la partie la plus à gauche de la plage de données et renvoyer une valeur correspondante dans la même ligne à partir d'une colonne que vous avez spécifiée. Ce didacticiel explique comment utiliser la fonction RECHERCHEV avec quelques exemples de base et avancés dans Excel.
  • Renvoie plusieurs valeurs correspondantes basées sur un ou plusieurs critères
  • Normalement, rechercher une valeur spécifique et renvoyer l'élément correspondant est facile pour la plupart d'entre nous en utilisant la fonction RECHERCHEV. Mais avez-vous déjà essayé de renvoyer plusieurs valeurs correspondantes basées sur un ou plusieurs critères? Dans cet article, je présenterai quelques formules pour résoudre cette tâche complexe dans Excel.
  • Vlookup et renvoyer plusieurs valeurs verticalement
  • Normalement, vous pouvez utiliser la fonction Vlookup pour obtenir la première valeur correspondante, mais, parfois, vous souhaitez renvoyer tous les enregistrements correspondants en fonction d'un critère spécifique. Cet article, je vais parler de la façon de vlookup et de renvoyer toutes les valeurs correspondantes verticalement, horizontalement ou dans une seule cellule.
  • Vlookup et renvoyer plusieurs valeurs à partir de la liste déroulante
  • Dans Excel, comment pouvez-vous rechercher et renvoyer plusieurs valeurs correspondantes à partir d'une liste déroulante, ce qui signifie que lorsque vous choisissez un élément dans la liste déroulante, toutes ses valeurs relatives sont affichées en même temps. Cet article, je vais vous présenter la solution étape par étape.

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é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 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.
onglet kte 201905

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!
bas de cabine
Commentaires (43)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Comment ajusterais-je cette formule pour séparer chaque valeur renvoyée mais ", " ainsi que pour ne renvoyer que des valeurs uniques ?
Ce commentaire a été minimisé par le modérateur sur le site
Merci pour le code !!

En ce qui concerne les caractères génériques, un moyen de contourner utilise INSTR

Vous pouvez remplacer [ If rng = pValue Then ] par [ InStr(1, rng.Value, pValue) Then ] et si vous ne voulez pas qu'il soit sensible à la casse, utilisez [ InStr(1, rng.Value, pValue, vbTextCompare) Alors ]
Ce commentaire a été minimisé par le modérateur sur le site
Merci pour le code VBA ci-dessus. Pouvez-vous me dire comment faire entrer les résultats sur une nouvelle ligne dans la cellule, c'est-à-dire comme Alt-Entrée 300 400 1000 1300
Ce commentaire a été minimisé par le modérateur sur le site
Merci d'avoir partagé le code ci-dessus. Je l'utilise depuis plusieurs mois maintenant mais aujourd'hui cela ne semble pas fonctionner. Je reçois des cellules vides au lieu de l'erreur habituelle lorsqu'il y a des données à renvoyer. Des pensées?
Ce commentaire a été minimisé par le modérateur sur le site
Travail génial.. J'ai exactement ce que je veux !!! Aimer !!
Ce commentaire a été minimisé par le modérateur sur le site
Salut, je suis vraiment impressionné par le travail et c'est si facile d'en créer un pour utiliser cette fonction. cependant j'ai besoin de plus de soutien. Mon ? est-ce que comment puis-je sélectionner un nombre dans une cellule avec plusieurs cellules dans mon tableau vlookup. c'est-à-dire si la cellule A1 = 100, A2 = 350, A3 = 69 C1 = 100 ; 1222 ; 12133 C2 = 69 ; 222 D1 = Pomme D2 = banane Alors, comment sélectionner 100 dans la colonne C de mon tableau de tableau pour dériver le correspondant D1 = pomme Veuillez noter que j'ai des nombres à 7 chiffres dans ma valeur de recherche et mon tableau de tableau qui sont séparés par un ";". J'apprécierais vraiment si vous pouviez résoudre ce problème et m'aider à gagner beaucoup de temps.
Ce commentaire a été minimisé par le modérateur sur le site
Merci pour le code VBA. J'ai exactement ce que je veux ! J'ai modifié uniquement le code " rng.Offset(0, pIndex - 1) " en " rng.Offset(0, pIndex - 2) " . Ainsi, MYVLOOKUP est capable de rechercher de droite à gauche.
Ce commentaire a été minimisé par le modérateur sur le site
C'est exactement ce que je cherchais et je ne pensais pas simplement créer ma propre UDF. Cependant, il ne fonctionnera pas exactement comme VLOOKUP. Si la chaîne que vous recherchez n'est pas seulement dans la première colonne, elle pourrait vous donner des données en dehors de la plage d'origine transmise. Nom Numéro Autre nom Colonne hors plage passée Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Si le tableau ci-dessus était composé de cellules A1:D7 si vous avez réussi uniquement A1:C7 votre La fonction "MYVLOOKUP" renvoie 1 1 2 2 5 5 alors que vous vous attendriez à ce qu'elle renvoie 1 2 5. Les modifications ci-dessous résolvent le problème : /20150310/6 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 9 To Rows If pWorkRng.Cells(i, 16). Valeur = pValue Then xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(1, pIndex - 1) End If Next i 'For Each rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If ' Next MYVLOOKUP = xResult End Function
Ce commentaire a été minimisé par le modérateur sur le site
Cela fonctionne très bien, mais j'ai besoin d'aide avec la commande pour supprimer les doublons des résultats. Sérieusement, excellent travail.
Ce commentaire a été minimisé par le modérateur sur le site
Cela fonctionne très bien, mais j'ai toujours besoin d'aide avec la fonction de commande pour supprimer les doublons des résultats.
Ce commentaire a été minimisé par le modérateur sur le site
Notifiez-moi des nouveaux commentaires
Ce commentaire a été minimisé par le modérateur sur le site
Ne rien retourner ! après avoir appliqué MYLOOKUP ne donnant aucun résultat mais vide.
Ce commentaire a été minimisé par le modérateur sur le site
Salut, ça marche bien. Ce que je voudrais faire, c'est adapter le code pour séparer les résultats de valeur avec "///" ou tout autre marqueur (pour des raisons techniques, je ne veux pas qu'un seul caractère séparateur). De plus, j'ai remarqué que cette formule ne fonctionne pas avec un joker. Je sais que je demande trop, mais cela ne fonctionne pas car vlookup peut fonctionner lorsque je recherche =myvlookup("*"&E6&"*",$A$2:$C$15,2) ce qu'il ferait/pourrait faire. Une aide ?
Ce commentaire a été minimisé par le modérateur sur le site
La tête haute. J'ai compris comment obtenir un séparateur dans cette sortie. C'est rudimentaire. Mais je l'ai compris. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) La dernière chose, et la plus souhaitée, est de lui permettre de travailler avec des caractères génériques dans les critères de recherche. Merci encore pour cette belle et brillante solution. Extrêmement utile. Maintenant, je veux juste que la macro s'exécute et soit installée dans mon Excel de manière permanente, peu importe ce que je fais, afin que je puisse l'utiliser quand j'en ai besoin. Et les jokers ! Merci beaucoup. Les jokers sont tout ce qui reste à faire.
Ce commentaire a été minimisé par le modérateur sur le site
Pour obtenir un enregistrement unique, vous pouvez utiliser ci-dessous : (modifié par référence à un autre code utilisateur) Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," For Each varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Alors sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) End Function
Ce commentaire a été minimisé par le modérateur sur le site
Cela a parfaitement fonctionné, mais il m'a fallu un certain temps pour que la fonction fonctionne correctement dans ma feuille de calcul de 20 onglets et 50 XNUMX lignes. Maintenant, la GRANDE question est de savoir comment prendre cette chaîne délimitée, puis utiliser chaque entrée comme valeur de recherche Index/Match (non mariée à Index/Match, mais cela semble plus rapide) dans un autre ensemble de données, renvoyant la valeur SOMME de tous les retours dans une cellule . Mon scénario est que j'ai une commande unique qui a plusieurs factures. Votre fonction MYVLOOKUP fonctionne parfaitement pour rapporter toutes les factures dans une cellule. Ce que je veux faire maintenant, c'est prendre chaque retour concaténé avec la cellule signalée, parcourir ce tableau et totaliser les montants de paiement de chaque facture dans la cellule de formule. J'apprécie toute aide que vous pouvez offrir à ce sujet et merci pour la fonction MYVLOOKUP !
Ce commentaire a été minimisé par le modérateur sur le site
Peu importe ce que je fais, j'obtiens toujours de la #valeur ! retourné au lieu d'un résultat. vlookup fonctionne très bien, donc les données fonctionnent. Déjà suivi le processus d'activation des macros. J'ai même tout combiné en une seule feuille. Des idées??
Ce commentaire a été minimisé par le modérateur sur le site
Super macro, utile. Mais besoin de savoir peut-il être modifié pour vérifier 2 critères et quelqu'un a-t-il trouvé de toute façon pour faire fonctionner les caractères génériques dessus. De l'aide?
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen de modifier le résultat afin qu'au lieu d'afficher 1000 1000 -1000, il affiche par exemple 1,000 1,000/1,000 XNUMX/(XNUMX XNUMX) ?
Ce commentaire a été minimisé par le modérateur sur le site
Excellente fonction, mais parcourir 100,000 XNUMX enregistrements s'avère un peu trop pour mon pauvre ordinateur portable, il faudra le laisser fonctionner pendant la nuit !
Ce commentaire a été minimisé par le modérateur sur le site
C'est incroyable, merci !
Il n'y a pas encore de commentaires postés ici
TÉLÉCHARGER PLUS
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés