Return string from duplicate value

[ccarruth]

Workbook attached has full explanation. Basically finding a minimum price from a range of cells (done). Use the min price to find the source in an adjoining cell range; if there is more than one source for same minimum price, return the multiple sources as a comma delimited string.

Thanks all!

 

[Hui]

I would use my Concatif() function available here:
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

or listed below:

Function ConcatIf(Src As Range, ChkRng As Range, myVal As Variant, Optional Sep As String) As String
Dim c As Range
Dim retVal As String
Dim i As Integer

retVal = ""
i = 1

For Each c In ChkRng
If c = myVal Then
retVal = retVal + Src(i) + Sep

End If
i = i + 1
Next

ConcatIf = Left(retVal, Len(retVal) - Len(Sep))
End Function

You use the function as:
=ConcatIf(B9:E9,F9:I9,K9,",")

see it in use in your file:

 

[jindon]

Also UDF but no loop
=JoinIf(F9:I9,K9,B9:E9,",")

Function JoinIf(ByVal rng1 As Range, ByVal crit, ByVal rng2 As Range, _
                        Optional myJoin As String = " ")
    If Not IsNumeric(crit) Then crit = Chr(34) & crit & Chr(34)
    If rng1.Rows.Count = 1 Then
        JoinIf = Join(Filter(Evaluate("if(" & rng1.Address & "=" & crit & _
        "," & rng2.Address & ",char(2))"), Chr(2), 0), myJoin)
    Else
        JoinIf = Join(Filter(Evaluate("transpose(if(" & rng1.Address & "=" & _
        crit & "," & rng2.Address & ",char(2)))"), Chr(2), 0), myJoin)
    End If
End Function

 

[bosco_yip]

If more data in the Table of Sources and Prices, advised to use jindon's UDF in post #3.

As only 4+4 data in the OP's example table, then maybe consider to use the simply IF function,

In J9, formula :

=SUBSTITUTE(TRIM(IF(F9=K9,B9,"")&" "&IF(G9=K9,C9,"")&" "&IF(H9=K9,D9,"")&" "&IF(I9=K9,E9,""))," ",",")

Regards
Bosco