자주 쓰는 VBA 패턴 (4) : VBA로 index match 함수 구현하기

값을 매핑할때 대부분의 사람들은 보통 vlookup 함수를 사용하지만, 좀 더 엑셀을 잘 하는 사람들은 index-match라 불리는 합성함수를 더 자주 사용한다 ={INDEX(rng MATCH(lookupvalue, rng, 0))} . index match는 원리만 정확히 이해하면 다중조건, xlookup 등 폭넓게 활용 가능하다.

index match나 vlookup 모두 Application.WorksheetFunction 매서드로 vba안에서 사용할 수 있다. 다만, 때때로 현업에서는 해당 매서드 없이 index match, vlookup과 같은 기능을 구현해야 될 경우가 종종 생긴다.
(실무는 늘 제약사항이 존재한다)

아래의 코드는 WorksheetFunction 매서드 없이 vba에서 index match, vlookup을 구현한 코드이다.

Sub indexmatch()

    Dim index_col As String
    Dim match_value_col As String
    Dim match_range_col As String
    Dim rw As Integer
    Dim hdr_row As Integer
    Dim last_row As Integer


    Dim i As Integer
    Dim match_value As Variant
    
    index_col = "AY"
    match_value_col = "BM"
    match_range_col = "AX"
    rw = 12
    hdr_row = 11
    last_row = 2882

    match_value = Range(match_value_col & rw).Value

    For i = hdr_row + 1 To last_row
        If match_value = Range(match_range_col & i).Value Then
           Range("BH" & rw).Value = Range(index_col & i).Value
        Else
        End If
    Next i
        
    
End Sub

코드를 찬찬히 뜯어보면,

1. 일단 해당 코드를 Function으로 바꾼다면 매크로 안에서 사용할 수 있다.

2. for 문을 돌려서 index match로 값을 찾고, 찾은 값을 내가 원하는 셀에 입력한다.

3. 단 현재의 함수는 vlookup이나 index match와 다르게 중복되는 값이 있을 경우 가장 마지막에 찾은 값을 해당 셀에 넣게 되어 있다. if 문 아래에 break을 넣으면 이를 피할 수 있을 것이다.