엑셀 vba로 다중 vlookup 구현하기-동일한 항목 전체 찾기

https://youtu.be/GR28BNQ7kmc

vlookup 함수를 사용할 경우 특정영역에서 첫번째 항목을 찾을 수 있지만, 동일한 항목이 여러번 있는 경우에는 동작하지 않아서 다소 곤란한 상황이 됩니다. 이런 유사한 경우가 많이 있을 듯 하여 한번 만들어 봤습니다. 

예제는 어떤 분이 질문으로 주신 내용을 참고했습니다.

먼저  vlookup과 그것을 대체하는 방법인 index 와 match를 혼합하여 사용하는 방법에 대해 간단히 알아보겠습니다. 

우선 vlookup으로 원하는 내용을 찾는 방법은 다음과 같습니다.

위와 같은 내용이 있을 때 D3에 있는 내용을 A3.B20까지에서 찾아서 B열의 내용을 E3에 출력하기를 원하때는 아래와 같이 사용합니다.

E3 = VLOOKUP(찾을 대상, 찾을 범위,표현할범위(찾을범이내에서 표현할 컬럼 숫자),유사찾기 방법)

E3 = VLOOKUP(D3, A3.B20,2,FALSE)

만약 동일한 내용을 INDEX, MATCH를 써서 만들 경우 아래와 같습니다.

E3= INDEX(표현할범위,MATCH(찾을대상, 찾을범위,유사찾기 방법)

E3= INDEX(B3.B20,MATCH(D3, A3:A20,FALSE))

여기서 용어는 공식용어가 아니고 그냥 설명한 말이니 오해 없으시길.

그림으로 표현해 보면 아래와 같습니다.

항목이 1개씩만 있을 경우에는 2가지 방법중 어떤 것을 써도 관계 없지만 아래처럼 여러 셀이 있을 경우에는 맨 처음것만 검색이 되므로 VBA로 만들어야 합니다. INDEX, MATCH조합이 보다 융통성이 있으므로 이것을 이용해서 여러 셀에 있는 내용을 찾는 것을 만들어 보겠습니다.

INDEX, MATCH로 첫번째 항목을 찾을 수 있으므로 찾을 범위를 그 다음 항목부터 계속해서 찾으면 원하는 내용을 모두 찾을 수 있지 않겠습니까?

만약 D열3행의 내용을 A3부터 찾아서 동일한 항목이 나오면 D3, D4,D5,D6 등을 모두 찾아서 E3에 넣어 주면 됩니다.

결국 찾을 범위는 D3부터 D열의 마지막행까지이고, 찾을 범위는 A3부터 A열 마지막까지이며, 출력할 내용은 B열의 내용입니다.

우선 특정행/열의 마지막셀을 찾기 위해 아래처럼 넣습니다.

    With ThisWorkbook.Worksheets("Sheet1")
        ' 특정 행/열에서 마지막셀을 찾습니다.
        Dim endRow As Long ' 마지막행
        Dim endCol As Long ' 마지막열
        nEndRow = .Cells(.Rows.Count, 4).End(xlUp).Row
        nSEndRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        nEndCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    End With

nEndRow는 찾을 열인 D열의 마지막행번호입니다.

nSEndRow 는 A열과 B열의 마지막행번호입니다.

nEndCol은 사용할 일은 없지만 참고하시라고 넣었습니다.

이제 시작행인 3행부터 찾을열의 마지막인 nEndRow까지 For ~next 문을 돌면서 하나씩 찾는것입니다.

내용을 찾을 범위(A열)와 출력범위(B열)를 각각 지정해 줍니다. 여기서 nRow라는 변수를 통해 범위를 잡았습니다.

그리고 처음 찾을 내용을 지정합니다.

    For i = nStartRow To nEndRow
        nRow = nStartRow
        SRng = Range("A" & nRow & ".A" & nSEndRow)
        ORng = Range("B" & nRow & ".B" & nSEndRow)
        TResult = ""
        strFind = Cells(i, 4).Value

다음으로는 첫번째 찾은 위치를 nIndex로 저장해 놨다가 해당 위치만큼을 nRow에서 증가시켜주면서 nRow가 D열의 마지막 행번호를 넘을때까지 WHILE문으로 반복해 줍니다.  

        Do While nRow < nSEndRow
            nIndex = WorksheetFunction.Match(strFind, SRng, 0)
            nRow = nRow + nIndex
            Result = WorksheetFunction.Index(ORng, nIndex)
            TResult = TResult & Result(1) & ","
            SRng = Range("A" & nRow & ".A" & nSEndRow)
            ORng = Range("B" & nRow & ".B" & nSEndRow)
        Loop

    Next i

논리적으로는 여기까지 내용만으로 모두 이상없이 수행이 되어야 합니다. 하지만 실제로 진행해 보면 몇가지 더 할일이 있습니다. 먼저 Runtime 오류가 발생할것입니다. 따라서 해당 오류가 발생시 할일을 지정해 줘야 합니다. 

가장 먼저 발생하는 Runtime오류는 찾을 목록에서 더 이상 내용을 찾을 수 없을 때 발생하며, 두번째로는 찾을 2번째 항목을 진행하다 보면 1004 runtime 오류가 발생합니다. 이것을 예방하기 위해 macro 맨 앞에 다음 문장을 넣어 줍니다.

    On Error GoTo G1

오류 발생시 G1으로 가도록 했습니다. 그러므로 G1도 구현해 줘야 합니다.

먼저 오류는 해당 항목에서 더 이상 찾지 못할 때 나오므로 현재까지 찾은 값이 저장된 TResult값을 Cells(i, 5).Value에 넣어 줘야 합니다. 그런데 TResult의 마지막에 ","이 더 있으므로 해당 내용을 지워주도록 합니다. 방법은 TResult의 length를 구해서 그중 마지막만 빼고 가져오는 것입니다.

그리고 마지막에 Error를 초기화 하도록 해 줍니다.  그렇지 않으면 일정 횟수만큼 돌다가 오류가 발생할 것입니다.

이 오류 처리 루틴은 Loop와 Next i사이에 넣어 줍니다.

        Loop
G1:
        nLen = Len(TResult)
        TResult = Left(TResult, nLen - 1)
        Cells(i, 5).Value = TResult
        On Error GoTo -1
    Next i

이렇게 진행하면 원하는 내용을 모두 찾을 수 있습니다. 유첨하는 엑셀을 참고하시면 됩니다.

단축키는 ctrl+q를 할당해 놨습니다.

멀티vlookup.xlsm

0.03MB