Comment rechercher une valeur et retourner plusieurs valeurs correspondantes dans Excel ?
Ce tutoriel explique comment rechercher une valeur et retourner plusieurs valeurs correspondantes dans Excel, comme le montrent les captures d'écran ci-dessous :
Rechercher une valeur et retourner plusieurs valeurs correspondantes avec une formule matricielle
Rechercher une valeur et retourner plusieurs valeurs correspondantes avec Filtre
Rechercher une valeur et retourner plusieurs valeurs correspondantes avec une fonction définie
Rechercher une valeur et retourner plusieurs valeurs correspondantes avec une formule matricielle
Voici une longue formule matricielle qui peut vous aider à rechercher une valeur et retourner plusieurs valeurs correspondantes.
1. Entrez la valeur que vous souhaitez rechercher dans une cellule vide. Voir capture d'écran :
2. Dans la cellule adjacente, tapez cette formule =SI(ESTERREUR(INDEX($A$1:$B$7,PETITE.VALEUR(SI($A$1:$A$7=$D$4,LIGNE($A$1:$A$7)),LIGNE(1:1)),2)),"",
INDEX($A$1:$B$7,PETITE.VALEUR(SI($A$1:$A$7=$D$4,LIGNE($A$1:$A$7)),LIGNE(1:1)),2)) dedans, puis appuyez sur Maj + Ctrl + Entrée ensemble, puis faites glisser le Recopie automatique pour remplir vers le bas les cellules jusqu'à ce que la première cellule vide apparaisse. Voir capture d'écran :
Remarque : Dans la formule ci-dessus, $A$1:$B$7 indique la plage de données, $A$1:$A$7 représente la plage de colonnes dans laquelle vous recherchez une certaine valeur, $D$4 indique la cellule où vous avez tapé la valeur de recherche à l'étape 1, 2 indique la recherche des valeurs correspondantes dans la deuxième colonne.
Rechercher une valeur et retourner plusieurs valeurs correspondantes avec Filtre
Dans Excel, vous pouvez également utiliser la fonction Filtre pour résoudre ce problème.
1. Sélectionnez la plage de colonnes dans laquelle vous souhaitez rechercher une valeur, puis cliquez sur Données > Filtre. Voir capture d'écran :
2. Cliquez ensuite sur le bouton flèche dans la première cellule de la plage sélectionnée, et cochez la valeur que vous souhaitez rechercher uniquement dans la liste déroulante. Voir capture d'écran :
3. Cliquez sur OK, maintenant vous voyez que seule la valeur recherchée et ses valeurs correspondantes sont filtrées.
Rechercher une valeur et retourner plusieurs valeurs correspondantes avec une fonction définie
Si vous êtes intéressé par les fonctions définies, vous pouvez également résoudre le problème avec une fonction définie.
1. Appuyez sur Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Cliquez sur Module > Insérer pour insérer une fenêtreModule, et copiez le code VBA ci-dessous dans la fenêtre.
VBA : Rechercher une valeur et retourner plusieurs valeurs correspondantes.
Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
If pWorkRng = pRng.Cells(i, 1) Then
arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
ReDim Preserve arr(UBound(arr) + 1)
End If
Next
If pType = "h" Then
xCol = Range(Application.Caller.Address).Columns.Count
For i = UBound(arr) To xCol
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function
3. Fermez la fenêtre, et tapez cette formule dans une cellule =MyVlookup(A10,$A$2:$B$7,2) (A10 indique la valeur recherchée, $A$2:$B$7 indique la plage de données, 2 indique le numéro d'index de la colonne). Appuyez sur Maj + Ctrl + Entrée. Ensuite, faites glisser la poignée de recopie vers le bas des cellules, placez le curseur dans la Barre de formule, et appuyez à nouveau sur Maj + Ctrl + Entrée.
Astuce : Si vous souhaitez retourner les valeurs dans des cellules horizontales, vous pouvez taper cette formule =MyVlookup(A10, $A$2:$B$7, 2, "h").
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels