If you do decide to go with a UDF you could this a go:
Code:
Public Function CONCATIF(ByRef CONCATRANGE As Range, ByRef COMPARERANGE, ByRef CONDITION As Variant, Optional ByVal DELIMITER As String = "", Optional ByVal IGNORECASE = False) As String
Dim s As String
Dim c As Range
If TypeName(CONDITION) = "Range" Then CONDITION = CONDITION(1, 1).Value
If TypeName(CONCATRANGE) = "Range" And TypeName(COMPARERANGE) = "Range" _
And COMPARERANGE.Rows.Count = CONCATRANGE.Rows.Count _
And COMPARERANGE.Columns.Count = CONCATRANGE.Columns.Count Then
For Each c In COMPARERANGE
If StrComp(c.Value, CONDITION, IIf(IGNORECASE, vbTextCompare, vbBinaryCompare)) = 0 Then
s = s & IIf(s = "", "", DELIMITER) & CONCATRANGE(c.Row - COMPARERANGE(1, 1).Row + 1, c.Column - COMPARERANGE(1, 1).Column + 1).Value
End If
Next c
CONCATIF = s
Else
CONCATIF = ""
End If
End Function
the formula has the basic signature of:
=CONCATIF(CONCATRANGE, COMPARERANGE, CONDITION, DELIMITER, IGNORECASE)
wherein CONCATRANGE is the range containing the values that you are going to concatenate, COMPARERANGE is the range containing the values that you are going to apply the condition to (could be the same as CONCATRANGE), CONDITION is either a cell reference containing the condition or a hard coded string/number, DELIMITER is optional as must be a hard coded string which will separate the concatenated values and IGNORECASE defaults to FALSE but can be set to TRUE so that the comparison will compare values ignoring their case.
which you could use in your sheet like so:
=concatif($B$2:$B$6,$A$2:$A$6,A4,"-",TRUE) <-- Ignores case, condition is a cell reference and delimiter is a "-"
=concatif($B$2:$B$6,$A$2:$A$6,"EaSt",",",TRUE) <-- Ignores case, condition is a string and delimiter is a ","
=concatif($B$2:$B$6,$A$2:$A$6,"West","-") <-- Respects case, condition is a string and delimiter is a "-"
=concatif($B$2:$B$6,$A$2:$A$6,A2) <-- Respects case, condition is a cell reference and no delimiter
hth
