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").