Hi,

Try this UDF. Alt +F11 to open VB editor. Right click 'ThisWorkbook' and
insert module and paste the code below in

call with

=concat(a1:a10,1)

Adjust the range to suit and 1 is the lookup value


Function concat(rng As Range, condition As Long) As String
For Each r In rng
If r.Offset(, 1) = condition Then
concat = concat + r & " ,"
End If
Next r
concat = Left(concat, Len(concat) - 1)
End Function

Mike

"Betty" wrote:

Hi,

I want to lookup a value, and concatenate the results.

Example:

Col A; Col B
apple; 1
pear; 1
naartjie; 2

I want to lookup values with the value of "1"
Result apple, pear(in one cell)

I've seen the same query on other sites, with the answer to use
concatif(A:A,B:B,",), but I can't find the function on excel, or the function
doesn't work when I use it.