=ConcatText(Sheet1!A1:A3,A1,Sheet1!B1:B3)
Function ConcatText(범위1, 조건, 범위2) As String
Dim strTemp() As String
Dim rng As range
Dim i As Integer, n As Integer
n = 범위2.Column - 범위1.Column
For Each rng In 범위1
If rng = 조건 Then
ReDim Preserve strTemp(i)
strTemp(i) = rng.Offset(, n)
i = i + 1
End If
Next rng
ConcatText = Join(strTemp, ",")
End Function
만약 조건이 2개일때..
=ConcatText(N2:N9999,C5,Y2:Y9999,B5,2:I9999)
(설명) 범위1, 조건1, 범위2, 조건2, 표시값
Option Explicit
Function ConcatText(범위1, 값1, 범위2, 값2, 범위3) As String
Dim strTemp() As String
Dim rng As Range
Dim i As Integer
For Each rng In 범위1
If rng = 값1 Then
If rng.Offset(, 범위2.Column - 범위1.Column) = 값2 Then
ReDim Preserve strTemp(i)
strTemp(i) = rng.Offset(, 범위3.Column - 범위1.Column).Value
i = i + 1
End If
End If
Next rng
ConcatText = Join(strTemp, ", ")
End Function