Excel - jak vlookup, aby zwrócić wiele wartości?

Chcę użyć programu Excel do wyszukiwania i zwracania wielu wartości referencyjnych dla danego klucza. VLookup robi coś bardzo podobnego do tego, czego potrzebuję - ale zwraca tylko jedno dopasowanie.

Zakładam, że będzie to wymagało metod zwracania tablic i obsługi, choć wcześniej z nimi nie korzystałem. Niektórzy Googling zaczynają opierać się na if ([lookuparray] = [wartość], row [lookuparray]) jako część rozwiązania - chociaż nie mogę zmusić go do zwrócenia pojedynczego dopasowania ...

Na przykład, jeśli mam te dane referencyjne:

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

Próbuję uzyskać wiele zwracanych wartości po prawej stronie. (Oddzielone przecinkami, jeśli to możliwe)

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

(Mam już kluczową wartość po lewej stronie - nie trzeba wyciągać tych wartości)

Doceniamy wszelką pomoc dotyczącą podejścia do obsługi wielu wartości w tym kontekście. Dzięki.

Zakładając, że chcesz podejście oparte na formule, jak podano (nie używając WYSZUKAJ.PIONOWO, ale nadal formułę), oto jak ułożyłem dane:

Następnie użyłem następującej formuły w komórce 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))

Jest to formuła tablicowa, więc po skopiowaniu i wklejeniu jej do komórki musisz nacisnąć Ctrl+Shift+Enter. Następnie przeciągnąłem go w prawo i na dół.

Jeśli nie ma żadnych wartości, oznacza to #NUM!błąd, podałem przykład koloru żółtego w przesłanym obrazie.

Myślę, że podejście VBA / Macro byłoby lepszym rozwiązaniem, jeśli masz mnóstwo rzędów.

1. Zamień kolumny, aby kolory były w kolumnie A, a nazwy w kolumnie B, a następnie posortuj według koloru.

2. Formuła w C2 (skopiuj ją w dół kolumny): = JEŻELI (A2 <> A1, B2, C1 i „,” i B2)

3. Formuła w D2 (skopiuj ją w dół kolumny): = A2 <> A3

4. Filtr „PRAWDA” w kolumnie D, aby uzyskać pożądane wyniki. Patrz poniżej:

Jeśli chcesz zastosować formułę, wówczas znacznie łatwiej jest uzyskać wyniki w oddzielnych komórkach, więc załóżmy, że twoja pierwsza tabela to A2: B8, a kolory są ponownie wymienione w D2: D5. Wypróbuj tę formułę w 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))),"")

potwierdzone CTRL+SHIFT+ENTERi skopiowane w dół i w dół. Kiedy skończy się mecz, otrzymasz puste miejsca.

Formula zakłada Excel 2007 lub nowszy - jeśli wcześniejsza wersja może użyć COUNTIF zamiast IFERROR, tj

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

Oto rozwiązanie VBA dla Ciebie. Po pierwsze, tak wyglądają wyniki:

A oto kod:

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