Excel에서 여러 개의 해당 값을 VLOOKUP하고 연결하는 방법은 무엇입니까?

Excel에서 VLOOKUP을 사용할 때 일반적으로 데이터 세트에서 첫 번째 일치하는 값을 반환합니다. 그러나 특정 클래스와 관련된 모든 이름을 반환하고 연결해야 하는 경우처럼 여러 개의 해당 값을 반환하고 연결해야 하는 시나리오도 있습니다. VLOOKUP만으로는 이를 달성할 수 없으므로 원하는 결과를 얻기 위해 고급 기법을 살펴보겠습니다.

Excel에서 여러 개의 해당 값을 VLOOKUP하고 연결하기

• TEXTJOIN 및 FILTER 함수 사용 (Excel 365/2021)
• Kutools for Excel 사용하기
• 사용자 정의 함수 사용

TEXTJOIN 및 FILTER 함수를 사용하여 여러 개의 해당 값을 VLOOKUP하고 연결하기

Excel 365 또는 Excel 2021을 사용 중이라면, TEXTJOIN과 FILTER 함수를 결합하면 여러 개의 해당 값을 연결하는 강력하고 효율적인 방법을 제공합니다.

대상 셀에 다음 수식을 입력한 다음 수식을 다른 셀로 드래그하여 셀을 채웁니다. 모든 해당하는 일치하는 값이 추출되어 하나의 셀에 결합됩니다. 스크린샷 참조:

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

Kutools for Excel을 사용하여 여러 개의 해당 값을 VLOOKUP하고 연결하기

여러 해당 값을 처리할 때 Excel의 VLOOKUP 한계에 지쳤습니까? 하지만 Kutools for Excel의 일대다 조회 기능을 사용하면 몇 번의 클릭만으로 여러 해당 값을 쉽게 조회하고 연결할 수 있습니다!

Kutools for Excel 설치 후 다음 단계를 따르세요:

"Kutools" > "슈퍼 LOOKUP" > "일대다 조회(여러 결과 반환)"를 클릭하여 대화 상자를 엽니다. 대화 상자에서 아래와 같이 작업을 지정하세요:

1. 텍스트 상자에서 "목록 배치 영역"과 "검색할 값 범위"를 각각 선택하세요;
2. 사용하려는 테이블 범위를 선택하세요;
3. "키 열"과 "반환 열" 드롭다운에서 각각 키 열과 반환 열을 지정하세요;
4. 마지막으로 확인 버튼을 클릭하세요.
   

결과: 이제 모든 일치하는 값이 하나의 셀에 연결되었습니다. 스크린샷 참조:

사용자 정의 함수를 사용하여 여러 개의 해당 값을 VLOOKUP하고 연결하기

VBA(Visual Basic for Applications)에 익숙하다면 여러 해당 값을 연결하기 위한 사용자 정의 함수(UDF)를 생성할 수 있습니다. 이 방법은 모든 버전의 Excel에서 작동합니다.

1. "ALT + F11" 키를 눌러 "Microsoft Visual Basic for Applications" 창을 엽니다.

2. "삽입" > "모듈"을 클릭하고 모듈 창에 다음 코드를 붙여넣습니다.

VBA 코드: 셀에서 여러 일치하는 값을 VLOOKUP하고 연결하기

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. 그런 다음 이 코드를 저장하고 닫고 워크시트로 돌아가서 =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) 수식을 결과를 넣을 빈 셀에 입력한 다음, 채우기 핸들을 아래로 드래그하여 수식을 다른 셀에 채웁니다. 쉼표와 공백 구분 기호로 특정 데이터에 기반한 모든 해당 값이 하나의 셀에 반환됩니다. 스크린샷 참조:

Excel에서 여러 개의 해당 값을 연결하는 것은 다양한 방법으로 달성할 수 있으며, 각각 고유한 장점이 있습니다. 내장 함수인 TEXTJOIN 및 FILTER를 사용하든, Kutools와 같은 타사 도구를 사용하든, 맞춤형 VBA 솔루션을 사용하든, 중요한 것은 자신의 Excel 버전, 기술 수준 및 특정 요구 사항에 가장 적합한 방법을 선택하는 것입니다. 더 많은 Excel 팁과 요령을 탐구하고 싶다면 당사 웹사이트에서는 수천 개의 자습서를 제공합니다.