vlookup함수 사용 시 중복값 나타내기

=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