Excel - Hoe kan ik meerdere waarden teruggeven?

Ervan uitgaande dat je een formulebenadering wilt zoals vermeld (niet met behulp van VLOOKUP, maar toch een formule), is hier hoe ik de gegevens heb ingedeeld:

Ik heb toen de volgende formule gebruikt in cel 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))

Dit is een matrixformule, dus nadat je het hebt gekopieerd en geplakt in de cel moet je op Ctrl+Shift+Enter klikken. Ik heb het dan gewoon naar rechts en naar beneden gesleept.

Als er geen waarde(n) over is geeft het de #NUM! fout, ik gaf een voorbeeld voor geel in het geüploade voorbeeld.

Ik denk dat een VBA/Macro aanpak een betere oplossing zou zijn als je tonnen rijen hebt.

1. Verwissel kolommen zodat de kleuren in kolom A en de namen in kolom B staan, en sorteer dan op de kleur.

2. 1. Formule in C2 (kopieer het naar beneden in de kolom): =IF(A2<>A1,B2,C1 & “, ” & B2)
3. 1. Formule in D2 (kopieer het naar beneden in de kolom): =A2<>A3
4. 1. Filter voor “WAAR” in kolom D om de gewenste resultaten te krijgen. Zie hieronder:

Hier is de VBA-oplossing voor u. Ten eerste, zo zien de resultaten er uit:

En hier is de 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

Als je een formulebenadering wilt, dan is het veel eenvoudiger om de resultaten in aparte cellen te krijgen, dus laten we aannemen dat je eerste tabel A2:B8 is en de kleuren weer in D2:D5 staan. Probeer deze formule 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))),"") bevestigd met CTRL+SHIFT+ENTER en gekopieerd over en onder. Wanneer matches opraken krijg je blanco’s.

Formule gaat uit van Excel 2007 of later - als eerdere versie kun je COUNTIF gebruiken in plaats van IFERROR, d.w.z.

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