실무 함수의 꽃, VLOOKUP 완벽 정복 (더 이상 에러값은 없다)

신입 시절, 500명의 직원 이름이 적힌 명단과, 그들의 사번과 연락처가 적힌 또 다른 명단을 하나로 합치라는 지시를 받았습니다. 그때의 저는 VLOOKUP이라는 함수를 몰랐기 때문에, 모니터 두 대를 띄워놓고 이름 하나하나를 검색(Ctrl+F)해가며 복사하고 붙여넣는 단순 노동을 무려 3시간 동안이나 했습니다. 눈은 빠질 것 같았고, 중간에 줄을 잘못 맞춰 엉뚱한 사람의 연락처를 넣는 실수까지 저질렀죠.

나중에 선배가 단 10초 만에 함수 하나로 두 명단을 완벽하게 합치는 모습을 보고 느꼈던 허탈함과 경이로움은 지금도 잊을 수 없습니다. 직장인 엑셀은 VLOOKUP을 알기 전과 후로 나뉜다고 해도 과언이 아닙니다. 이 함수 하나만 제대로 다룰 줄 알아도 3시간 걸릴 야근을 3분으로 단축할 수 있습니다. 오늘은 엑셀 초보자들의 가장 큰 장벽이자 실무 함수의 꽃인 VLOOKUP을 아주 쉽게, 그리고 에러 없이 완벽하게 사용하는 법을 파헤쳐 보겠습니다.

VLOOKUP은 똑똑한 '도서관 사서'입니다

복잡한 영어 수식에 겁먹을 필요 없습니다. VLOOKUP(Vertical Lookup)은 수직으로 데이터를 나열한 표에서 내가 원하는 값을 대신 찾아주는 똑똑한 사서라고 생각하면 쉽습니다.

우리가 도서관 사서에게 책을 찾아달라고 할 때 어떻게 말하나요? "사서님, '해리포터'라는 책이(찾을 기준값), 저기 소설 코너(찾을 범위)의, 세 번째 책장(가져올 열 번호)에 있나요? 비슷한 거 말고 딱 그 책으로 찾아주세요(정확도)."라고 말할 것입니다. VLOOKUP 함수도 이와 똑같이 딱 4가지의 질문에만 답을 채워주면 됩니다.

=VLOOKUP(찾을 기준값, 찾을 범위, 가져올 열 번호, 0)

이 4가지 공식의 의미를 실무 상황에 맞춰 하나씩 대입해 보겠습니다.

4가지 괄호 채우기 실전 가이드

  1. 찾을 기준값 (Lookup_value): '누구'를 기준으로 찾을 것인가? 가장 먼저 선택해야 할 것은 내가 찾고자 하는 기준점입니다. 예를 들어 내 표에 '홍길동'이라는 직원이 있다면, 홍길동이 적힌 셀(예: A2)을 클릭합니다. 주의할 점은 이 기준값은 두 표에 공통으로 존재해야 한다는 것입니다. 동명이인이 있을 수 있으므로 이름보다는 고유한 '사번'이나 '제품 코드'를 기준값으로 잡는 것이 훨씬 안전합니다.

  2. 찾을 범위 (Table_array): '어디서' 찾을 것인가? (★핵심) 데이터를 가져올 원본 표의 범위를 마우스로 드래그하여 지정합니다. 여기서 초보자들이 가장 많이 하는 치명적인 실수가 두 가지 있습니다. 첫째, 찾을 범위의 '첫 번째 열(가장 왼쪽)'에는 반드시 앞서 지정한 기준값(사번 또는 이름)이 있어야 합니다. VLOOKUP은 무조건 범위의 첫 열에서만 기준값을 찾기 때문입니다. 둘째, 범위를 지정한 직후에는 무조건 키보드의 F4 키를 눌러 절대 참조($)로 묶어주어야 합니다. 그래야 수식을 아래로 자동 채우기 할 때 원본 표의 범위가 밑으로 밀려 내려가는 대참사를 막을 수 있습니다.

  3. 가져올 열 번호 (Col_index_num): '몇 번째' 데이터를 가져올 것인가? 방금 지정한 범위 안에서 내가 진짜로 가져오고 싶은 데이터(예: 연락처)가 왼쪽에서부터 몇 번째 기둥(열)에 있는지 숫자로 적습니다. 첫 번째 열이 이름(1), 두 번째가 부서(2), 세 번째가 연락처(3)라면 숫자 '3'을 적어주면 됩니다.

  4. 정확도 (Range_lookup): 비슷한 값을 찾을까, 똑같은 값을 찾을까? 마지막 칸에는 숫자 '0' (또는 FALSE)을 입력하고 괄호를 닫아줍니다. 0은 '정확히 일치하는 값만 찾아라'라는 뜻입니다. 간혹 숫자 1을 넣거나 생략하는 경우가 있는데, 그러면 엑셀이 엉뚱하고 비슷한 데이터를 마음대로 가져오므로 일반적인 사무 업무에서는 무조건 '0'을 쓴다고 외워두시면 마음이 편합니다.

왜 내 VLOOKUP은 항상 #N/A 에러가 뜰까?

수식을 완벽하게 썼는데도 얄미운 '#N/A(Not Available)' 에러가 뜬다면, 십중팔구 아래 3가지 원인 중 하나입니다.

첫 번째 원인은 앞서 강조한 'F4(절대 참조)'를 누르지 않아 참조 범위가 아래로 계속 밀려 내려갔기 때문입니다. 첫 번째 셀은 값이 잘 나왔는데 아래로 드래그할수록 에러가 뜬다면 100% 이 경우입니다.

두 번째 원인은 '보이지 않는 띄어쓰기(공백)' 때문입니다. 내 표에는 "홍길동"이라고 적혀 있는데, 원본 데이터에는 "홍길동 "처럼 이름 뒤에 스페이스바가 한 칸 들어가 있다면 엑셀은 이를 완전히 다른 글자로 인식해 찾지 못합니다. 이럴 때는 지난 2편에서 배운 '찾기 및 바꾸기(Ctrl+H)'를 이용해 공백을 모두 제거해 주어야 합니다.

세 번째 원인은 텍스트와 숫자의 형식이 다르기 때문입니다. 겉보기엔 똑같은 사번 '1234'라도, 내 표는 숫자 형식이고 원본 표는 텍스트 형식이라면 에러가 납니다. 두 데이터의 셀 서식을 똑같이 통일해 주면 마법처럼 에러가 사라집니다.

  • 핵심 요약

  1. VLOOKUP은 =VLOOKUP(기준값, 찾을 범위, 열 번호, 0) 4가지 요소만 순서대로 채워 넣으면 되는 조건부 검색 함수입니다.

  2. 참조할 원본 범위를 지정할 때는 무조건 F4 키를 눌러 절대 참조($)로 고정해야 수식이 엉키지 않습니다.

  3. #N/A 에러가 발생한다면 참조 범위 고정 누락, 보이지 않는 띄어쓰기, 숫자와 텍스트 형식 불일치 문제를 가장 먼저 확인해야 합니다.

  • 다음 편 예고 VLOOKUP은 정말 훌륭하지만 치명적인 단점이 하나 있습니다. 바로 기준값이 무조건 왼쪽 첫 번째 열에 있어야 하고, 오른쪽으로만 데이터를 찾을 수 있다는 점입니다. 다음 5편에서는 VLOOKUP의 이러한 한계를 완벽하게 부수고, 왼쪽 오른쪽 앞뒤 가리지 않고 데이터를 찾아내는 'INDEX와 MATCH 함수의 환상 조합'을 배워보겠습니다.

  • 독자님을 위한 질문 엑셀을 다루시면서 VLOOKUP 에러 때문에 가장 당황했거나 진땀을 뺐던 경험이 있으신가요? 그때 에러의 원인이 무엇이었는지 댓글로 남겨주시면 다른 분들에게도 큰 도움이 될 것입니다!

댓글

댓글 쓰기

이 블로그의 인기 게시물

식재료 보관의 신: 대파부터 고기까지, 오래 보관하는 소분법

지난 2편에서 우리는 마트의 유혹을 뿌리치고 꼭 필요한 만큼만 장을 보는 데 성공했습니다. 자, 이제 양손 무겁게 집에 돌아왔습니다. 여러분은 지금 사 온 식재료를 어떻게 하시나요? 아마 열에 아홉은 장바구니에 있던 비닐봉지 그대로 냉장고 빈칸에 쑤셔 넣고 피곤한 몸을 뉘이실 겁니다. 하지만 이 사소한 행동이 식비 낭비의 가장 큰 주범이라는 사실을 아시나요? 장을 보고 온 직후의 30분, 귀찮음을 이겨내는 이 짧은 시간이 식재료의 수명을 3일에서 3주로 늘려주는 골든타임입니다. 오늘은 1인 가구가 가장 자주 버리게 되는 대표 식재료들의 수명을 극적으로 늘려주는 실전 소분법을 공유합니다. 1. 마트 포장 용기는 세균의 온상입니다 가장 먼저 해야 할 일은 마트에서 담아준 포장재를 모두 벗겨내는 것입니다. 특히 고기가 담겨 있는 검은색 스티로폼 랩 포장은 겉보기엔 깔끔해 보이지만, 유통 과정에서 수많은 사람의 손을 거치며 세균에 노출되었을 확률이 높습니다. 게다가 공기가 드나들기 쉬워 식재료의 수분을 빼앗고 산화를 촉진합니다. 집에 도착하자마자 모든 식재료는 비닐과 스티로폼에서 탈출시켜야 합니다. 이때 필요한 무기는 딱 두 가지입니다. 공기를 차단해 줄 '지퍼백(또는 밀폐용기)'과 수분을 조절해 줄 '키친타월'입니다. 2. 1인 가구의 웬수, 대파와 마늘 완벽 보관법 한국인 밥상에 빠지지 않지만 1인 가구가 다 먹기엔 항상 양이 많은 대파와 마늘. 이 녀석들의 최대 적은 바로 '수분'입니다. 대파: 흙을 깨끗이 씻어낸 후, 물기가 완벽하게 마를 때까지 건조하는 것이 핵심입니다. (물기가 남으면 금방 짓무릅니다.) 그 후 용도에 맞게 송송 썰거나 길게 썰어 밀폐용기에 담습니다. 이때 바닥과 뚜껑 쪽에 키친타월을 한 장씩 깔아두면 타월이 습기를 흡수해 한 달 가까이 싱싱하게 보관할 수 있습니다. 오래 두고 먹을 절반은 지퍼백에 넓게 펴서 냉동실로 보냅니다. 깐 마늘: 밀폐용기 바닥에 설탕을 1cm 두께로 깔고 키친타월을 덮은 ...
자세한 내용 보기

장보기의 기술: 버려지는 식재료를 줄이는 스마트한 소량 구매 전략

1편에서 우리 집 냉장고를 비우고 식습관을 진단해보셨나요? 아마 유통기한이 지나 쓰레기통으로 향하는 식재료들을 보며 적지 않은 충격을 받으셨을 겁니다. 식비 절약의 핵심은 단순히 싼 재료를 사는 것이 아니라, '내가 산 식재료를 100% 소비하는 것'에 있습니다. 많은 1인 가구가 장보기 단계에서부터 실패를 경험합니다. 마트의 할인 유혹에 넘어가 '1+1' 상품을 집어 들거나, 무심코 큰 용량의 재료를 사서 결국 반 이상 버리게 되는 것이죠. 오늘은 1인 가구의 장보기 실패를 원천 차단하는 스마트한 소량 구매 전략을 정리해 드립니다. 1. '1+1'은 1인 가구에게 가장 비싼 상품입니다 마트에 가면 '2개 사면 30% 할인'이나 '1+1' 같은 문구가 우리를 유혹합니다. 1인 가구의 가장 큰 실수는 이 할인율에 속아 필요 이상의 양을 구매하는 것입니다. 생각해보세요. 대파 한 단을 할인받아 2,000원에 샀다고 칩시다. 하지만 혼자 사는 사람이 일주일에 소비할 수 있는 대파의 양은 정해져 있습니다. 다 못 먹고 절반을 버린다면, 결과적으로 나는 2,000원을 주고 1,000원어치 대파만 먹은 셈입니다. 할인받은 금액보다 버리는 식재료의 값이 더 크다면, 그것은 절대 경제적인 장보기가 아닙니다. 1인 가구에게 진정한 할인은 '내가 기한 내에 다 먹을 수 있는 적정량'을 사는 것입니다. 2. 장보기 전 '3-Day 리스트' 만들기 매주 한 번씩 몰아서 장을 보는 것이 효율적이라고 생각하시나요? 하지만 경험상 1인 가구에게 일주일 치 식단은 너무 깁니다. 갑작스러운 야근, 친구와의 약속, 배달 음식의 유혹이 변수로 작용하기 때문입니다. 이제부터는 '일주일'이 아닌 '3일 치' 리스트를 작성해보세요. 3일은 내 냉장고의 식재료가 신선하게 유지되는 기간이자, 내 식사 계획을 현실적으로 통제할 수 있는 최적의 시간 단위입니다. 3일 뒤에 무엇을...
자세한 내용 보기

계좌만 파면 끝? 방치된 IRP/ISA에 생기를 불어넣을 실전 상품 매수 가이드

연말정산 혜택을 위해, 혹은 비과세라는 말에 이끌려 큰맘 먹고 IRP와 ISA 계좌를 개설하셨나요? 매월 꼬박꼬박 자동이체까지 걸어두었다면 노후 준비의 절반은 해내신 셈입니다. 하지만 여기서 안심하기엔 이릅니다. 과거의 제가 그랬듯, 많은 직장인들이 계좌에 돈을 입금한 것만으로 모든 투자가 끝났다고 착각합니다. 1년 뒤 수익률을 확인해 보려 앱을 열었을 때, 수익률이 0%대 머물러 있어 당황했던 기억이 생생합니다. 그 이유는 제가 입금한 돈이 어떤 금융 상품도 사지 않은 채 '현금성 대기 자금(예수금)' 상태로 덩그러니 방치되어 있었기 때문입니다. ISA와 IRP는 돈을 불려주는 요술 램프가 아니라, 금융 상품을 담는 '빈 장바구니'일 뿐입니다. 오늘은 빈 장바구니에 어떤 물건을 담아야 할지 막막한 4050 세대를 위해, 복잡한 주식 공부 없이도 안전하게 계좌에 생기를 불어넣는 실전 상품 매수 가이드를 정리해 드립니다. 1. 개별 주식의 유혹을 버려라 "삼성전자 사면 되나요?", "요즘 테슬라가 좋다던데 ISA로 살 수 있나요?" 계좌를 막 개설한 분들이 가장 많이 하는 질문입니다. 결론부터 말씀드리면, 우리의 피 같은 노후 자금은 개별 기업의 흥망성쇠에 운명을 맡겨서는 안 됩니다. 특히 4050 세대는 투자 실패 시 이를 만회할 시간적 여유가 부족합니다. 한 기업의 악재로 자산이 반토막 나는 리스크를 피하려면, 수십에서 수백 개의 우량 기업을 하나의 바구니에 담아 파는 상품을 고르는 것이 정답입니다. 고민할 것 없이 아래 두 가지 선택지만 기억하시면 됩니다. 2. 귀찮은 게 싫다면: TDF (Target Date Fund) 투자 공부할 시간도 없고, 매번 앱을 들여다보며 스트레스받기 싫은 분들에게 최적화된 상품이 바로 TDF입니다. TDF는 내 은퇴 예상 연도(Target Date)에 맞춰 전문가가 알아서 주식과 채권의 비중을 조절해 주는 '자율주행 펀드'입니다. 상품명 뒤에 ...
자세한 내용 보기