Excel - Wie kann man vlookup verwenden, um mehrere Werte zurückzugeben?

Angenommen, Sie möchten einen Formelansatz wie angegeben (nicht unter Verwendung von VLOOKUP, aber immer noch eine Formel), so habe ich die Daten wie folgt angeordnet:

In Zelle C12 habe ich dann folgende Formel verwendet:

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

Dies ist eine Array-Formel, so dass Sie, nachdem Sie sie kopiert und in die Zelle eingefügt haben, Ctrl+Shift+Enter drücken müssen. Ich habe sie dann einfach nach rechts und nach unten gezogen.

Wenn kein Wert (keine Werte) mehr übrig ist (sind), ergibt sich der Fehler #NUM!, ich habe ein Beispiel für Gelb in dem hochgeladenen Bildbeispiel gegeben.

Ich denke, ein VBA/Makro-Ansatz wäre eine bessere Lösung, wenn Sie viele Zeilen haben.

1. Vertauschen Sie die Spalten, so dass die Farben in Spalte A und die Namen in Spalte B stehen, und sortieren Sie dann nach der Farbe.

2. Formel in C2 (in der Spalte nach unten kopieren): =IF(A2<>A1,B2,C1 & “, ” & B2)

3. Formel in D2 (die Spalte nach unten kopieren): =A2<>A3

4. Filtern Sie nach “WAHR” in Spalte D, um die gewünschten Ergebnisse zu erhalten. Siehe unten:

Hier ist die VBA-Lösung für Sie. Zunächst sehen die Ergebnisse folgendermaßen aus:

Und hier ist der Code:

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

Wenn Sie einen Formelansatz wünschen, ist es viel einfacher, die Ergebnisse in getrennten Zellen zu erhalten. Nehmen wir also an, dass Ihre erste Tabelle A2:B8 ist und die Farben wieder in D2:D5 aufgelistet sind. Versuchen Sie diese Formel in 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))),"")

mit CTRL+SHIFT+ENTER bestätigt und quer und nach unten kopiert. Wenn die Übereinstimmungen ausgehen, erhalten Sie Leerzeichen.

Formel setzt Excel 2007 oder später voraus - bei früheren Versionen können Sie COUNTIF anstelle von IFERROR verwenden, d.h.

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