xls - 오피스 하위버전(2019)에서 xlookup함수 사용하기

Function XLOOKUP(ByVal lookup_value As Variant, _
                 ByVal lookup_array As Range, _
                 ByVal return_array As Range, _
                 Optional ByVal if_not_found As Variant = CVErr(xlErrNA), _
                 Optional ByVal match_mode As Long = 0, _
                 Optional ByVal search_mode As Long = 1) As Variant
    ' match_mode: 0 = 정확히 일치, 1 = 정확히 또는 다음 큰 값, -1 = 정확히 또는 다음 작은 값
    ' search_mode: 1 = 첫번째부터, -1 = 마지막부터

    Dim i As Long, n As Long
    Dim arrL As Variant, arrR As Variant
   
    ' 범위를 배열로 변환
    arrL = lookup_array.value
    arrR = return_array.value
    n = UBound(arrL, 1) * UBound(arrL, 2) ' 총 원소 수
   
    Dim stepVal As Long
    stepVal = IIf(search_mode = -1, -1, 1)
   
    ' 방향(행/열) 판정
    Dim isRow As Boolean
    isRow = (UBound(arrL, 1) = 1) ' 행벡터인지 확인
   
    If isRow Then
        Dim j As Long
        If stepVal = 1 Then
            For j = 1 To UBound(arrL, 2)
                If arrL(1, j) = lookup_value Then
                    XLOOKUP = arrR(1, j)
                    Exit Function
                End If
            Next j
        Else
            For j = UBound(arrL, 2) To 1 Step -1
                If arrL(1, j) = lookup_value Then
                    XLOOKUP = arrR(1, j)
                    Exit Function
                End If
            Next j
        End If
    Else
        If stepVal = 1 Then
            For i = 1 To UBound(arrL, 1)
                If arrL(i, 1) = lookup_value Then
                    XLOOKUP = arrR(i, 1)
                    Exit Function
                End If
            Next i
        Else
            For i = UBound(arrL, 1) To 1 Step -1
                If arrL(i, 1) = lookup_value Then
                    XLOOKUP = arrR(i, 1)
                    Exit Function
                End If
            Next i
        End If
    End If
   
    ' 찾지 못한 경우
    XLOOKUP = if_not_found
End Function