[Excel] 01. Vlookup 대소문자 구분하는 방법

VLOOKUP(Vertical Lookup) 함수는 Excel에서 가장 일반적으로 사용되는 함수 중 하나로, 특정 값을 기준으로 데이터 범위에서 해당 값을 찾아 특정 열의 데이터를 반환하는 역할을 합니다. VLOOKUP 함수의 구문과 사용법을 설명하겠습니다.

VLOOKUP 함수의 구문

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: 찾으려는 값을 나타내는 셀 또는 값입니다.
table_array: 데이터베이스 역할을 하는 범위(테이블)입니다. 찾으려는 값을 포함하고 있는 범위입니다.
col_index_num: 검색 결과를 반환할 열의 인덱스 번호입니다. table_array 범위 내에서 몇 번째 열인지를 지정합니다. 1부터 시작합니다.
range_lookup (선택사항): 이 매개변수는 논리값(TRUE 또는 FALSE)으로, 정확하게 일치하는지 검색할지 여부를 설정합니다. TRUE 또는 생략하면 근사치를 검색합니다.

VLOOKUP 함수 사용 예시

예를 들어, A2 셀에 있는 값을 기준으로 B2:C10 범위에서 해당 값을 찾아 C열의 데이터를 반환하려면 다음과 같이 VLOOKUP 함수를 사용합니다

=VLOOKUP(A2, B2:C10, 2, FALSE)

위 예제에서 lookup_value는 A2, table_array는 B2:C10, col_index_num은 2(두 번째 열, 즉 C열), range_lookup는 FALSE로 설정하여 정확하게 일치하는 값을 검색하도록 지정했습니다.

VLOOKUP 함수는 주로 정렬된 데이터를 검색할 때 사용되며, 대소문자를 구분하지 않고 근사치 검색을 하려면 VLOOKUP 함수의 대체로 INDEX와 MATCH 함수를 활용할 수 있습니다. INDEX와 MATCH 함수의 조합은 더욱 유연하고 강력한 검색 기능을 제공합니다.

Excel의 VLOOKUP 함수는 기본적으로 대소문자를 구분하지 않습니다. 따라서 VLOOKUP 함수 자체로는 대소문자를 구분하여 검색하는 것이 불가능합니다.

그래서 대소문자를 구분하여 검색하려면 다른 방법을 사용해야 합니다. 아래는 대소문자를 구분하여 검색하는 방법 중 하나인 배열 수식(Array Formula)을 사용하는 방법입니다.

대소문자를 구분하여 검색하는 방법

배열 수식 사용하기

배열 수식을 사용하여 대소문자를 구분하여 검색하려면 아래와 같은 과정을 따릅니다.

검색하려는 값: A2
데이터베이스 역할을 하는 범위: B2:B10 (이 예제에서는 열 B에 데이터가 있다고 가정합니다.)

원하는 결과를 출력할 셀에 다음과 같은 수식을 입력합니다.

=INDEX(B2:B10, MATCH(TRUE, EXACT(A2, B2:B10), 0))

위 수식은 대소문자를 구분하여 A2의 값을 범위 B2:B10에서 찾고, 해당 값을 C2:C10에서 반환합니다. 배열 수식처럼, 이 수식을 입력할 때 Ctrl+Shift+Enter 키를 눌러 배열 수식으로 입력해야 합니다. 이렇게 하면 수식 주위에 중괄호 {}가 추가됩니다. 중괄호가 보이지 않더라도 Ctrl+Shift+Enter를 눌렀는지 확인하세요.

위 수식은 VLOOKUP과 달리 대소문자를 구분하여 검색하며, 데이터베이스 범위(B2:B10)에서 A2의 값을 정확하게 일치하는지 확인하기 위해 EXACT 함수를 사용합니다. MATCH 함수는 TRUE와 EXACT 함수를 조합하여 정확한 일치를 찾습니다. 그리고 INDEX 함수를 사용하여 해당 결과를 반환합니다.

이 방법을 다른 셀로 복사하여 다른 검색 결과를 얻을 수 있습니다. INDEX와 MATCH 함수를 활용하면 VLOOKUP보다 더 강력한 검색 기능을 구현할 수 있으며, 대소문자를 구분하거나 다른 복잡한 조건을 활용하여 데이터를 찾을 수 있습니다.

아래와 같이 VLOOKUP에서는 대소문자를 구분하지 못하지만 INDEX와 MATCH 함수를 활용하면 대소문자를 구분하여 데이터를 찾을 수 있습니다.

VBA 사용하기

VBA를 사용하여 Excel의 VLOOKUP 함수를 대소문자를 구분하여 구현할 수 있습니다. VBA를 사용하면 사용자 정의 함수를 작성하여 VLOOKUP 함수와 유사한 동작을 수행할 수 있습니다.

아래는 VBA를 사용하여 대소문자를 구분하여 VLOOKUP과 유사한 동작을 수행하는 사용자 정의 함수의 예시 코드입니다.

먼저, 엑셀 파일을 열고 “개발자” 탭에서 “Visual Basic”을 클릭하여 VBA 편집기를 엽니다.
VBA 편집기에서 “삽입” 메뉴에서 “모듈”을 선택하여 새 모듈을 추가합니다.
아래의 VBA 코드를 모듈에 추가합니다:

Function VLOOKUP_CaseSensitive(lookup_value As String, table_array As Range, col_index_num As Integer) As Variant
    Dim cell As Range
    Dim lookup_value_lower As String

    lookup_value_lower = LCase(lookup_value)
    
    For Each cell In table_array.Columns(1)
        If LCase(cell.Value) = lookup_value_lower Then
            VLOOKUP_CaseSensitive = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    
    VLOOKUP_CaseSensitive = "Not Found"
End Function