엑셀 VLOOKUP 함수 중복값 모두 반환하는 방법에 관해 알아보는 글입니다. 데이터 범위 내에 중복값이 있는 경우 VLOOKUP 함수는 제일 위에 있는 값만 찾아오며, 그 아래에 있는 중복값은 불러오지 못합니다. 하지만 RIGHT 함수, IFERROR 함수, COUNTIF 함수 등을 함께 사용한다면 중복값을 모두 불러오는 게 가능합니다. 지금 바로 아래에서 그 방법을 알아보겠습니다.
위 엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 VLOOKUP 함수 중복값 모두 반환하는법
엑셀 VLOOKUP 함수를 이용해서 중복 값을 모두 반환하는 방법을 알아보겠습니다.
아래 사진을 보면 과일명이 중복되어 있는 걸 알 수 있는데요. 중복되어 있는 각각의 과일명 값을 아래 오른쪽 사진처럼 한 번에 다 찾아와 보겠습니다.
1.COUNTIF 함수로 중복값 개수 카운트
첫 번째로 중복 값이 아닌 고유 값이 되도록 각 과일명의 숫자를 따로 붙여보겠습니다. 그러기 위해서 COUNTIF 함수를 입력 해줍니다.
=COUNTIF($B$3:B3,B3) 이라고 입력을 해주시고요. 엔터키를 누른 후 아래로 자동 채우기를 해주면 됩니다.
2.고유의 찾을값 만들기
과일명 앞에 숫자를 붙이면 중복 값이 아닌 고유 값이 됩니다. 따라서 고유한 값을 만들어 주겠습니다.
우선 = 을 입력한 후 D3 셀을 입력하고 연결 기호를 입력한 후 B3 셀을 입력 해줍니다. 그럼 1사과라는 값으로 표시가 되고요. 아래쪽에도 자동 채우기를 해주면 값이 모두 고유한 값이 됩니다.
이제 찾을 값을 제일 왼쪽 열로 이동시켜주겠습니다. VLOOKUP 함수는 찾을값이 제일 왼쪽 1열에 있을 때만 값을 찾아오기 때문입니다.
3.VLOOKUP 함수로 중복값 모두 반환하기
VLOOKUP 함수로 중복 값을 모두 반환하는 함수식을 입력해 보겠습니다. 우선 VLOOKUP 함수를 입력해준 후에 RIGHT 함수를 입력 해줍니다.
=IFERROR(VLOOKUP(RIGHT(G$2,1)&$F3,$A$2:$C$10,3,0),””)
여기서 RIGHT 함수를 사용하는 이유는 ‘고객명1’ 이라고 되어 있는 부분에서 숫자 1만 추출해내기 위함 입니다. ‘고객명1’ 에서 오른쪽에서부터 첫 번째까지의 숫자를 추출하기 위해서 RIGHT 함수를 입력을 한 거고요. 연결 기호를 입력하고 G3 셀을 입력합니다. 그럼 ‘1사과’ 라고 연결된 텍스트가 나오게 됩니다.
이때 중요한 건 절대 참조를 해주는 건데요. RIGHT 함수 내의 H2셀은 2행만 고정이 되면 되겠죠. 따라서 2행을 절대 참조를 해주면 됩니다. F4 키를 두 번 눌러주시면 2열만 절대 참조가 됩니다.
그리고 G3셀의 경우 G열은 고정이 되어야 되기 때문에 G열에만 절대 참조를 해주면 됩니다. 이번엔 F4 키를 3번 눌러주시면 G열에만 절대 참조가 됩니다.
콤마를 입력해주고요. VLOOKUP 두 번째 인수로 전체 찾을 값이 포함되어 있는 범위를 선택해줘야 됩니다. A2부터 C10까지의 범위를 선택해준 후 이번에도 F4 키를 눌러서 절대 참조를 해주고요.
콤마를 입력하고 네 번째로 세 번째로 열 번호를 입력 하는데요. 우리가 찾을 고객명 이름은 C열, 즉 3열에 나와 있기 때문에 숫자 3을 입력해주면 되고요.
콤마를 입력하고 FALSE 또는 0을 입력합니다. 정확한 값을 찾을 거기 때문에 0을 입력하는 거고요. 괄호를 닫고 콤마를 입력합니다.
그리고 다시 VLOOKUP 함수 맨 앞으로 커서를 가져갑니다. 이번엔 IFERROR 함수를 입력해줍니다. 그리고 다시 맨 뒤에 커서를 두고 큰 따옴표를 두 번 입력해주고 괄호를 닫고 엔터키를 누르면 됩니다.
IFERROR 함수를 이용하는 이유는 찾는 값이 범위 내에 없는 경우 오류 값이 아닌 우리가 입력한 값으로 표시가 되게 하기 위함 입니다. 우리는 큰 따옴표 안에 아무것도 입력을 하지 않았죠. 즉 공백으로 나오도록 입력을 했기 때문에 값이 없는 경우 공백으로 표시가 되는 겁니다.
엑셀 VLOOKUP 함수 사용법 | 총정리
엑셀 VLOOKUP 다른 시트 값 가져 오기 방법
VLOOKUP 함수 오류 6가지 해결법
엑셀 XLOOKUP 함수 사용법 5가지
엑셀 XLOOKUP 함수 중첩 | SUM 함수 중첩
이상 엑셀 VLOOKUP 함수 중복값 에 관한 글이었습니다.