vlookup함수를 사용할 때 주의해야 할 사항 3가지

Today's Agenda

vlookup을 쓰면서 자주하게 되는 실수 유형 세 가지

엑셀에서 가장 유용하게 쓰이는 함수 중에 하나가 바로 vlookup 일 것 입니다. 사용 빈도가 많은 만큼, 원하는 결과가 나오지 않거나 에러가 발생해서 고생하는 경우가 많은 것도 사실입니다. 그래서 오늘은 vlookup을 사용하면 자주 하게 되는 실수 유형을 정리하고 주의해야 할 사항을 적어볼 까 합니다. 

1. 참조영역을 설정할 때 절대값으로 지정하지 않는 경우    

아래 예제 C2셀을 보면, VLOOKUP함수를 통해 개개인의 국가에 해당하는 국가번호를 자동으로 가져오게 하고 있습니다. 홍길동씨의 국가(한국)에 대한 국가번호를 잘 가져오고 있으므로 아무 이상이 없는 것 같습니다.

 

하지만 드래그해서 아래로 카피해 내려오면, 양조위씨의 국가번호를 찾는 데서 #N/A, 즉 값을 찾지 못 했다고 나옵니다. 분명 참조영역에는 중국은 86 이라고 되어 있는데 말이죠. 

이때 원인은 바로 참조영역을 상대적으로 지정해둔데 있습니다. C4셀에 가서 내용을 살펴보면, VLOOKUP(B4,F6:G8,2,FALSE)라고 되어 있는 걸 확인하실 수 있습니다. B4셀에 있는 내용을 F6:G8영역에서 찾으라는 것인데, F6:G8이면 원래 의도했던 참조영역이 아닌 다른 곳이네요. 

흔히 처음 함수를 입력하는 셀에서는 문제가 생기지 않고, 드래그해서 카피해갈 때도 즉각적으로 문제가 생기지 않기 때문에 종종 왜 틀렸는지 인식하는 데 시간이 걸리기도 합니다. 그러므로 VLOOKUP함수를 쓰실 때 참조영역은 반드시 $를 붙인 절대 영역으로 지정해주시는 습관(F4를 눌러주시면 됩니다.)을 들이는 것이 좋습니다.

2. 참조영역을 너무 작게 지정하는 경우 

예제 C2셀을 보시면, B2셀의 값을 토대로 참조영역에서 국가번호를 찾아오게 잘 작성한 것 같은데 결과 값은 #REF!입니다. 분명히 1번에서 얘기한 것처럼 절대값으로도 잘 지정한 것 같고, 국가값 기준 2번째 칸에 있는 값을 불러오는 것이이 국가번호를 가져오라고도 제대로 되어 있는 것 같습니다. 

이상하죠?

에러의 원인은 바로 참조영역을 F열:F열로만 잡고 있다는 것입니다. 제대로 하려면 F열:G열까지 했어야 겠죠. 물론 예제라 좀 어거지 같다고 하실 수 있겠지만, 상당히 넓은 참조 영역을 사용하다 보면 방금과 같은 상황에 종종 맞딱드리게 됩니다. 그러므로 VLOOKUP에서 #REF!를 만나셨다. 그럼, 참조영역을 제대로 설정했는지 다시 한 번 살펴보시기 바랍니다. 

그리고, 당연히 참조영역을 위아래로 너무 짧게 설정하셔도 #N/A에러를 만나실 수 있습니다. 아래 예제에서 C3셀을 보시면 참조영역이 F4:G5로 일본이 있는 6행을 포함하고 있지 않습니다. 

3. FALSE를 빼지 말아주세요.

엑셀에서 제공하는 VLOOKUP함수 설명에 보면, 4번째 입력값은 option으로 되어 있습니다. 그러나 실무적으로는 NEVER, 절대 필수입니다. 아래 예제를 보실까요? 가군,나군,다군이라는 별명에 해당하는 번호를 찾아오게 만들었습니다. 가군과 다군은 각각 82, 86 제대로 된 번호를 찾아 오는데, 나군은 ... 나군은 이상하게 82를 찾아 오고 있습니다. 

에러가 나는 것은 아니기 때문에 단번에 눈에 띄지 않아서 실무적으로는 문제가 될 가능성이 더 큰 경우라고 할 수 있겠죠. 이런일이 생기면 곤란하겠죠? 

원인은 VLOOKUP(B4,$F$4:$G$6,2)처럼 마지막 인수에 FALSE를 넣지 않아서 입니다. 마지막 FALSE가 무슨 의미인데 그러느냐? 쉽게 얘기하면 '비슷한 값에 만족해도 괜찮아?'에 대한 대답을 넣는 곳입니다. 

FALSE라고 넣는다면 '엑셀아, 완전히! 똑같은 값을 찾아야 해."라고 하는 것이고, 넣지 않는다면 '비슷하기만 해도 괜찮아'라는 의미 인 것이죠. 

물론 위의 예제에서 '가'와 '나'가 어디가 비슷하냐고 물으실 수 있습니다. 하지만 컴퓨터가 인공지능은 아니기 때문에, 컴퓨터는  오름차순/내림차순으로 찾아 내려가다가 찾는 값을 OVER하면 그 앞의 값이 제일 비슷하다고 판단하는 것입니다. 즉 '나군'을 찾기 위해 '가군' -> '다군 -> '나군' 의 순서로 찾아가는 것이죠. '가군'을 보고서는 더 찾아 보기로 하지만 '다군'을 만나고 나서는 이미 ㄱㄴㄷ순에서 OVER했기 때문에 '가군'이 제일 비슷하다고 결론 내리고 82를 돌려주는 것입니다. 

결론은 반드시 FALSE를 넣어주세요. 입니다. ( FALSE 대신 0을 넣어주어도 같은 의미 입니다만, 개인적으로는 사람이 이해하기 쉽게 FALSE를 넣는 것을 선호합니다. )