Comment rechercher une valeur renvoyant plusieurs valeurs correspondantes dans Excel?

Ce didacticiel parle de rechercher une valeur renvoyant plusieurs valeurs correspondantes dans Excel, comme illustré ci-dessous les captures d'écran:

La valeur de recherche renvoie plusieurs valeurs correspondantes avec une formule matricielle

La valeur de recherche renvoie plusieurs valeurs correspondantes avec le filtre

La valeur de recherche renvoie plusieurs valeurs correspondantes avec la fonction définie

La valeur de recherche renvoie plusieurs valeurs correspondantes avec une formule matricielle

Voici une formule de tableau long qui peut aider à rechercher une valeur et renvoyer plusieurs valeurs correspondantes.

1. Entrez la valeur que vous souhaitez rechercher dans une cellule vide. Voir la capture d'écran:

2. Dans la cellule adjacente, saisissez cette formule =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)) dedans, et appuyez sur Maj + Ctrl + Entrée touches ensemble, puis faites glisser Remplissage automatique pour remplir les cellules jusqu'à ce que la première cellule vide sorte. Voir la capture d'écran:

Remarque: dans la formule ci-dessus, $ A $ 1: $ B $ 7 indique les données de plage, $ A $ 1: $ A $ 7 représente la plage de colonnes dans laquelle votre recherche de la valeur donnée, $ D $ 4 indique la cellule dans laquelle vous tapez la recherche valeur à l'étape 1, 2 indique la recherche des valeurs correspondantes dans la deuxième colonne.

La valeur de recherche renvoie plusieurs valeurs correspondantes avec le filtre

Dans Excel, vous pouvez également utiliser la fonction Filtre pour résoudre ce problème.

1. Sélectionnez la plage de colonnes dont vous souhaitez rechercher la valeur, puis cliquez sur Sauvegarde de > Filtre. Voir la capture d'écran:

2. Cliquez ensuite sur le bouton fléché 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 la capture d'écran:

3. Cliquez OK, maintenant vous ne voyez que la valeur de recherche et ses valeurs correspondantes sont filtrées.

La valeur de recherche renvoie plusieurs valeurs correspondantes avec la fonction définie

Si vous êtes intéressé par la fonction définie, vous pouvez également résoudre le problème avec la fonction définie.

1. presse Alt + F11 clés pour ouvrir le Microsoft Visual Basic pour les applications fenêtre.

2. Cliquez Module > insérer pour insérer un Module et copiez le VBA ci-dessous dans la fenêtre.

VBA: la valeur de recherche renvoie 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 de recherche, $ A $ 2: $ B $ 7 indique la plage de données, 2 indique le numéro d'index de la colonne). Et appuyez sur Maj + Ctrl + Entrée clés. Faites ensuite glisser la poignée de recopie vers le bas des cellules, placez le curseur dans le Barre de formuleet appuyez sur Maj + Ctrl + Entrée nouveau.

Conseil : Si vous souhaitez renvoyer les valeurs dans des cellules horizontales, vous pouvez taper cette formule = MyVlookup (A10, $ A $ 2: $ B $ 7, 2, "h").