2013-01-14 21:43:56 +0000 2013-01-14 21:43:56 +0000
20
20

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

Estoy buscando usar Excel para buscar y devolver múltiples valores de referencia para una clave dada. VLookup hace algo muy similar a lo que necesito - pero sólo devuelve una única coincidencia.

Asumo que implicará métodos de devolución y manejo de arreglos, aunque no he tratado con estos antes. Algunos Google empiezan a apoyarse en el if([lookuparray]=[valor],row[lookuparray]) como parte de una solución, aunque no puedo conseguir que devuelva una única coincidencia…

Por ejemplo, si tengo estos datos de referencia: 

Adam Red
Adam Green
Adam Blue
Bob Red
Bob Yellow
Bob Green
Carl Red

Intento conseguir los múltiples valores de retorno de la derecha. (separados por comas, si es posible) 

Red Adam, Bob, Carl
Green Adam, Bob
Blue Adam
Yellow Bob

(Ya tengo el valor clave a la izquierda - no hay necesidad de sacar esos valores)

Se aprecia cualquier ayuda sobre cómo abordar el manejo de los valores múltiples en este contexto. Gracias.
Fuente

Respuestas (4)

14
14
14
2013-01-14 22:17:56 +0000

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.
Fuente
5
5
5
2013-01-15 13:35:44 +0000

  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:

Fuente
4
4
4
2015-02-23 13:45:45 +0000

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
Fuente
3
3
3
2013-01-14 21:59:35 +0000

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