대량의 목록을 한 번에 VLOOKUP 조회하는 mass VLOOKUP

수천행 또는 수십만행 정도의 대량 목록에 대해서 VLOOKUP을 하게되면 각 셀마다 수천/수십만개의 자료에 대해서 검색을 하느라 내장함수이지만 시간이 많이 소요될 수 밖에 없습니다.

 

대부분의 업무가 조회 대상 Data는 고정해서 VLOOKUP을 사용하므로,

각 셀별로 따로따로 검색을 하지 않고, 검색 결과를 일괄로 작업하여 결과를 도출하는 것이 시간을 많이 단축될 것입니다.

 

이를 함수로 구현하는 부분은 이렇게 됩니다.

인수는 VLOOKUP과 같으나 찾는 값이 단일값이 아닌 범위를 사용하는 것이 차이가 될 수 있습니다.

추가로 VLOOKUP에서 찾는 값이 없을 때 #N/A 오류가 나오고 이를 처리하기 위해서 IFERROR 함수를 추가로 사용하므로

이를 줄이고자 추가 인수로 PAD_with를 넣어 주는 것으로 합니다.

Public Function mVLOOKUP(Find_Range As Variant, Search_Array As Variant, _
Col As Variant, Optional Match_type As Boolean = False, Optional PAD_with As Variant = "")
    Dim vFind, vSearch, vLookups, vCol, vC, vData, vResult  '// 검색을 위한 것들
    Dim r As Long, c As Long, x As Long, sCol As String     '// 검색 결과를 위한 것들

 

찾는 값 영역과 Data 영역에 대해서 유효성을 확인하고 배열로 전환합니다.

사용자 실수로 찾는 값을 여러 열을 지정했더라도 맨 왼쪽열만 사용하도록 조정해 둡니다.

    '// 검색어영역을 배열로 전환
    If TypeName(Find_Range) = "Range" Then
        Set vFind = Intersect(Find_Range.Parent.UsedRange, Find_Range)
        If vFind Is Nothing Then mVLOOKUP = CVErr(xlErrRef): Exit Function
        vFind = Find_Range.Value2
    Else
        ReDim vFind(1 To 1, 1 To 1)
        vFind(1, 1) = Find_Range
    End If
    '// 검색은 맨 왼쪽 열만 사용하도록 조절
    If UBound(vFind, 2) > 1 Then ReDim Preserve vFind(1 To UBound(vFind), 1 To 1)
    
    '// Data영역을 배열로 전환
    If TypeName(Search_Array) = "Range" Then
        Set vSearch = Intersect(Search_Array.Parent.UsedRange.EntireRow, Search_Array)
        If vSearch Is Nothing Then mVLOOKUP = CVErr(xlErrRef): Exit Function
        vSearch = Search_Array.Value2
    Else
        vSearch = Search_Array
    End If
    
    If Not isArray(vSearch) Then mVLOOKUP = CVErr(xlErrRef): Exit Function

 

검색결과 출력할 열에 대해서 배열로 만들어 처리하도록 합니다.

    '// 열번호를 배열로 처리, 숫자가 아닌 경우 오류
    If TypeName(Col) = "Range" Then vCol = Col.Value2 Else vCol = Col
    If Not isArray(vCol) Then
        ReDim vCol(1 To 1): vCol(1) = Col
    Else
        For Each vC In vCol: sCol = sCol & "," & vC: Next vC
        vCol = Split(sCol, ",")
    End If
    For c = 1 To UBound(vCol)
        If Not IsNumeric(vCol(c)) Then mVLOOKUP = CVErr(xlErrValue): Exit Function
        If CLng(vCol(c)) < 1 Or CLng(vCol(c)) > UBound(vSearch, 2) Then mVLOOKUP = CVErr(xlErrValue): Exit Function
    Next c

 

검색할 Data영역은 오름차순 정렬을 하고 (QuickSort 함수 참조)

VLOOKUP에서 맨 왼쪽열이 기준열이므로 Index함수로 첫번째 열만 남깁니다.

검색결과는 MATCH 함수를 이용해서 일괄로 배열로 저장합니다.

 

검색할 영역을 오름차순으로 정렬하는 이유는 MATCH함수와 VLOOKUP함수에서  match_type이 기본값으로 1로 오름차순 정렬된 자료에서 작거나 같은 값에서 최대값을 나타내기 때문입니다.

    '// Data영역을 오름차순으로 정렬 (vLookup의 Match_Mode = True는 오름차순 기준)
    QuickSort vSearch, 1, 1
    '// Data영역중 검색은 맨 왼쪽 열 하나만 사용함
    vLookups = Application.Index(vSearch, 0, 1)
    
    vResult = Application.Match(vFind, vLookups)

 

Match 함수 결과에 따라 결과 배열을 채웁니다.

Mach 함수 결과가 오류이면 작거나 같은 값이 없으므로 무조건 PAD_with를 출력하고,

값이 있는데,  Match_mode에 따라 기본값인 유사검색(True, 1)이면 그 값을 사용하고,

정확히 일치(False, 0)이면 찾는 값과 같은지 확인하여 같지 않으면 PAD_with로 채웁니다.

    '// 결과를 담을 배열을 선언, 열수는 Col에 입력된 배열수중 index 0을 제외한 값
    ReDim vData(1 To UBound(vResult, 1), 1 To UBound(vCol))
    For r = 1 To UBound(vData, 1)
        If IsError(vResult(r, 1)) Then
            '// 찾는 값이 없는 경우 오류대체 입력
            For c = 1 To UBound(vData, 2): vData(r, c) = PAD_with: Next c
        Else
            If Match_type Then
                '// True로 가까운 값을 검색해 둔 것을 사용
                For c = 1 To UBound(vData, 2): vData(r, c) = vSearch(vResult(r, 1), vCol(c)): Next c
            Else
                '// False로 정확한 일치는 값이 같은지 확인하고 맞으면 출력, 아니면 오류대체 입력
                If UCase(vFind(r, 1)) = UCase(vSearch(vResult(r, 1), 1)) Then
                    For c = 1 To UBound(vData, 2): vData(r, c) = vSearch(vResult(r, 1), vCol(c)): Next c
                Else
                    For c = 1 To UBound(vData, 2): vData(r, c) = PAD_with: Next c
                End If
            End If
        End If
    Next r

 

마지막으로 결과 배열을 sheet에 출력합니다. (getArrayResult 함수 설명 참조)

    '// 배열을 sheet에 출력
    mVLOOKUP = getArrayResult(vData, Application.Caller.Formula, "mVLOOKUP")
    '// mVLOOKUP = vData  <- Excel 2021과 Microsoft 365에서는 직접 전달가능

 

실제 EGTools에 사용한 소스에는 버전별 차이 관련된 부분이 추가되어 있습니다.