Excel - ¿Cómo vlookup para devolver múltiples valores?

Asumiendo que quieres un enfoque de fórmula como se ha dicho (sin usar VLOOKUP, pero aún así una fórmula), así es como he dispuesto los datos:

He usado entonces la siguiente fórmula en la celda 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))

Esta es una fórmula de matriz, así que después de copiarla y pegarla en la celda tienes que pulsar Ctrl+Shift+Enter. Luego la arrastré a la derecha y al fondo.

Si no hay ningún valor(es) restante da el error #NUM!, di un ejemplo para el amarillo en el ejemplo de la imagen cargada.

Creo que un enfoque VBA/Macro sería una mejor solución si tienes toneladas de filas.

1. Intercambien las columnas de modo que los colores estén en la columna A y los nombres en la columna B, y luego ordenen el color.

2. Fórmula en C2 (cópialo en la columna): =IF(A2<>A1,B2,C1 & “, ” & B2)

3. Fórmula en D2 (cópialo en la columna): =A2 <>A3

4. Filtra “VERDADERO” en la columna D para obtener los resultados deseados. Ver más abajo:

Aquí está la solución de VBA para usted. Primero, así es como se ven los resultados:

Y aquí está el código:

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 quieres un enfoque de fórmula entonces es mucho más simple obtener los resultados en celdas separadas, así que asumamos que tu primera tabla es A2:B8 y los colores están listados de nuevo en D2:D5. Prueba esta fórmula en 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))),"")

confirmada con CTRL+SHIFT+ENTER y copiada a lo largo y a lo ancho. Cuando se agotan los partidos se obtienen espacios en blanco.

La fórmula asume Excel 2007 o posterior - si la versión anterior se puede usar COUNTIF en lugar de IFERROR, es decir

=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))))