대소문자/특수문자 완벽하게 구분하여 상응하는 값 찾기(LOOKUP류)

INDEX/MATCH나 VLOOKUP은 찾는 값을 기준으로 좌 또는 우측에 있는 데이터를 조회할 수 있으나, 두 가지 명확한 한계를 가집니다. 첫째, 대소문자를 구분하지 못하며("ID-01"과 "id-01"을 같은 값으로 취급), 둘째, 물결표(~), 별표(*) 같은 특수 문자를 와일드카드로 인식하여 예기치 않은 결과를 반환할 수 있습니다.

이러한 문제를 해결하고, 데이터의 대소문자나 특수문자까지 100% 정확하게 일치하는 값을 찾고 싶을 때 INDEX, MATCH, EXACT 함수의 조합을 사용할 수 있습니다. .

이 함수 조합의 반환 결과는 INDEX 함수의 첫 번째 인수인 '반환할_영역'에 있는 데이터의 타입(텍스트, 숫자 등)과 같습니다.

함수 조합 사용법

이 함수 조합은 각 문자를 개별적으로 비교하는 배열 연산을 수행하므로, 배열 수식으로 입력해야 합니다.

{=INDEX(반환할_영역, MATCH(TRUE, EXACT(찾는값_셀, 찾을_영역), 0))}

• EXACT(찾는값_셀, 찾을_영역): '찾는값_셀'의 값과 '찾을_영역'의 각 셀 값을 하나씩 대소문자까지 구분하여 비교한 후, TRUE 또는 FALSE 값의 배열을 생성합니다.
• MATCH(TRUE, ..., 0): EXACT 함수가 생성한 배열에서 첫 번째 TRUE 값의 위치를 찾습니다.
• {=...}: 이 수식은 일반적인 수식이 아니므로, 입력을 완료할 때 반드시 Ctrl + Shift + Enter 키를 함께 눌러야 합니다. 수식이 중괄호 { }로 감싸지면 배열 수식으로 올바르게 입력된 것입니다. (Microsoft 2021 이상에서는 Enter로 입력 가능합니다.)

함수 예제 및 설명

예제 데이터

다음은 대소문자가 섞인 제품 ID와 특수문자가 포함된 ID를 관리하는 목록입니다. 표준 INDEX/MATCH로는 정확한 조회가 어렵습니다.

사용 예시

A7 셀에 있는 "A-123" ID의 '상태'를 B7 셀에 정확히 조회하고자 합니다. 만약 일반적인 MATCH 함수를 사용했다면, 대소문자를 구분하지 못해 A2 셀의 "a-123"을 먼저 찾아 "정상"이라는 잘못된 값을 반환했을 것입니다.

B7 셀에 아래와 같이 배열 수식을 입력합니다.

{=INDEX(B2:B4, MATCH(TRUE, EXACT(A7, A2:A4), 0))}

수식을 입력하고 Ctrl + Shift + Enter를 누르면, "a-123"을 건너뛰고 "A-123"과 정확히 일치하는 3행의 '상태' 값인 "특별관리" 가 올바르게 반환됩니다.

계산 과정 단계별 설명

Excel이 위 수식을 계산하는 과정은 다음과 같습니다.

1. EXACT 함수 실행: EXACT(A7, A2:A4)

   • 이 단계가 이 조합의 핵심입니다. A7 셀의 값("A-123")을 A2:A4 범위의 각 셀과 순서대로, 대소문자까지 완전히 일치하는지 비교합니다.
   • EXACT("A-123", "a-123") → FALSE (대소문자가 다름)
   • EXACT("A-123", "A-123") → TRUE (완벽히 일치)
   • EXACT("A-123", "B-456*") → FALSE (값이 다름)
   • 중간 결과: {FALSE; TRUE; FALSE} 라는 논리값 배열이 메모리에 생성됩니다.

2. MATCH 함수 실행: MATCH(TRUE, {FALSE; TRUE; FALSE}, 0)

   • EXACT 함수가 만든 논리값 배열 {FALSE; TRUE; FALSE} 안에서 TRUE 값이 몇 번째 위치에 있는지 찾습니다.
   • TRUE는 배열의 두 번째에 있으므로, MATCH 함수는 2를 반환합니다.
   • 중간 결과: 2 (숫자)

3. INDEX 함수 실행: INDEX(B2:B4, 2)

   • 최종적으로 값을 가져올 '상태' 영역(B2:B4)에서, MATCH 함수가 알려준 2번째 위치의 값을 반환합니다.
   • B2:B4 범위의 두 번째 값은 "특별관리"입니다.
   • 최종 결과: "특별관리" (텍스트)

기타 고려 사항

• 배열 수식(Array Formula) 필수: 이 함수 조합은 구버전 Excel(2019 이전)에서는 반드시 Ctrl + Shift + Enter로 입력해야 정상적으로 작동합니다. 수식 입력줄에 { }가 자동으로 생기는지 확인하는 것이 중요합니다.
• 정확성의 대가, 성능: EXACT 함수는 범위 내의 모든 셀과 한 글자씩 비교하는 연산을 수행합니다. 따라서 조회할 데이터의 양이 수천, 수만 행으로 매우 많아질 경우, 일반 INDEX/MATCH보다 통합 문서의 계산 속도를 느리게 만들 수 있습니다. 꼭 필요한 경우에 사용하는 것이 좋습니다.