[VBA] VLookup 완벽 가이드

이 포스트는 Excel Macro Mastery 사이트의 'VBA VLookup – A Complete Guide(by Paul Kelly)'의 내용을 다시 정리한 것입니다. 이번 포스트에서는 VLookup 함수를 VBA 스크립트에서 사용하는 방법에 대해 다룹니다.

들어가며

이번 포스트에서는 VLookup 함수를 VBA 스크립트에서 쉽게 사용할 수 있는 방법에 대해 다루도록 하겠습니다. 그리고 VLookup 함수를 사용함에 있어서 쉽게 빠질수 있는 함정과 그걸 패해가는 방법 역시 다룰 예정입니다.

만일 엑셀 VLookup이 익숙하지 않은 분이라면 [여기]에 좋은 설명이 있습니다.

간단한 VLookup예제

NOTE : 아래 예제의 shData는 코드 네임으로 참조되는 워크시트를 나타냅니다. 여러분은 이 변수의 이름을 여러분이 사용하고 있는 워크시트의 이름으로 변경할 수 있습니다.

다음 데이터를 살펴 보도록 하겠습니다.

' 이 Sub를 사용하여 데이터를 생성합니다.
Sub GenerateData()
    
    ' Change the sheet name as required
    With ThisWorkbook.Worksheets("Sheet1")
    
        .Range("A1").CurrentRegion.ClearContents
        .Range("A1:A7").Value = WorksheetFunction.Transpose(Array("과일", "사과", "살구", "오렌지", "복숭아", "배", "자두"))
        .Range("B1:B7").Value = WorksheetFunction.Transpose(Array("가격", 1500, 2300, 1450, 2200, 1600, 1200))

    End With
    
End Sub

아래 예제 코드는 배의 가격 1600을 반환합니다.

Sub SimpleVLookup()
    
    Dim sRes As String
    ' sRes = Application.VLookup("배",shData.Range("A2:B7"),2)
    sRes = Application.VLookup("배", shData.Range("A2:B7"), 2, False)
    
    ' This will print 1600 to the Immediate Window(Ctrl + G)
    Debug.Print sRes
    
End Sub

NOTE : 원문에서는 맨 마지막 선택인자를 지정하지 않았지만 실제 예제를 실행했을 때 네 번째 선택인자에 False를 지정하지 않는 경우 '13' 런타임 오류가 발생하였습니다: 형식이 일치하지 않습니다. 오류가 발생한다.

이유는 네 번째 인자를 명시적으로 지정하지 않으면 True. 즉, '유사 일치;로써 가장 가까운 일치항목을 검색하는데, 이는 첫번째 열이 알파벳 또는 숫자순 오름차순으로 정렬된다고 가정한다. 하지만 예제에서는 한글을 사용하고 오름차순이 아니었기 때문에 이와 같은 오류를 발생 시켰다. 알파벳의 경우 오름차순 정렬이 아니더라도 오류는 발생시키지 않았다.

위 코드는 A2:B7 범위에서 텍스트 "배"를 찾습니다. 지정된 텍스트를 찾으면 VLookup함수의 세번째 인자로 지정된 열(위 예에서는 2)에서 텍스트와 동일한 행의 값을 반환합니다.

몇가지 예와 결과를 좀더 살펴 보겠습니다.

    ' 1450 반환
    sRes = Application.VLookup("오렌지", shData.Range("A2:B7"), 2, False)
    Debug.Print sRes

    ' 1500 반환
    sRes = Application.VLookup("사과", shData.Range("A2:B7"), 2, False)
    Debug.Print sRes

    ' 1200 반환
    sRes = Application.VLookup("자두", shData.Range("A2:B7"), 2, False)
    Debug.Print sRes

   ' 열이 1일 때 오렌지를 반환
    sRes = Application.VLookup("오렌지", shData.Range("A2:B7"), 1, False)
    Debug.Print sRes

    ' 열이 1일 때 사과를 반환
    sRes = Application.VLookup("사과", shData.Range("A2:B7"), 1, False)
    Debug.Print sRes

    ' 열이 1일 때 자두를 반환
    sRes = Application.VLookup("자두", shData.Range("A2:B7"), 1, False)
    Debug.Print sRes

매개변수

VLookup(lookup_value, table_array, col_index_num. range_lookup).

VLOOKUP 함수의 인자는 순서대로 아래 네 개가 있습니다.

1. lookup_value : 조회하려는 값으로서, 조회 값이라고도 합니다. 범위의 첫번째 열에 있어야 합니다.
2. table_array : 조회 값이 있는 범위입니다. 조회 값은 항상 조회 범위에서 첫 번째 열에 있어야 한다는 것을 주의하십시오. 예를 들어, 조회 값이 A2 셀에 있다면 범위는 A로 시작해야 합니다.
3. col_index_num : 반환 값이 포함된 범위에 있는 열 번호입니다. 예를 들어 A2:B7 을 범위로 지정하는 경우 A를 첫 번째 열로, B를 두 번째 열로 계산해야 합니다.
4. range_lookup(선택 사항) : 유사 일치가 필요하면 TRUE를 지정하고, 반환 값의 정확한 일치가 필요하면 FALSE를 지정할 수 있습니다. 항목을 지정하지 않으면, 기본값은 항상 TRUE, 즉 유사 일치 입니다.
   True를 사용하면 첫 번째 열이 알파벳 또는 숫자순으로 정렬된다고 가정하므로 한글을 사용하는 경우 FALSE를 명시적으로 지정해주어야 한다.

매개변수 1 : lookup_value

이것은 여러분이 찾고 있는 값입니다. 이 값을 지정해줄때 명심해야 하는 것은, 항상 조회 범위의 첫번째 열에 있는 값이어야 한다는 것입니다. 예를 들어 C4:X10 범위를 사용하는 경우 조회 값은 C열에 있는 값이어야만 합니다. Z1:AB5의 경우 Z열에 있어야 합니다.

Sub StringVLookup()
    
    Dim sFruit As String
    sFruit = "자두"
    
    Dim sRes As Variant
    sRes = Application.VLookup(sFruit, shData.Range("A2:B7"), 2, False)
    
End Sub

일반적으로 검색하는 값의 타입은 문자열을 많이 사용합니다. 물론 번호를 이용하여 검색할 수도 있지만 주의해야 할 사항이 있습니다.

• 숫자가 텍스트로 저장된 경우 검색 값은 문자열이어야 합니다.
• 숫자가 숫자로 저장된 경우 검색값은 숫자여야 합니다.

예를 들어 아래의 데이터에는 조회열의 타입이 숫자로 되어 있습니다.

이 경우 조회 값은 long이어야 하며 그렇지 않으면 오류가 발생합니다.

Sub NumberVLookup()
    
    Dim num As Long
    num = 7
    
    Dim sRes As Variant
    sRes = Application.VLookup(num, shData.Range("F2:G7"), 2, True)
    
    Debug.Print sRes
    
End Sub

소수 값을 찾는 경우 Double을 사용할수도 있습니다. 하지만 정수 숫자와 마찬가지로 Double을 사용하려면 엑셀의 셀 형식이 숫자로 지정되어 있어야 합니다.

숫자와 텍스트 외에도 날짜를 이용해 VLookup을 사용하는 경우도 있습니다. 하지만 이 경우에는 VBA는 Date타입이 있지만 워크시트에는 없습니다. 따라서 다음 예제와 같이 날짜 타입을 Long으로 변환해야 할 필요가 있습니다.

theDate = CLng(#1/14/2017#)

theDate = CLng(CDate("1/14/2017"))

theDate = CLng(shData.Range("H10"))

이와 같이 날짜를 Long 타입으로 변경한 후에 그것을 이용해 VLookup함수에서 정상적으로 사용할 수 있습니다.

Sub DateVLookup()
    
    Dim theDate As Long
    theDate = CLng(#1/14/2017#)
    
    Dim sRes As Variant
    sRes = Application.VLookup( _
                 theDate, shData.Range("I2:J7"), 2, False)
    
    Debug.Print sRes
    
End Sub

매개변수 2 : table_array

이 매개변수는 찾고있는 데이터의 범위를 나타냅니다. 지금까지 예에서 본것처럼 일반적으로 Range를 사용합니다. 만일 워크시트 테이블을 사용하는 경우 테이블의 범위를 사용할 수 있습니다.

Sub SimpleVLookupTable()
    
    Dim sRes As Variant
    
    ' Get the table
    Dim table As ListObject
    Set table = shData.ListObjects("Table1")
    
    ' Use the table for the table_array parameter
    sRes = Application.VLookup("자두", table.Range, 2, False)
    
    Debug.Print sRes
    
End Sub

배열 또한 두 번째 인자로 사용 될수는 있지만 이는 매우 느립니다.

매개변수 3 : col_index_num

이 매개 변수는 반환하려는 값이 포함된 열을 지정합니다. 만일 1이라고 입력되면 table_array의 가장 왼쪽 열을 의미합니다. 열 번호가 조회 범위의 열 보다 크면 오류가 발생합니다. 자세한 사항은 아래의 VLookup Error 섹션을 참조하세요.

매개변수 4 : range_lookup

이것은 선택적 매개변수 입니다. 사용하지 않으면 True를 기본값으로 사용합니다.

• True : 정확하게 일치하지는 않더라도 대략적으로 일치하는 항목을 반환합니다. 정상 동작하기 위해서는 첫 번째 열을 숫자 또는 알파벳 순으로 정렬해야 합니다. 만일 한글을 사용하는 경우 True옵션인 경우 에러를 리턴합니다.
• False : 정확히 일치하는 항목을 찾아야 함을 의미합니다.
• 실무에서는 False(정확한 일치)를 일반적으로 사용합니다.
• True(유사 일치)를 사용할 경우 참조범위의 맨 좌측열(첫번째 열)은 반드시 오름차순으로 정렬 되어야만 합니다.
• 만일 일치 옵션이 True(유사 일치)이고 참조 범위 첫번째 열에서 정확히 일치하는 값이 없는 경우, 찾을 값보다 작거나 같은 값 중 최대값을 반환 합니다.
  만일 찾을 값이 참조 범위의 최소값 보다도 작아 반활할 값이 없는 경우 VLookup함수는 #N/A 오류를 반환 합니다.

테스트의 편의를 위해 앞에서 다루었던 샘플데이터를 영어로 변경해서 다시 살펴 보도록하겠습니다.

Sub SimpleVLookup()

    Dim rg As Range
    Set rg = shData.Range("A2:B7")
    
    Dim sRes As Variant
    
    ' 일치하는 값이 없으므로 찾을 값 'P' 보다 작은 값중 가장 큰 Orange를 리턴한다.
    sRes = Application.VLookup("P", rg, 2, True)
    Debug.Print sRes
    
    ' 일치하는 값이 없으므로 찾을 값 'Pea' 보다 작은 값중 가장 큰 Orange를 리턴한다.
    sRes = Application.VLookup("Pea", rg, 2, True)
    Debug.Print sRes
    
    ' 유사 일치를 찾지 못했으므로 'Pea'까지 성공하고 가장 큰 값인 Peach를 리턴한다.
    sRes = Application.VLookup("Pead", rg, 2, True)
    Debug.Print sRes
    
    ' 유사일치를 사용하지 않으므로 찾지 못함 에러를 리턴한다(오류 2042)
    sRes = Application.VLookup("Pea", rg, 2, False)
    Debug.Print sRes
    
End Sub

에러 핸들링

VBA에서는 'Appliction' 또는 'WorksheetFunction'. 이렇게 두가지 방법으로 VLookup함수를 사용할 수 있습니다.

Application.WorksheetFunction.VLookup

Application.VLookup

위 두 VLookup의 기본적인 사용법은 같지만, 에러를 처리하는 방법은 각각 다릅니다.

WorksheetFunction 사용하기

WorksheetFunction.VLookup을 사용하면 에러 발생시 On Error를 사용하여 잡아야 합니다(On Error에 대한 자세한 설명은 [여기] 참조).

Sub UseWorksheetFunction()
    
    Dim sRes As Variant
    
    ' Turn on error trapping
    On Error Resume Next
    Err.Clear
    
    sRes = Application.WorksheetFunction.VLookup("Plum", shData.Range("A2:B7"), 2, False)
    
    ' 값이 있는지 확인
    If Err.Number = 0 Then
        Debug.Print "찾은 아이템 값은 " & sRes
    Else
        Debug.Print "값을 찾을 수 없음 " & "Plum"
    End If
    
End Sub

Application 사용하기

Application.VLookup은 간단하게 반환값을 확인함으로써 오류를 체크할 수 있습니다.

ub UsingApplication()
    
    Dim sRes As Variant
    
    sRes = Application.VLookup("Plum", shData.Range("A2:B7"), 2, False)
    
    ' Check if value found
    If IsError(sRes) = False Then
        Debug.Print "Found item. The value is " & sRes
    Else
        Debug.Print "Could not find value: " & "Plum"
    End If
    
End Sub

VLookup 에러 타입

아래 표는 Application.VLookup을 사용할 때 발생 할 수 있는 오류 코드입니다.

Constant	에러 코드	에러 값
xlErrDiv0	2007	#DIV/0
xlErrNA	2042	#N/A
xlErrName	2029	#NAME?
xlErrNull	2000	#NULL!
xlErrNum	2036	#NUM!
xlErrRef	2023	#REF!
xlErrValue	2015	#VALUE!

오류와 원인

다음 표는 VLookup에서 발생할 수 있는 몇가지 일반적인 오류를 보여 줍니다. 특정 VLookup오류에 문제가 있는 경우 먼저 엑셀에서 테스트 해보는것이 좋습니다.

Error	Cell	이유
Error 2015	#VALUE!	열 번호가 1보다 작음
Error 2015	#VALUE!	table_array의 인자로 범위 대신 문자열을 사용했음.
Error 2023	#REF!	열 번호가 열 수보다 큼
Error 2042	#N/A	값을 찾을 수 없음

만일 값을 찾을 수 없다는 오류가 발생하는 경우 다음을 확인해 보세요.

1. 테이블 또는 참조범위가 올바른지 확인하세요.
2. 테이블 또는 참조범위에 헤더가 포함되어있는지 확인하세요(VBA는 이것을 정렬되지 않은 데이터라고 간주합니다)
3. 테이블 또는 참조범위가 올바를 워크시트인지 확인하세요.
4. 숫자를 검색하는 경우 lookup_value매개 변수에서 long 또는 double 데이터 유형을 사용하십시오.
5. 텍스트로 저장된 숫자를 검색하는 경우 lookup_value 매개변수에 문자열 데이터 타입을 사용하십시오.
6. 날짜를 검색하는 경우 lookup_value 매개변수에서 long(앞의 날짜 타입 참조)으로 변환해야 합니다.

만일 값을 찾기는 하는데 잘못된 값을 얻고 있다면 네번째 range_lookup 인자가 True이거나 사용되지 않은 경우 첫 번째 열이 알파벳순 또는 숫자순으로 오름차순 정렬되어 있는지 확인합니다.

VLookup 속도 향상

큰 범위에 대해 VLookup를 사용해야 하는 경우 너무 느려 성능 문제가 발생할 수 있습니다. 이 경우 VBA Dictionary를 사용하는 것은 유용한 대안이 될 수 있습니다.

Sub UseDictionary()

    ' Get the range of values
    Dim rg As Range
    Set rg = shData.Range("M1:N20000")
    
    ' Create the dictionary
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Fill the dictionary
    Dim cell As Range
    For Each cell In rg
        dict(cell.Value) = cell.Offset(0, 1).Value
    Next
    
    ' Perform the Lookups
    For Each cell In rg
        Debug.Print dict(cell.Value)
    Next

End Sub

부록 1. 같이 읽으면 좋은 글

• [Excel] VBA에서 매크로를 만드는 방법
• [Excel] Visual Basic for Application(VBA)
• [Excel] VBA - 배열 완벽 가이드
• [Excel] VBA - Range와 Cell 완벽 가이드
• [Excel] VBA - 디버깅(Debugging)