VLOOKUP 함수 응용 예제

이래서 업무 표준화(내지는 작업 표준화)가 필요한 것입니다. 어느 회사에서 청소용 비품 관리를 하는데, 어떤 때에는 A사에서 만든 짧은 빗자루만 잔뜩 사오고, 또 어떤 때에는 B사의 긴 빗자루를 사오고, 이번 달에는 비오는 수요일에 구매를 하였으니까 빨간 빗자루를, 다음 달에는 파란 빗자루만 사오고, 한 마디로 구매하는 담당자의 구입 당시 기분 내지는 성향에 따라 매번 구매 형태가 달라진다면 비품 재고 관리가 제대로 되기가 어려울 것입니다.

데이터 베이스를 구축할 때에도 마찬가지입니다. 정보를 입력할 때에는 항상 표준을 정해 두어야 나중에 이런 불필요한 경우를 당하지 않게 됩니다. DB를 구축할 때에는 바로 이런 규칙을 정하는 단계에서 가장 많은 시간을 투자하고 또 가장 어려운 작업이기도 합니다.

예를 들어 전화번호를 입력할 때, 철수는 02-709-1113, 영희는 02)709-1212, 갑돌이는 02 836 1297, 영식이는 (02)646-5355… 이런 식으로 입력하는 사람에 따라 제각각 입력을 한다면 나중에 아주 곤란한 경우를 당하게 됩니다. 심한 말로 얘기를 하자면 이런 데이터는 정보가 아니라 Garbage Data(쓰레기 정보) 입니다. ^^;

물론 오늘 질문하신 분의 경우에는 이것과는 상황이 틀린 경우입니다. 아래의 두 데이터를 보면, 테이블 중에서 서로 연결되는 고리(즉 사번)의 형태가 틀리긴 합니다만, 한 테이블 내에서는 형태가 동일합니다. 따라서 약간의 테크닉만 있으면 해결이 가능합니다.

[표 1]

[표 2]

[표 1]의 사번은 앞에 식별자가 없는 반면, [표 2]에서는 앞에 P가 모두 붙어 있습니다. 따라서 이 상태에서 VLOOKUP 함수를 사용하게 되면 보시는 바와 같이 #N/A 에러(Not Available), 즉 "아무리 찾아 보아도 그런 데이터는 도무지 찾을 수가 없다우!" 하는 에러 메시지를 표시해 주는 것입니다.

이런 경우에는 & 연산자를 사용하여 문자열을 먼저 합친 다음 VLOOKUP 함수를 사용하여 아래와 같이 하시면 되겠습니다.

=VLOOKUP("P"&A2,Source!$B$2:$F$27,3,FALSE)

그렇다면 테이블의 형태가 반대로 되어 있다면 어떻게 해야 할까요? 즉 [표 1]에는 P43734와 같이 사번 앞에 식별자가 붙어 있고 [표 2]에서는 식별자가 없다고 할 경우에는? 이런 경우에는 위의 경우보다 약간 복잡한 단계를 거칩니다만 가능합니다.

=VLOOKUP(VALUE(RIGHT(A9,LEN(A9)-1)),Source!$B$30:$F$55,3,FALSE)

RIGHT, LEN, VALUE라는 세 가지 텍스트 함수를 사용하시면 됩니다.

정리 — VLOOKUP 기준값 형식 맞추기

상황	해결 수식
기준값에 식별자를 붙여야 할 때	=VLOOKUP("P"&A2,Source!$B$2:$F$27,3,FALSE)
기준값에서 식별자를 떼어내야 할 때	=VLOOKUP(VALUE(RIGHT(A9,LEN(A9)-1)),Source!$B$30:$F$55,3,FALSE)

자주 묻는 질문 (FAQ)

마치며

데이터를 입력할 때 표기 형식을 통일해 두는 것이 가장 좋지만, 이미 형식이 어긋난 데이터를 다뤄야 할 때는 오늘 소개한 & 연산자와 RIGHT·LEN·VALUE 함수 조합으로 충분히 해결할 수 있습니다. 여러 상황에 응용해 보시기 바랍니다.