Excel - Comment rechercher pour retourner des valeurs multiples ?

En supposant que vous voulez une approche par formule comme indiqué (sans utiliser VLOOKUP, mais toujours une formule), voici comment j'ai présenté les données :

J'ai ensuite utilisé la formule suivante dans la cellule C12 :

=INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), 1))

Il s'agit d'une formule de tableau, donc après l'avoir copiée et collée dans la cellule, vous devez appuyer sur Ctrl+Shift+Enter. Je l'ai ensuite simplement déplacée vers la droite et vers le bas.

S'il ne reste aucune valeur, cela donne l'erreur #NUM!, j'ai donné un exemple pour le jaune dans l'exemple d'image téléchargée.

Je pense qu'une approche VBA/Macro serait une meilleure solution si vous avez des tonnes de lignes.

1. Échangez les colonnes de sorte que les couleurs soient dans la colonne A et les noms dans la colonne B, puis triez sur la couleur.

2. Formule en C2 (à copier dans la colonne) : =IF(A2<>A1,B2,C1 & “, ” & B2)

3. Formule en D2 (à recopier dans la colonne) : =A2<>A3

4. Filtrez pour “VRAI” sur la colonne D pour obtenir les résultats souhaités. Voir ci-dessous :

Voici la solution VBA pour vous. Tout d'abord, voici à quoi ressemblent les résultats :

Et voici le code :

Option Explicit
Function LookupCSVResults(lookupValue As Variant, lookupRange As Range, resultsRange As Range) As String

    Dim s As String 'Results placeholder
    Dim sTmp As String 'Cell value placeholder
    Dim r As Long 'Row
    Dim c As Long 'Column
    Const strDelimiter = "|||" 'Makes InStr more robust

    s = strDelimiter
    For r = 1 To lookupRange.Rows.Count
        For c = 1 To lookupRange.Columns.Count
            If lookupRange.Cells(r, c).Value = lookupValue Then
                'I know it's weird to use offset but it works even if the two ranges
                'are of different sizes and it's the same way that SUMIF works
                sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
                If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                    s = s & sTmp & strDelimiter
                End If
            End If
        Next
    Next

    'Now make it look like CSV
    s = Replace(s, strDelimiter, ",")
    If Left(s, 1) = "," Then s = Mid(s, 2)
    If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

    LookupCSVResults = s 'Return the function

End Function

Si vous souhaitez une approche par formule, il est beaucoup plus simple d'obtenir les résultats dans des cellules séparées. Supposons donc que votre premier tableau soit A2:B8 et que les couleurs soient à nouveau répertoriées dans D2:D5. Essayez cette formule dans E2

=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,ROW($B$2:$B$8)-ROW($B$2)+1),COLUMNS($E2:E2))),"")

confirmée par CTRL+SHIFT+ENTER et copiée de part et d'autre. Lorsque les correspondances sont épuisées, vous obtenez des blancs. La formule

suppose Excel 2007 ou une version ultérieure - si une version antérieure, vous pouvez utiliser COUNTIF au lieu de IFERROR, c'est-à-dire

=IF(COLUMNS($E2:E2)>COUNTIF($B$2:$B$8,$D2),"",INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,ROW($B$2:$B$8)-ROW($B$2)+1),COLUMNS($E2:E2))))