Excel - Jak vlookupować, aby zwrócić wiele wartości?

Zakładając, że chcesz podejście do formuły, jak podano (nie przy użyciu VLOOKUP, ale nadal formuła), tutaj jest, jak układał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 macierzowa, więc po skopiowaniu i wklejeniu go do komórki musisz trafić Ctrl+Shift+Enter. Następnie po prostu przeciągnąłem ją w prawo i na dół.

Jeśli nie ma już wartości(y), to daje to błąd #NUM!, podałem przykład dla żółtego w przykładzie załadowanego obrazu.

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

1. Zamień kolumny tak, aby kolory znajdowały się w kolumnie A, a nazwy w kolumnie B, a następnie posortuj na kolor.

2. Formuła w C2 (skopiuj ją w dół kolumny): =IF(A2<<>A1,B2,C1 & “, ” & B2)

3. Wzór w D2 (skopiuj go w dół kolumny): =A2<<A3

4. Filtr dla “TRUE” w kolumnie D, aby uzyskać pożądane wyniki. Zobacz poniżej:

Oto rozwiązanie VBA dla Ciebie. Po pierwsze, oto jak 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

Jeśli chcesz skorzystać z podejścia opartego na formułach, o wiele łatwiej jest uzyskać wyniki w osobnych komórkach, więc załóżmy, że twoja pierwsza tabela to A2:B8, a kolory są wymienione ponownie 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))),"")

potwierdzoną z CTRL+SHIFT+ENTER i skopiowaną w poprzek i w dół. Gdy skończą się mecze otrzymujesz puste miejsca.

Formuła zakłada Excel 2007 lub nowszy - jeśli wcześniejsza wersja, możesz użyć COUNTIF zamiast IFERROR, czyli

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