[ 응용 ] VLOOKUP 함수 중복값 모두 확인하기, VLOOKUP 중복값 가져오기

[ 들어가며 ]

 

VLOOKUP 함수에 대해 잘 모르시는 분께서는

아래 링크를 먼저 확인하고 오시면 됩니다.

※ VLOOKUP 함수

 

VLOOKUP 함수는 정말 많이 사용하고

강력한 함수이지만,

단점도 명확합니다.

 

먼저 기준이 되는 셀을 기준으로

오른쪽에 있는 값만 찾을 수 있다는 점

같은 기준 값이 여러개 있을 경우

맨 위에 있는 값만 기준으로 한다는 점입니다.

 

오늘은 같은 기준값이 여러개 있을 경우

즉, 중복된 항목이 있을 경우

중복된 값에 대응되는 모든 값을 찾는 방법을 알아보겠습니다.

 

[ 자료 확인하기 ]

 

 

위 그림의 표를 보시면

[ 홍길동 ]이라는 이름을 갖는 사람은

두명이 있지만,

생년월일이 다릅니다.

즉, 다른 사람인 것입니다.

하지만 VLOOKUP으로 [ 홍길동 ]을 찾으면

생년월일이 [ 850501 ]만 출력이 됩니다.

 

이렇게 같은 이름이 여러개 있을 경우

같은 이름을 가진 사람의

생년월일을 모두 표시하는 방법을 알아보겠습니다.

[ 중복값을 모두 찾는 방법 ]

 

먼저 할 일은 중복값이 몇개가 있는 지 확인하기 위해

열을 하나 더 추가해 줄 것입니다.

 

그리고 아래 그림과 같이 수식을 입력합니다.

=COUNTIF($B$2:B9,B2)

이제 이렇게 하면

이름이 몇 번 중복되는 지 확인이 가능합니다.

즉, 기준으로 삼을 값이 몇번이 중복되는 지 확인이 가능한 것입니다. 

VLOOKUP 함수로 자료를 찾을 수 있게

조금 더 데이터를 고쳐보겠습니다.

 

함수식을 다음과 같이 고쳐주세요

이름과 "-"와 참고열의 중복된 숫자를 결합시킵니다.

=B2&"-"&COUNTIF($B$2:B2,B2)

 

이제 아래 그림과 같은 표를 하나 더 만들어 줍니다. 

반응형

이제 빨간색 네모 부분에 함수식을 입력하겠습니다.

=VLOOKUP($E$2&"-"&G$1, $A$2:$C$9, 3, FALSE)

 

홍길동은 모두 두번 중복되므로

1과 2에만 값이 나타나고

3과 4에는 값이 나타나지 않습니다.

 

이름을 [ 김철수 ]로 바꾸어 보겠습니다.

[ 김철수 ]는 세 번 중복되므로 

3까지만 값이 나타납니다. 

 

이제 [ #N/A ]라는 오류 메세지가 표시되지 않게 하기 위해

함수식을 조금 더 수정하겠습니다.

 

IFERROR 함수를 추가하여

에러가 발생하면 값을 입력하지 않게 하겠습니다.

=IFERROR(VLOOKUP($E$2&"-"&G$1, $A$2:$C$9, 3, FALSE), "")

 

이제 [ 생년월일 ]에 

중복된 모든 값을 표시하겠습니다.

TEXTJOIN 함수를 사용할 것입니다.

참고로 TEXTJOIN 함수는

엑셀 2019 버전 이상에서부터 사용이 가능합니다.

※TEXTJOIN 함수에 대한 설명은 여기를 참고하세요

=TEXTJOIN(", ", , $G$2:$J$2)

 

이렇게 하면 VLOOKUP 함수로도

중복된 항목을 모두 표시하도록 할 수 있습니다.

 

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기