Concatenate with more than one logical test and multiple results

[dl08in]

I'm looking for a way to compare a text string or cell address to a column of possible matches. Then I want to concatenate notes from every row that has a match, but only if a logical test is true. A UDF would be perfect.

I've seen one from Rick Rothstein mentioned a couple times now, but I can't figure out how to add the logical test that I require. In the table below is an example of what I'm looking for. The values in column A (Item) are irrelevant for my purposes, but I want to compare their cell addresses (text strings) against a lookup table in D1:F7. Column B is an example of the results I'm looking for. Note that item $A$2 is present in D3, but the logical test in E3 returns FALSE so B2 is empty. $A$3 is present in D2 and D5. E2 is TRUE and E5 is FALSE, so only F2 is returned in B3. I hope this makes sense.

	A	B	C	D	E	F
1	Item	Results		Cell References	Logical Test	Notes
2	1			$A$3, $A$5	TRUE	AA
3	2	AA		$A$2	FALSE	BB
4	3	EE		$A$6	FALSE	CC
5	4	AA		$A$3	FALSE	DD
6	5	EE		$A$4, $A$6, $A$7	TRUE	EE
7	6	EE, FF		$A$7	TRUE	FF

<tbody>
</tbody>

 

[Aladin Akyurek]

B2: control+shift+enter, not just enter, and copy down:

=REPLACE(ACONCAT(IF(ISNUMBER(SEARCH(CELL("address",A2),$D$2:$D$7)),
    IF($E$2:$E$7,", "&$F$2:$F$7,""),"")),1,2,"")

For this formula to work, you need to add ACONCAT as a module, using Alt+F1, to your workbook:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[dl08in]

That works! I don't fully understand it, but it does exactly what I wanted. Thank you!

 

[Aladin Akyurek]

That works! I don't fully understand it, but it does exactly what I wanted. Thank you!

You are welcome.