Nếu dùng UDF thì dùng hàm này
Function LookUpConcat(ByVal SearchString As String,
SearchRange As Range,
ReturnRange As Range,
Optional Delimiter As String = " ",
Optional MatchWhole As Boolean = True,
Optional UniqueOnly As Boolean = False,
Optional MatchCase As Boolean = False)
Dim X As Long, CellVal As String, ReturnVal As String, Result As String
If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or
(ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
LookUpConcat = CVErr(xlErrRef)
Else
If Not MatchCase Then SearchString = UCase(SearchString)
For X = 1 To SearchRange.Count
If MatchCase Then
CellVal = SearchRange(X).Value
Else
CellVal = UCase(SearchRange(X).Value)
End If
ReturnVal = ReturnRange(X).Value
If MatchWhole And CellVal = SearchString Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter &
ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
ElseIf Not MatchWhole And CellVal Like "" & SearchString & "" Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
End If
Continue:
Next
LookUpConcat = Mid(Result, Len(Delimiter) + 1)
End If
End Function