여러 가지 조건을 만족하는 값을 찾을 때(2-1) - 사용자 정의 함수(1)

XLookup함수가 뛰어나기는 하지만 Excel 2021 버전이상에서만 사용할 수 있기 때문에

사용자 정의 함수를 만들어 사용하려고 합니다.

 

만들면서 한 열만 지정하는 경우는 범위로 되고,

열을 &연산자로 연결하면 배열로 돼서 이를 해결하느라 고생 좀 했습니다.

 

사용자 정의 함수는 추가기능 파일, 확장자 xla(m) 파일에 모아 놓고 사용하는 것이 좋습니다. 그렇지만 설명이므로 모듈에 만드는 것을 기준으로 설명하겠습니다.

 

1. VBA 에디터 실행 및 모듈 삽입

개발도구 탭이 없다면

 

파일 - 옵션에서 Excel 옵션 창을 연 후

리본 사용자 지정 메뉴를 선택하고, 개발도구 왼쪽에 체크해야 합니다.

 

개발 도구 탭을 누른 후 Visual Basic 명령을 눌러

Visual Basic 에디터를 엽니다.

 

그리고, Visual Basic 에디터에서 '여러 가지 조건 검색.xlsx'를 클릭한 후 사용자 정의 폼 등 콤보 상자 버튼을 누른 후 모듈을 클릭해서 모듈을 삽입합니다.

 

그러면 엑셀 파일명 아래에 모듈과 그 아래 Module1이 생기고, 오른쪽을 보면 빈 에디터 창이 있습니다.

 

 

2. 사용자 정의 함수 작성

 

아래 코드를 오른쪽 에디터 창에 붙여 넣습니다.

Option Explicit
Option Base 1

Function multi_vlookup(lookup_value, lookup_array, return_array As Range)
    Dim i As Double
    
    '배열은 그대로 두고, 범위일 경우 배열로 변환
    Dim Range_to_Array As Variant
    Range_to_Array = lookup_array
    
    For i = 1 To UBound(Range_to_Array)
        If i >= 2 And Len(Range_to_Array(i, 1)) = 0 Then
            If Len(Range_to_Array(i - 1, 1)) > 0 _
            And Len(Range_to_Array(i, 1)) = 0 Then Exit For
        End If
            
        If StrComp(lookup_value, Range_to_Array(i, 1)) = 0 Then
            multi_vlookup = return_array(i, 1)
            Exit For
        Else
            multi_vlookup = CVErr(xlErrNA)
        End If
    Next
End Function

 

 

3. 사용자 정의 함수 실행

 

가. multi_vlookup 함수의 구문

multi_vlookup(lookup_value, lookup_array_Range, return_array_Range As Range) 로서

XLookup함수와 유사합니다.

 

다만 인수 다음에 세 번째 인수만 Range로 주고, 나머지는 다양한 형식인 Variant가 되도록 지정하지 않았습니다. 그리고, XLookup함수의 선택 인수는 모두 제외했습니다.

 

나. multi_vlookup 함수 사용

C2셀에 =mu까지만 입력해도 multi_vlookup이란 사용자 정의 함수명이 표시되므로 탭 키를 눌러 완성합니다.

 

그리고, 한국의 2021년도 인구수를 찾아야 하므로

찾을 값으로는 A2셀과 B2셀을 결합해서 A2&B2라고 입력하고,

 

찾을 범위는 F열과 G열을 결합해서

F:F&G:G라고 입력합니다.

 

그리고, 반환 값의 범위는 H열에 있으므로 

H:H라고 입력합니다. 

이 때는 열 전체를 지정했으므로 F4키를 눌러 절대 참조 범위로 지정할 필요가 없습니다.

 

그러면 한국의 2021년도 인구 50이 반환됩니다.

 

또는 =multi_vlookup(A2&B2,$F$2:$F$7&$G$2:$G$7,$H$2:$H$7)라고

값이 있는 셀만 지정해서

찾을 범위는 $F$2:$F$7&$G$2:$G$7,

반환 범위는 $H$2:$H$7라고 입력해도 됩니다.

다만, 이 때는 열 전체를 지정했을 경우와는 달리 F4키를 눌러서 절대 참조 형식으로 입력해야 합니다.

마찬가지로 50이 반환됐습니다.

 

 

※ 주의 사항 Microsoft 365 버전에서는 Shift+Ctrl+Enter키를 누르지 않아도 문제없이 값이 반환되는데, Excel 2019버전에서 해보니 값이 이상해서 살펴보니 Shift+Ctrl+Enter키를 눌러야 합니다.

여러 가지 조건 검색2(완성).xlsm

0.02MB