VLOOKUP 함수의 모든 것: 다른 시트의 데이터 정확하게 불러오기

 

브이룩업 사용법, 다른 시트 데이터 가져오기, VLOOKUP 열 번호, #N/A 오류 해결, 엑셀 매칭 함수

엑셀을 사용하는 직장인들의 실무 능력을 평가할 때 "VLOOKUP 함수를 쓸 줄 아는가?"는 일종의 합격 기준선처럼 여겨집니다. 그만큼 실무에서 가장 많이 쓰이고, 알아두면 퇴근 시간을 몇 시간씩 앞당겨주는 기적 같은 함수이기 때문입니다.

VLOOKUP은 한마디로 "수많은 데이터가 쌓인 거대한 표에서, 내가 원하는 기준을 가지고 오른쪽으로 눈을 돌려 정확한 값을 찾아오는 함수"입니다. 예를 들어 '사원번호'만 입력하면 다른 시트에 있는 사원 명부에서 해당 사원의 '부서'와 '연봉'을 자석처럼 알아서 찾아와 채워주는 식입니다.

저 역시 초보 시절, 수백 명의 거래처 명단에 일일이 사업자등록번호를 채워 넣기 위해 두 개의 시트를 번갈아 켜놓고 Ctrl + F(찾기)를 수백 번 누르며 눈물겨운 수작업을 했던 적이 있습니다. 나중에 이 VLOOKUP 함수를 배우고 나서 5분 만에 작업이 끝나는 것을 보고 허탈함과 동시에 엑셀의 위대함을 깨달았습니다. 오늘은 복잡해 보이지만 원리만 알면 정말 쉬운 VLOOKUP의 4가지 필수 공식을 완벽하게 정리해 드리겠습니다.

1. 묻지도 따지지도 말고 외우는 VLOOKUP 4단 공식

VLOOKUP 함수는 괄호 안에 총 4가지 정보(인수)를 쉼표로 구별해서 넣어주어야 합니다. 이 순서만 머릿속에 기억해 두세요.

Excel
=VLOOKUP(① 찾을 기준값, ② 찾을 원본 표 범위, ③ 가져올 열 번호, ④ 정확도)

  • ① 찾을 기준값: "무엇을 기준으로 찾을 것인가?"입니다. 내가 현재 가지고 있는 단서(예: 사원번호, 제품코드, 이름 등)가 있는 셀을 클릭합니다.

  • ② 찾을 원본 표 범위 (F4 필수!): "어디서 찾을 것인가?"입니다. 진짜 데이터들이 모여 있는 원본 표의 영역을 마우스로 드래그합니다. 이때, 내가 찾고자 하는 '기준값'이 무조건 원본 표의 '첫 번째 열(맨 왼쪽)'에 위치하도록 범위를 잡아야 하는 철칙이 있습니다. 그리고 이전 편에서 배운 대로, 수식을 아래로 복사해도 원본 표가 도망가지 않도록 범위를 잡은 직후 반드시 F4 키를 눌러 절대참조($) 자물쇠를 채워야 합니다.

  • ③ 가져올 열 번호: "원본 표에서 몇 번째 칸에 있는 값을 가져올 것인가?"입니다. 내가 지정한 원본 표 범위(②)의 맨 왼쪽 열을 1번으로 시작하여, 내가 가져오고 싶은 데이터(예: 부서, 단가 등)가 오른쪽으로 몇 번째 열에 있는지를 숫자로 적어줍니다.

  • ④ 정확도: "얼마나 정확하게 찾을 것인가?"입니다. 실무의 99%는 소수점이나 비슷한 값이 아닌 '정확히 일치하는 값'을 찾아야 하므로, 고민하지 말고 숫자 0 또는 FALSE를 입력하시면 됩니다.

2. 실전 매칭: 제품 코드로 단가 불러오기 단계별 가이드

실제 예시를 통해 수식을 완성해 보겠습니다. 현재 [A시트]에는 제품코드만 적혀있고 단가가 비어있습니다. [B시트]에는 모든 제품의 코드와 단가가 정리된 원본 마스터 표가 있습니다.

  1. [A시트]의 단가를 채워 넣을 첫 번째 칸을 클릭하고 =VLOOKUP(을 입력합니다.

  2. ① 기준값 지정: 내 옆 칸에 있는 제품코드 셀(예: A2)을 클릭하고 쉼표(,)를 찍습니다.

  3. ② 원본 범위 지정: [B시트]로 넘어가서 제품코드부터 단가까지 포함된 원본 표 전체를 마우스로 드래그합니다. 그 직후 키보드의 F4를 눌러 주소를 $A$2:$C$100 형태로 고정한 뒤 쉼표(,)를 찍습니다.

  4. ③ 열 번호 지정: 원본 표에서 '제품코드'가 1번 열이고, 내가 가져올 '단가'가 오른쪽으로 3번째 열에 있다면 숫자 3을 적고 쉼표(,)를 찍습니다.

  5. ④ 정확도 입력: 정확히 일치하는 값을 찾아야 하므로 숫자 0을 입력하고 괄호를 닫습니다.

  6. 완성된 수식은 다음과 같으며, 엔터를 누른 뒤 아래로 드래그하면 모든 단가가 마법처럼 채워집니다. =VLOOKUP(A2, [B시트]!$A$2:$C$100, 3, 0)

3. VLOOKUP이 내뱉는 가장 흔한 오류: #N/A 의 원인

VLOOKUP을 쓰다 보면 가장 자주 마주치는 오류 메시지가 바로 #N/A입니다. 이는 'Not Available'의 약자로 "당신이 준 기준값에 맞는 데이터를 원본 표에서 아무리 찾아봐도 존재하지 않는다"라는 엑셀의 대답입니다. 이 오류가 뜨면 다음 세 가지를 점검해 보세요.

첫째, 원본 표에 진짜로 그 데이터가 없는 경우입니다. 오타가 났거나 신규 데이터라 누락되었는지 확인하세요. 둘째, 원본 범위를 지정할 때 F4(절대참조)를 누르지 않아 수식을 아래로 복사하면서 원본 표의 범위가 아래로 밀려 내려가 데이터를 못 찾는 경우입니다. 셋째, 눈에는 똑같이 보여도 한쪽 표에는 '문자 숫자'로, 다른 쪽 표에는 '진짜 숫자'로 저장되어 있어 엑셀이 서로 다른 데이터로 인식하는 경우입니다. (이럴 땐 2편에서 배운 숫자로 변환하는 기술이 필요합니다.)

[핵심 요약 3줄]

  • VLOOKUP 함수는 =VLOOKUP(기준값, 원본범위, 열번호, 0)의 4단계 구조로 작동하며, 흩어진 표의 데이터를 매칭하여 불러올 때 사용한다.

  • 원본 범위를 지정할 때는 기준값이 반드시 맨 왼쪽 열에 있어야 하며, 수식 복사 시 범위가 틀어지지 않도록 무조건 F4를 눌러 절대참조로 고정해야 한다.

  • 결과창에 #N/A 오류가 뜬다면 원본 표에 기준값이 실제로 누락되었거나, F4 고정을 빠뜨렸거나, 두 표의 데이터 형식(문자/숫자)이 일치하지 않는지 확인해야 한다.

댓글

댓글 쓰기

이 블로그의 인기 게시물

[6편] 머리말과 꼬리말 활용법 — 문서 상하단 정보 정갈하게 배치하기

 보고서나 논문, 긴 안내문처럼 페이지 수가 많은 문서를 검토하다 보면 문서 상단이나 하단에 반복적으로 표시되는 정보들을 볼 수 있습니다. 문서 제목, 단원명, 회사명, 작성자 정보, 작성 일자 같은 요소들입니다. 이러한 상하단 정보를 매 페이지마다 수동으로 텍스트 상자를 만들어 넣거나 본문에 직접 타이핑하는 것은 엄청난 시간 낭비일 뿐만 아니라, 본문 서식이 바뀔 때마다 위치가 틀어져 큰 혼란을 초래합니다. 한글(HWP) 프로그램에서는 이 영역을 '머리말'과 '꼬리말'이라는 별도의 고정 영역으로 관리합니다. 초보자 시절에는 머리말 영역을 어떻게 열고 닫는지 몰라 본문 수정 중에 계속 커서가 머리말 안으로 얽혀 들어가 애를 먹는 경우를 흔히 겪습니다. 하지만 머리말과 꼬리말의 개념을 정확히 잡고 본문과 분리하여 제어할 줄 알게 되면, 문서 전체의 통일감과 격식을 손쉽게 끌어올릴 수 있습니다. 1. 머리말/꼬리말 만들기(Ctrl + N, H)와 기본 구조 한글 문서에 머리말이나 꼬리말을 넣는 기본 단축키는 Ctrl + N을 누른 상태에서 H 를 누르는 것입니다. 단축키를 누르면 [머리말/꼬리말] 설정 창이 뜨게 됩니다. (1) 위치 및 종류 선택 위치: 머리말(문서 상단) 또는 꼬리말(문서 하단) 중 작성하고자 하는 영역을 선택합니다. 종류: '양 쪽', '한 쪽', '홀수 쪽', '짝수 쪽'을 선택할 수 있습니다. 일반적인 보고서라면 '양 쪽'을 선택하여 모든 페이지에 동일한 상하단 정보가 표시되도록 설정합니다. 마당 선택: 기본 작성 외에도 파일 이름, 작성 일자, 쪽 번호 등을 자동으로 불러오는 양식을 선택할 수도 있습니다. [만들기] 버튼을 누르면 본문 영역이 살짝 옅은 회색으로 바뀌면서 상단이나 하단에 점선으로 된 머리말/꼬리말 전용 작성 영역이 나타납니다. 2. 정갈한 머리말 디자인과 실무 작성 팁 머리말은 독자의 시선이 문서에 들어설 때 처음 닿...
자세한 내용 보기

[9편] 각주와 주석 달기 — 참고문헌 및 전문 용어 깔끔하게 정리하기

 보고서나 논문, 기획서를 읽다가 낯선 전문 용어나 생소한 약어를 만나면 독자는 순간적으로 글의 흐름을 놓치게 됩니다. 그렇다고 본문 중간에 용어의 뜻이나 긴 출처 정보를 길게 늘어놓으면, 글의 진짜 핵심 내용이 묻히고 문장이 산만해지기 쉽습니다. 문서의 전달력을 떨어뜨리지 않으면서도 전문적인 정보와 출처를 명확하게 제공하는 가장 깔끔한 방법이 바로 '각주(Footnote)'와 '미주(Endnote)' 기능을 활용하는 것입니다. 처음 문서 작업을 할 때는 본문 하단에 직접 선을 그리고 숫자를 적어 수동으로 주석을 달기도 합니다. 하지만 이 경우 본문 내용이 수정되어 페이지가 밀리면 주석의 위치가 틀어지고 번호가 엉키는 대참사가 발생합니다. 한글(HWP) 프로그램의 자동 각주 기능을 이용하면, 본문이 아무리 수정되어도 번호와 위치가 알아서 정돈됩니다. 오늘은 문서의 신뢰도를 높여주는 각주와 미주 활용법을 완벽하게 정리해 드리겠습니다. 1. 각주(Ctrl + N, N)와 미주(Ctrl + N, E)의 차이점 주석은 크게 각주 와 미주 두 가지로 나뉩니다. 두 기능 모두 본문에 조그만 주석 번호를 달아주는 것은 같지만, 설명글이 출력되는 위치에 차이가 있습니다. (1) 각주 (Ctrl + N, N) 위치: 주석 번호가 달린 해당 페이지의 맨 아랫부분 에 설명이 표시됩니다. 용도: 생소한 전문 용어 해설, 본문 내용을 보충하는 짧은 부연 설명, 참고문헌 출처 표기 등에 가장 흔하게 쓰입니다. (2) 미주 (Ctrl + N, E) 위치: 페이지와 상관없이 문서의 맨 마지막 장(최하단)에 모든 주석이 한꺼번에 모여서 표시됩니다. 용도: 페이지마다 아래쪽 여백을 차지하는 것이 부담스럽거나, 문서 전체의 참고문헌 목록을 맨 뒤로 한데 모아 깔끔하게 정리하고 싶을 때 주로 사용합니다. 일반적인 기업 보고서나 레포트에서는 독자가 해당 페이지를 읽는 즉시 용어를 확인할 수 있도록 '각주(Ctrl + N, N)'를 사용하는 것이...
자세한 내용 보기

[2편] 글꼴과 문단 모양 정복 — 보기 좋은 보고서의 텍스트 배치 원칙

 문서의 내용을 아무리 알차게 작성하더라도, 눈으로 볼 때 글자가 빼곡하거나 서식이 들쭉날쭉하면 읽는 사람에게 지루하고 가독성이 떨어지는 인상을 주기 쉽습니다. 반대로 깔끔하게 정리된 글꼴과 균형 잡힌 문단 모양은 문서의 신뢰도를 즉시 끌어올려 줍니다. 처음 한글(HWP)로 보고서를 작성할 때 가장 많이 하는 실수는 '글자 크기만 키우거나 줄 간격만 넓혀서' 페이지 수를 채우려는 시도입니다. 저 역시 초보 시절에는 강조하고 싶은 문장마다 서로 다른 색상과 폰트를 남발했다가, 상사에게 "문서가 너무 산만해서 핵심이 눈에 들어오지 않는다"는 지적을 받기도 했습니다. 가독성 높은 문서를 만드는 핵심은 정교한 텍스트 배치와 규칙적인 서식 적용에 있습니다. 오늘은 한글 문서의 완성도를 한 단계 올려주는 글꼴 선택 기준과 문단 모양(Alt + T) 설정 테크닉을 정리해 보겠습니다. 1. 보고서에 적합한 가독성 높은 글꼴(Font) 조합 문서에서 글꼴을 고를 때는 화려함보다 '눈의 피로도'와 '가독성'을 최우선으로 고려해야 합니다. 한 문서 안에서 사용되는 글꼴 종류는 최대 2~3가지 이내 로 제한하는 것이 원칙입니다. 제목 및 헤드라인: 시선을 사로잡아야 하므로 획이 두껍고 명확한 돋움(고딕) 계열을 사용합니다. 추천 글꼴: 맑은 고딕(Bold), 나눔고딕(Bold), Pretendard, KoPub돋움체 본문 내용: 오래 읽어도 눈이 피로하지 않고 주간 간격이 일정하게 유지되는 정갈한 폰트를 선택합니다. 추천 글꼴: 맑은 고딕(Regular), 함초롬돋움, 나눔바른고딕 많은 분들이 기본 설정인 '함초롬바탕'이나 '바탕체'를 그대로 사용하시지만, 모니터 화면으로 문서를 검토하는 요즘 환경에서는 명조(바탕) 계열보다 돋움(고딕) 계열 이 훨씬 선명하고 깔끔하게 읽힙니다. 2. 문단 모양 설정(Alt + T)으로 답답한 여백 줄이기 단순히 Enter 키를 여러 번 눌러 줄 바꿈을 ...
자세한 내용 보기