최초 작성일: 2008-07-01
최종 수정일: 2026-09-30
조회수: 30 회
작성자: 권현욱 (엑셀러)
강의 제목: 나만의 Vlookup 함수 만들기

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

아주 오래 전에 받은 질문 메일인데 PC 정리하다가 찾았습니다.

'질문을 보냈는데 왜 빨랑빨랑 답장이 안오는거야?' 라고 생각하시는 분이 계실 줄 압니다.

질문은 가급적이면 Q&A 게시판을 이용해 주시고, 개인 메일로 보내신 경우에는… 그냥 잊고 계시는 것이 좋을 듯 합니다. 그러다가 오늘처럼 강좌에서 해결해 드리는 날이면 마치 복권에라도 당첨되는 기분이 들지 않을까요?(머라구… 퍽~~@&$(*@#&…)

농담이었습니다. 여건이 되는대로 신속히 답변드리도록 노력하겠습니다. @_T;

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

나만의 Vlookup 함수 만들기

핵심 요약: 조건에 맞는 모든 값을 한 셀에 모아주는 MyVlookup

Vlookup 함수는 조건을 만족하는 값이 여러 개여도 맨 위의 값 하나만 돌려줍니다. 사용자 정의 함수를 만들면 조건에 맞는 값을 모두 이어 붙여서 한 셀에 보여줄 수 있습니다.

1단계: Vlookup과 같은 3개의 인수(찾을 값, 참조 영역, 열 번호)를 받는 Function을 만듭니다.
2단계: 참조 영역의 첫 번째 열을 순환하면서 찾을 값과 같은 셀을 만나면 값을 이어 붙입니다.
3단계: 워크시트에서 =MyVlookup(...)으로 내장 함수처럼 사용합니다.

Step 1: 어떤 질문이었을까요?

질문 하나

Vlookup에 관해서 질문드립니다. TABLE에 있는 정보를 찾아서 다른 Sheet에 뿌려주는 것을 구현 하고 싶은데 문제점이 하나 있습니다.

TABLE에 있는 자료중에 중복된 자료가 있는데 이것이 하나만 찾아서 뿌려주고 나머지는 찾지를 않습니다.

예를 들면 TABLE의 구조는 다음과 같습니다.

접수일 : 접수번호 : 수량 : 증상 :
06/27 : 1010 : 1 : 접촉불량
06/27 : 1010 : 1 : 다른 증상
06/27 : 1010 : 1 : 다른 증상2
06/27 : 1010 : 1 : 다른 증상3
06/27 : 1010 : 1 : 접촉불량 4
06/27 : 1010 : 1 : 접촉불량 6
06/27 : 1010 : 1 : 접촉불량 7
06/27 : 1010 : 1 : 접촉불량 8

위와 같이 TABLE 있는데 접수 번호 1010에 수량이 8개가 접수되었고 각각의 증상이 다른 데 Vlookup으로 접수 번호를 찾으면 맨 위에 있는 접촉 불량만 나타나고 다른 것은 보이질 않습니다. 어떻게 해야 하는지 질문 드립니다.

비슷한 질문

안녕하세요 박O준입니다. 오늘도 회사 파일을 작성하다가 급하게 필요해서 이렇게 질문을 드립니다. 매번 도와주심에 감사드립니다.

vlookup 같은 경우 조건에 맞는 셀이 여러개가 겹치면 가장 위에 있는 조건에 맞는 셀의 값을 가져오지요. Sumif는 문자를 인식하지 못하지만 숫자는 모두 합칩니다. 이런 식으로 Vlookup(혹은 다른 수식)을 사용하여 문자를 한 셀에 모두 합치는게 가능한지요? 매크로를 사용하면 될 듯도 한데… 고견 부탁드립니다.

열1	열2	찾을 값	원하는 결과
a	a	a	abcd
a	b	b	ef
a	c	c	g
a	d
b	e
b	f
c	g

왼쪽을 검색하여 위와 같이 만들고 싶습니다.

Vlookup 함수는 주어진 조건을 충족하는 첫번째 영역의 값을 돌려줍니다. 다시 말해서, 조건을 만족하는 값이 여러 개인 경우, 맨 위의 값만 찾아준다는 얘깁니다.

일반적으로, 조건을 충족하는 모든 데이터를 보고 싶은 경우, (고급) 필터나 피벗 테이블을 사용합니다만, VBA를 이용하면 여러 단계의 조작을 거치지 않고서도 간단하게 해결할 수 있을뿐만 아니라 여러 가지 경우에 맞게 활용할 수 있습니다.

Step 2: 나만의 Vlookup 함수 사용해 보기

열1	열2	찾을 값	검색 결과
a	a	a	abcdk
a	b	b	efb
a	c	c	gqm
a	d
b	e
b	f
c	g
a	k
b	b
c	q
c	m

위 표의 '검색 결과' 셀에는 이런 수식이 들어 있습니다.

=MyVlookup(D73,$B$73:$C$83,2)

원문 오류 수정: 원문에는 수식이 =myvlookup(D61,$B$61:$C$71,2)로 적혀 있어 실제 표의 위치(찾을 값 D73, 참조 영역 B73:C83)와 맞지 않았습니다. 예제 파일의 실제 수식에 맞게 바로잡았습니다. 표의 검색 결과는 MyVlookup 함수를 실행했을 때 나오는 값입니다.

여기서 MyVlookup은 Vlookup 함수를 보강(?)하여 만든 사용자 정의 함수입니다. 코드를 살펴보죠(무지 간단합니다).

Step 3: MyVlookup 함수 코드 살펴보기

Function MyVlookup(rngX As Range, rngRefTable As Range, intCol As Integer) As String
'Vlookup 함수와 비슷하게 3개의 인수를 받습니다. rngX는 lookup_value, rngRefTable은 table_array,
'intCol은 col_index_num 인수에 해당합니다.

    Dim rngCell As Range
    Dim strTemp As String

    For Each rngCell In rngRefTable.Columns(1).Cells
        If rngX = rngCell Then
        'rngRefTable로 지정된 영역 내의 셀을 대상으로 조건 비교를 합니다. rngX 변수에 저장된
        '값과 일치하는 셀을 만나면 strTemp 변수에 차례대로 담아둡니다.

            strTemp = strTemp & rngCell.Offset(, intCol - 1)
        End If
    Next rngCell

    MyVlookup = strTemp

End Function

참고 - 사용자 정의 함수 사용법: 위 코드는 Visual Basic Editor에서 [삽입] - [모듈]을 선택해 표준 모듈에 붙여 넣으면 됩니다. 그런 다음 워크시트에서 내장 함수처럼 =MyVlookup(...)을 입력하세요. 사용자 정의 함수가 들어 있는 파일은 매크로를 사용할 수 있는 형식(xlsm 등)으로 저장해야 다시 열었을 때도 동작합니다.

싱거우리만치 간단하지요? 늘 그렇듯, '응용'은 여러분의 몫으로 남겨둡니다.

응용 힌트: 찾은 값 사이에 쉼표 같은 구분 기호를 넣고 싶다면 strTemp = strTemp & rngCell.Offset(, intCol - 1) 부분에서 값 뒤에 구분 기호를 함께 이어 붙이도록 바꿔 보세요. 마지막에 남는 구분 기호는 Left 함수 등으로 잘라 내면 됩니다.

오늘은 짧게 여기까지…

정리 — MyVlookup 함수 핵심

구분	내용
Vlookup의 한계	조건을 만족하는 값이 여러 개여도 맨 위의 값 하나만 반환
MyVlookup의 인수	rngX(찾을 값), rngRefTable(참조 영역), intCol(가져올 열 번호)
핵심 동작	참조 영역 첫 번째 열을 For Each로 순환하며 일치하는 셀의 값을 strTemp에 이어 붙임
결과 예	찾을 값 a → abcdk, b → efb, c → gqm
대안	모든 값을 나열하려면 고급 필터나 피벗 테이블을 사용할 수도 있음