Excel - Como vlookup para retornar vários valores?

Question

Estou procurando usar o Excel para procurar e retornar vários valores de referência para uma determinada chave. O VLookup faz algo muito semelhante ao que eu preciso - mas só retorna uma única correspondência.

Suponho que isso envolverá métodos de retorno e manipulação de matrizes, embora eu não tenha lidado com isso antes. Alguns Googling começam a se apoiar no if ([lookuparray] = [value], row [lookuparray]) como parte de uma solução - embora eu não consiga que ele retorne uma única correspondência ...

Por exemplo, se eu tiver esses dados de referência:

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

Estou tentando obter os vários valores de retorno à direita. (Separados por vírgula, se possível)

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

(Eu já tenho o valor da chave à esquerda - não é necessário extrair esses valores)

Qualquer ajuda sobre como abordar vários valores nesse contexto é necessária. Obrigado.

    

Answer 1

Supondo que você queira uma abordagem de fórmula conforme declarado (não usando o VLOOKUP, mas ainda assim uma fórmula), aqui está como eu expus os dados:

EuentãouseiaseguintefórmulanacélulaC12:

=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 é uma fórmula de matriz, portanto, depois de copiá-la e colá-la na célula, é necessário atingir Ctrl+Shift+Enter . Eu então apenas arrastei para a direita e para o fundo.

Se não houver valor (es) restantes, será exibido o erro #NUM! . Dei um exemplo para amarelo no exemplo de imagem enviada.

Acho que uma abordagem VBA / Macro seria uma solução melhor se você tivesse toneladas de linhas.

    

Answer 2

1. Troque as colunas para que as cores estejam na coluna A e os nomes estejam na coluna B e, em seguida, classifique a cor.

2. Fórmula em C2 (copie para baixo na coluna): = IF (A2 A1, B2, C1 & "," & B2)

3. Fórmula em D2 (copie-a na coluna): = A2 < > A3

4. Filtre por "TRUE" na coluna D para obter os resultados desejados. Veja abaixo:

    

Answer 3

Se você quiser uma abordagem de fórmula, é muito mais simples obter os resultados em células separadas, então vamos assumir que sua primeira tabela é A2: B8 e as cores são listadas novamente em D2: D5. Tente esta fórmula em 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))),"")

confirmado com CTRL+SHIFT+ENTER e copiado de um lado para o outro. Quando os jogos acabarem, você recebe espaços em branco.

A fórmula pressupõe o Excel 2007 ou posterior - se a versão anterior você pode usar o COUNTIF em vez de IFERROR, ou seja,

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

    

Answer 4

Aqui está a solução VBA para você. Primeiro, é assim que os resultados se parecem:

Eaquiestáocódigo:

OptionExplicitFunctionLookupCSVResults(lookupValueAsVariant,lookupRangeAsRange,resultsRangeAsRange)AsStringDimsAsString'ResultsplaceholderDimsTmpAsString'CellvalueplaceholderDimrAsLong'RowDimcAsLong'ColumnConststrDelimiter="|||"  '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