나만의 Vlookup 함수 만들기 2 - n번째 값 가져오기

Step 1: MyVlookup2 함수로 n번째 값 가져오기

<F9> 키를 누를 때마다 '결과값'이 어떻게 바뀌는지 살펴보세요.

Vlookup 함수는 같은 값이 여러 개 있을 경우, 맨 위에 있는 하나의 값만 가져옵니다. '분류1'에서 a라는 값이 여러 개 있습니다. 만약 '=VLOOKUP("a",$B$29:$C$44,2,FALSE)' 라고 하면 항상 'a1'이라는 결과값을 돌려줍니다.

하지만 이번 시간에 만들어 볼 'MyVlookup2' 함수는 같은 값이 여러 개 있을 경우 그 중에서 몇번째 것을 가져올 것인지 지정할 수 있습니다.

Step 2: 함수 사용하는 방법

F29 셀에는 다음과 같은 수식이 들어있습니다. 물론 myvlookup2라는 것은 엑셀에서 제공하는 함수가 아니라 사용자가 필요에 따라 만든 '사용자 정의 함수'입니다.

=myvlookup2(D29,$B$28:$C$44,2,E29)

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

myvlookup2 함수의 내용을 살펴볼까요.

Function MyVlookup2(rngX As Range, rngRefTable As Range, intCol As Integer, Optional intXXX As Integer = 1)
'MyVlookup2 함수는 4개의 인수를 가집니다. rngX는 '찾을 값', rngRefTable은 '참조 테이블'
'intCol은 참조 테이블에서 '몇번째 열'의 값을 가져올 지를 각각 지정합니다.
'여기까지는 지난 시간에 보았던 MyVlookup 함수와 비슷한데… 뒤에 intXXX 라는 것이 하나 더
'붙어 있습니다. 그것도 앞에 Optional 어쩌구… 하는 요상한(?) 것을 데리고 말입니다.

'우리가 자동차를 살 때, 에어컨이나 카오디오 등과 같이 기본적으로 딸려 나오는 것이 있나 하면
'에어백이나 선루프, 자동변속기 등과 같은 선택사양이 있습니다.

'Optional 키워드는 선택사양과 비슷한 역할을 수행합니다. 즉, 특정한 인수를 생략하면 기본적으로
'어떤 값을 갖도록 설정할 때 사용합니다. 여기서는 이 인수를 특별히 지정하지 않으면 '1'이라는
'값을 갖도록 하였습니다.

    Dim rngFound As Range
    Dim rngSource As Range
    Dim i As Integer
    Dim strAddress As String
    Dim rngTarget As Range

    If intXXX < 1 Then
    'intXXX 변수 값으로 1보다 작은 것이 입력되면 오류 메시지를 보여주고 프로시저를 종료합니다.
        MyVlookup2 = "No Data!!"
        Exit Function
    End If

    Set rngSource = rngRefTable.Columns(1)
    Set rngFound = rngSource.Find(rngX, , xlValues, xlWhole)
    'Find 메서드를 사용하여 rngSource 영역 내에 rngX 변수값과 같은 것이 있는지 찾습니다.

    If Not rngFound Is Nothing Then
    'Not rngFound Is Nothing이라는 것을 말 그대로 해석해 보면
    ''rngFound 변수가 아무 것도 아닌 것이 아니라면'이라는 이중부정 형태입니다.
    '이것을 달리 표현하면, '뭔가 값을 찾았다면'이라는 의미입니다.

        If intXXX > 1 Then
        'intXXX 변수값이 1보다 크면 아래의 구문을 실행합니다.

            strAddress = rngFound.Address

            For i = 2 To intXXX
            'intXXX, 즉 같은 값이 여러 개 있을 경우, 사용자가 지정한 위치의 것을 가져오기 위해
            'For~Next 순환문을 사용하였습니다. 같은 값을 계속 찾으면 안되니까 Find 메서드의
            'after 파라미터(여기서는 rngFound)를 지정해 주었습니다.

                Set rngFound = rngSource.Find(rngX, rngFound, xlValues, xlWhole)

                If rngFound.Address = strAddress Then
                    Set rngFound = Nothing
                    Exit For
                End If
            Next i
         End If
    End If

    If rngFound Is Nothing Then
        MyVlookup2 = "No Data!!"
    Else
        MyVlookup2 = rngFound.Offset(, intCol - 1)
        '지정한 횟수만큼 순환문을 돌린 다음 intCol - 1 위치에 있는 결과값을 MyVlookup2 함수의
        '결과값으로 돌려줍니다. 왜 1을 빼주었는지는… 잘 생각해 보세요. Offset 프로퍼티의 성질을
        '잘 떠올려 보시면…? 0부터 시작하는지 1부터 시작하는지… ^^

    End If

End Function

오늘 질문에 대한 것은 이것으로 어느 정도 해결이 될 듯 합니다. 즉 Vlookup 함수와 비슷한 역할을 VBA로 구현하려면 Find와 FindNext 메서드를 적절히 사용하면 되니 말입니다. 오류 트랩에 대한 부분은 별도로 추가하지 않았는데, 이것은 여러분의 필요에 따라 적당히 추가해서 사용하세요.

다음 시간에…

정리 — MyVlookup2 함수 핵심

자주 묻는 질문 (FAQ)

마치며

Optional 인수 하나만 더해도 함수의 쓰임새가 훨씬 넓어집니다. Find 메서드의 특성을 잘 이해해 두면 다양한 검색 함수를 직접 만들 수 있으니, 여러분의 업무에 맞게 응용해 보세요!