A lookup to find multiple corresponding values

[Jaspal]

Hello there,

I have a set of data - Column A, product list like choc,candy,fudge etc and in Coumn B I have a match formula which returns True when a particular product/products is/are selected and false when not.

Q - I want to create an formula that looks up in Coulmn B for True and returns the corresponding names of the products from Column A but in a A CELL.

Column A ColumnB Formula
Choc True Choc, Fudge << this will be a formula
Candy False
Fudge True


Thanks in advance for help

 

[venkat1926]

instead of a formula try autofilter (data-filter-autofilter)
first you must have column headings.

keep the cursor within the data
small arrows will appear in column heads.click the arrow in columm B and choose TRUE. TRUE row will be filtered and you can copy filtered data to any location.

once work is over remove autofilter by repeating the same data-filter-autofilter

 

[mikerickson]

You could use this UDF

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    Dim i As Long, j As Long
    
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                            stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

and the formula =ConcatIf(B:B, TRUE, A:A, ", ")