[ 엑셀 VBA ] vlookup과 비슷한 중복값 찾기 기능, 좌측값 찾기 기능이 있는 사용자 정의 함수 만들기

 함수 기능 설명

 

ADVLOOKUP 함수는 인자를 세 개를 받습니다.

첫 번째 인자는 기준 값

두 번째 인자는 기준 값이 있는 열 범위

세 번째 인자는 찾을 값이 기준 열 범위에서 좌측 또는 우측으로 얼마나 떨어져 있는지

 

ADVLOOKUP 함수는 중복값이 있을 경우 

"중복값있음"을 반환하고 값을 찾지 않습니다.

VLOOKUP 사용 시 중복값이 있을 경우에도 값이 찾아지는 부분을 보완했습니다.

 

ADVLOOKUP 함수는 좌측의 값을 찾을 수 있습니다.

세 번째 인자가 0일 경우 두 번째 인자에 지정한 범위에서 찾고

양수를 입력하면 우측값

음수를 입력하면 좌측값을 찾습니다.

 

 동작 모습

 

F1셀의 수식입니다.

=ADVLOOKUP(E1, B1:B5, 1)

 

기준 값 돼지는 B1~B5범위에 있고, 

세 번째 인자 1을 입력하면 우측으로 1칸 떨어진 셀의 값을 찾습니다.

 

세 번째 인자에 -1을 입력한 결과입니다.

 

중복된 값이 있는 것을 찾으려 하면 중복값있음이라고 표시합니다.

B1~B5 범위에 사과는 2개가 있습니다.

어떤 값을 찾는 지 정확하지 않아 중복값있음을 표시합니다.

 

 전체 코드

 

전체 소스코드는 다음과 같습니다.

Function ADVLOOKUP(search_value, search_range As Range, relative_pos As Integer) As Variant
    Dim i As Long
    Dim reference_col As Range
    Dim duplicate_num As Integer
    Dim last_row As Long
    Dim reference_col_num As Integer
    Dim pos_result As Long

    reference_col_num = search_range.Column

    last_row = search_range.Rows.Count

    Set reference_col = search_range.Columns(1)
    
    duplicate_num = 0

    For i = 1 To last_row
        If reference_col.Cells(i) = search_value Then
            duplicate_num = duplicate_num + 1
            If duplicate_num = 1 Then
                pos_result = i
            End If
        End If
    Next i

    If duplicate_num = 0 Then
        ADVLOOKUP = CVErr(xlErrNA)
    ElseIf duplicate_num > 1 Then
        ADVLOOKUP = "중복값있음"
    Else
        If relative_pos = 0 Then
            ADVLOOKUP = search_range.Cells(pos_result, 1)
        Else
            ADVLOOKUP = Cells(search_range.Cells(pos_result, 1).Row, reference_col_num + relative_pos)
        End If
    End If
End Function

 

반응형

 참고사항

 

만약 두번째 인자에 여러 열을 범위로 하는 값을 넣어도

첫 번째 열을 기준으로 계산을 합니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기