사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 영어 대/소문자를 구분해 참조하기

 

 

 

 

 

대/소문자를 구분해 값을 참조할 필요가 있다면 EXACT 함수와 INDEX, MATCH 함수를 중첩해서 사용하는 배열수식을 작성해야 한다. 배열수식이라고 해도 이전의 INDEX, MATCH 함수 구성과 같기 때문에 쉽게 사용할 수 있다.

 

EXACT함수는 두 텍스트 값을 비교해 같으면 TRUE, 틀리면 FLASE를 반환한다. 특히 영어 대/소문자를 구분해 비교할 수 있다. 함수의 사용법은 다음과 같다.

 

=EXACT(텍스트1, 텍스트2)

 

대소문자구분.xlsx

0.01MB

 

위의 첨부파일을 열면 아래와 같은 표를 확인할 수 있다.

 

 

[F1] 셀에 입력된 제품 코드를 이용해 왼쪽 표의 [C2:C11] 범위의 가격을 참조해 오는 수식을 입력한다. 이때 주의할 점은 [A2:A11] 범위의 제품 코드에는 대/소문자가 구분되어 입력되었다는 것이다.

 

 

[F2] 셀을 선택하고 아래의 수식을 입력하면 [C9] 셀이 아니라 [C8] 셀을 참조하는 것을 확인할 수 있다.

 

=VLOOKUP(F1,A2:C11,3,FALSE)

 

이 수식은 [F1] 셀의 값으로 [A2:C11] 범위의 첫 번째 열(A2:A11)에서 정확하게 일치하는 첫 번째 값을 찾고(FALSE 옵션), 찾은 값과 같은 행에 위치한 [A2:C11] 범위의 세 번째 열(C2:C11) 값을 반환한다. 수식 구성으로만 보면 문제가 없지만, VLOOKUP 함수는 [F1] 셀의 '찾을 값'을 찾을 때 대/소문자를 구별하지 못한다. 그러므로 [A2:A11] 범위에서 [F1] 셀의 값과 첫 번째 값이 입력된 [A8] 셀을 찾아 잘못된 가격을 참조한다.

 

 

대/소문자 구분해 값을 참조하도록 [F2] 셀의 수식을 다음과 같이 수정한다. 수식을 입력할 때는 배열수식으로 인식되도록 [Ctrl + Shift + Enter]를 눌러 수식을 입력한다.

 

=INDEX(C2:C11,MATCH(TRUE,EXACT(F1,A2:A11),0))

 

MATCH 함수를 보자.

 

MATCH(TRUE,EXACT(F1,A2:A11),0)

 

값을 값은 TRUE이고, 이 값을 EXACT(F1,A2:A11)의 결과에서 찾는다. 앞에서 설명한 것과 같이 EXACT 함수는 두 값을 서로 비교해 값이 정확한지 확인한 후 TRUE 또는 FALSE를 반환한다. TRUE의 위치만 찾으면 대/소문자를 구분해 값의 위치를 찾을 수 있다. 이때 [A2:A11] 범위의 값과 [F1] 셀의 값을 일반수식에서는 비교할 수 없으므로 배열로 변환해 처리할 수 있도록 배열수식으로 입력한다.

 

이렇게 개/소문자를 수분해 위치를 찾을 수 있도, INDEX 함수로 [C2:C11] 범위의 값 중에서 MATH 함수로 확인한 값의 위치와 같은 행의 값을 참조한다.