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

Excel - Como vlookup para retornar múltiplos valores?

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

Presumo que vai envolver métodos de retorno e manipulação de arrays, embora nunca tenha lidado com estes antes. Alguns Googling começam a apoiar-se no if([lookuparray]=[valor],row[lookuparray]) como parte de uma solução - embora eu não consiga obter uma única correspondência…

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

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

estou a tentar obter os múltiplos valores de retorno à direita. (Vírgula separada, se possível) 

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

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

Qualquer ajuda sobre como abordar o tratamento de múltiplos valores neste contexto é apreciada. Obrigado.
Fonte

Respostas (4)

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

Assumindo que você quer uma abordagem de fórmula como indicado (não usando VLOOKUP, mas ainda assim uma fórmula), aqui está a forma como eu estabeleci os dados:

Eu então usei a seguinte fórmula na célula 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 é uma fórmula de array, então depois de copiá-la e colá-la na célula você tem que bater Ctrl+Shift+Enter. Depois arrastei-a para a direita e para baixo.

Se não restar nenhum valor, dá o erro #NUM!, dei um exemplo para amarelo no exemplo da imagem carregada.

Penso que uma abordagem VBA/Macro seria uma solução melhor se tivesse toneladas de linhas.
Fonte
5
5
5
2013-01-15 13:35:44 +0000

  1. Trocar as colunas para que as cores estejam na coluna A e os nomes na coluna B, e depois ordenar na cor.

  2. Fórmula em C2 (copie-a na coluna): =IF(A2<>A1,B2,C1 & “, ” & B2)

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

  4. Filtro para “TRUE” na coluna D para obter os resultados desejados. Veja abaixo:

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

Aqui está a solução VBA para si. Primeiro, os resultados são estes:

E aqui está o 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
Fonte
3
3
3
2013-01-14 21:59:35 +0000

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

confirmada com CTRL+SHIFT+ENTER e copiada transversalmente e para baixo. Quando os resultados se esgotam, obtém-se espaços em branco.

Formula assume o Excel 2007 ou posterior - se a versão anterior pode usar 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))))
Fonte