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

Excel - Hoe kan ik meerdere waarden teruggeven?

Ik ben op zoek naar Excel om meerdere referentiewaarden voor een bepaalde sleutel op te zoeken en terug te geven. VLookup doet iets wat erg lijkt op wat ik nodig heb - maar geeft slechts een enkele match terug.

Ik neem aan dat het een array-retour en behandelingsmethodes inhoudt, hoewel ik deze nog niet eerder heb behandeld. Sommige Googlen begint te leunen op de if([lookuparray]=[waarde],rij[lookuparray]) als onderdeel van een oplossing - hoewel ik het niet kan krijgen om een enkele overeenkomst terug te geven…

Bijvoorbeeld, als ik deze referentiegegevens heb: 

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

probeer ik de meervoudige retourwaarden aan de rechterkant te krijgen. (Komma gescheiden, indien mogelijk) 

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

(ik heb de sleutelwaarde al aan de linkerkant - het is niet nodig om die waarden eruit te halen)

Elke hulp met betrekking tot hoe om te gaan met meerdere waarden in deze context wordt toegejuicht. Bedankt.
Bron

Antwoorden (4)

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

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

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

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

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

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

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