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

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

Ich möchte Excel verwenden, um mehrere Referenzwerte für einen bestimmten Schlüssel nachzuschlagen und zurückzugeben. VLookup macht etwas sehr Ähnliches wie das, was ich brauche - gibt aber nur eine einzige Übereinstimmung zurück.

Ich nehme an, dass es Array-Rückgabe- und Bearbeitungsmethoden beinhaltet, obwohl ich mich mit diesen noch nicht befasst habe. Manches Googling beginnt, sich auf das if([lookuparray]=[Wert],Zeile[lookuparray]) als Teil einer Lösung zu stützen - obwohl ich es nicht dazu bringen kann, ein einzelnes Match zurückzugeben…

Wenn ich zum Beispiel diese Referenzdaten habe: 

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

versuche ich, die mehrfachen Rückgabewerte auf der rechten Seite zu erhalten. (Komma getrennt, wenn möglich) 

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

(Ich habe bereits den Schlüsselwert auf der linken Seite - es ist nicht nötig, diese Werte herauszuziehen)

Jede Hilfe, wie man in diesem Zusammenhang den Umgang mit mehreren Werten angeht, ist willkommen. Danke.
Quelle

Antworten (4)

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

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.
Quelle
5
5
5
2013-01-15 13:35:44 +0000

  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:

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

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
Quelle
3
3
3
2013-01-14 21:59:35 +0000

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