엑셀 VLOOKUP 함수: 브이룩업 기초부터 오류 없이 활용하는 방법
검색값과 표 범위만 제대로 잡으면, 반복 입력을 줄이고 필요한 정보를 빠르게 불러올 수 있습니다.
VLOOKUP은 무엇을 하는 함수인가
VLOOKUP은 표의 가장 왼쪽 열에서 기준값을 찾고, 같은 행의 다른 열 값을 가져오는 함수입니다. 상품 코드로 상품명이나 단가를 찾고, 사번으로 부서나 직급을 불러오고, 학번으로 성적을 확인할 때 특히 유용합니다. 핵심은 사람이 눈으로 표를 찾아 옮기는 일을 수식으로 바꾸는 데 있습니다. 기준값이 바뀌면 결과도 자동으로 갱신되므로 복사·붙여넣기에서 생기는 오입력을 줄일 수 있습니다. 다만 VLOOKUP은 검색 범위의 첫 열에서만 값을 찾으므로 표를 만들 때 기준 열의 위치를 먼저 확인해야 합니다.
처음 사용할 때는 함수 이름보다 데이터의 구조를 살피는 편이 안전합니다. 검색할 값은 어느 셀에 있는지, 원본 표는 어디서 시작하는지, 가져올 정보는 표의 몇 번째 열인지 종이에라도 적어 보세요. 표에 빈 행이나 병합 셀이 많으면 결과를 읽기 어렵고, 숫자처럼 보이는 코드가 텍스트로 저장되어 있으면 같은 값도 찾지 못할 수 있습니다. VLOOKUP은 마법처럼 데이터를 정리해 주는 기능이 아니라, 정리된 표에서 같은 기준으로 값을 되찾는 도구라는 점을 기억하면 실수가 줄어듭니다.
기본 문법을 네 부분으로 나누기
기본 문법은 =VLOOKUP(찾을값, 표범위, 열번호, 일치방식)입니다. 찾을값은 현재 표에서 입력하거나 선택한 상품 코드처럼 검색의 출발점입니다. 표범위는 그 코드를 포함한 원본 표 전체이며, 반드시 기준 열이 맨 왼쪽에 있어야 합니다. 열번호는 표범위의 왼쪽부터 세어 결과를 가져올 열의 번호입니다. 마지막 일치방식은 대부분의 업무에서 FALSE를 사용해 정확히 같은 값만 찾도록 설정합니다.
예를 들어 B2에 상품 코드가 있고 F2:H20에 코드·상품명·단가 표가 있다면, 상품명을 가져오는 수식은 =VLOOKUP(B2,$F$2:$H$20,2,FALSE)처럼 쓸 수 있습니다. 여기서 달러 기호는 아래로 수식을 복사해도 원본 표 범위가 움직이지 않도록 고정합니다. 열번호 2는 F열이 첫 번째, G열이 두 번째이기 때문입니다. 단가를 가져오려면 같은 범위에서 3을 사용합니다. 수식을 입력한 뒤에는 코드 하나만 바꾸어 예상한 행의 값이 바뀌는지 반드시 확인하세요.
| 인수 | 무엇을 넣나 | 확인할 점 |
|---|---|---|
| 찾을값 | 상품 코드 셀 | 숫자·텍스트 형식 통일 |
| 표범위 | 코드부터 결과 열까지 | 기준 열은 맨 왼쪽 |
| 열번호 | 가져올 열 순번 | 범위 안의 번호인지 |
| 일치방식 | FALSE | 코드는 정확 일치 |
표 범위를 잡는 순서
표 범위에는 기준 열과 반환할 열을 모두 넣어야 합니다. 상품 코드가 F열, 상품명이 G열, 단가가 H열이라면 F:H 전체가 아니라 실제 데이터가 있는 F2:H200처럼 머리글과 데이터 범위를 고려해 잡습니다. 머리글을 포함해도 대개 동작하지만, 첫 데이터 행부터 범위를 지정하면 구조를 읽기 쉽습니다. 반대로 결과 열만 선택하거나 기준 열을 빼면 VLOOKUP은 찾을 위치를 잃습니다. 원본 표가 다른 시트에 있다면 시트 이름 뒤에 느낌표를 붙여 상품목록!$A$2:$D$500처럼 참조합니다.
복사할 수식이라면 범위를 절대참조로 고정하는 습관이 중요합니다. $A$2:$D$500의 달러 기호를 빼면 한 행 아래로 수식을 내릴 때 범위도 A3:D501로 밀려 일부 데이터를 놓칠 수 있습니다. 원본 표가 매달 커진다면 표 서식으로 변환하거나, 충분한 여유 행을 포함하되 불필요한 다른 표까지 섞지 마세요. 같은 시트에 표가 여럿 있으면 이름 정의나 표 이름을 사용하면 관리가 편하지만, 우선은 범위가 어디인지 눈으로 확인하는 기본 절차를 익히는 것이 좋습니다.
정확히 일치와 근사 일치의 차이
마지막 인수 FALSE는 정확히 일치하는 값만 찾으라는 뜻입니다. 사번, 주문번호, 제품 코드처럼 하나라도 달라지면 안 되는 데이터에는 FALSE가 기본입니다. 찾을 값이 없을 때 #N/A가 보이는 것은 함수가 고장 난 것이 아니라, 원본 표에 같은 값이 없거나 형식이 다르다는 신호입니다. 이 신호를 숨기기 전에 코드의 앞뒤 공백, 숫자와 텍스트 형식, 원본 범위를 먼저 점검해야 합니다.
TRUE 또는 생략은 근사 일치로 동작합니다. 이 방식은 점수 구간에 따라 등급을 매기거나 수량별 할인율을 찾는 표처럼 기준값이 오름차순으로 정렬되어 있을 때만 사용합니다. 정렬되지 않은 표에서 TRUE를 쓰면 엉뚱한 가까운 값을 반환할 수 있어 특히 위험합니다. 업무용 코드 검색에는 FALSE를 명시적으로 넣고, 근사 일치를 쓸 때는 표의 정렬 규칙과 경계값을 별도 열로 확인하세요. 같은 수식처럼 보여도 마지막 인수 하나가 결과의 신뢰도를 좌우합니다.
실전 예시: 상품 코드로 상품명과 단가 채우기
주문 입력 시트에 상품 코드만 입력하고 상품명과 단가를 자동으로 채우는 상황을 생각해 보세요. 원본 시트에는 코드, 상품명, 단가, 재고 순서로 표를 둡니다. 주문 시트의 상품명 칸에는 코드 셀을 찾을값으로, 원본 표를 표범위로, 상품명 열의 순번을 열번호로 넣습니다. 단가 칸에는 같은 수식에서 열번호만 바꿉니다. 두 수식을 아래 행으로 복사하면 여러 주문을 빠르게 처리할 수 있습니다.
이때 상품 코드는 A001처럼 앞에 0이 없더라도 문자와 숫자가 섞일 수 있으므로 양쪽 열의 서식을 통일해야 합니다. 새 상품을 등록한 뒤 원본 범위 끝을 늘리지 않으면 신규 코드가 #N/A로 보일 수 있습니다. 가격 변경이 잦다면 원본 표만 수정하고 주문 시트의 수식은 건드리지 않는 방식이 안전합니다. 자동으로 나온 값이라도 대량 주문 전에는 임의의 코드 세 개를 골라 원본 표와 비교하세요. 입력 오류를 수식으로 증폭시키지 않는 가장 간단한 검토 방법입니다.
자주 나오는 오류와 해결법
#N/A는 가장 흔한 오류입니다. 원본 표에 값이 없을 때뿐 아니라 찾을값에 보이지 않는 공백이 있거나, 한쪽은 숫자이고 다른 쪽은 텍스트일 때도 생깁니다. 셀을 더블클릭해 앞뒤 공백을 확인하고, TRIM으로 공백을 정리하거나 텍스트 나누기 기능으로 형식을 맞춰 보세요. 값이 실제로 있는지 Ctrl+F로 원본 표에서 먼저 찾는 것도 빠른 방법입니다. 오류를 IFERROR로 빈칸 처리하기 전, 왜 못 찾았는지 기록해야 데이터 누락을 발견할 수 있습니다.
#REF!는 열번호가 표범위의 열 수보다 클 때 주로 나옵니다. F:H 세 열만 선택하고 열번호에 4를 넣으면 네 번째 열이 없으므로 반환할 수 없습니다. #VALUE!는 인수 구분이나 수식 입력이 잘못된 경우를 의심합니다. 결과가 다른 사람의 값과 다르면 절대참조가 빠져 범위가 밀렸는지, TRUE가 들어갔는지, 중복 코드가 있는지를 차례로 봅니다. 오류 메시지를 무조건 없애기보다 원인별로 한 번씩 확인하면 다음 파일에서도 같은 문제를 예방할 수 있습니다.
| 증상 | 주된 원인 | 먼저 할 일 |
|---|---|---|
| #N/A | 값·형식·공백 불일치 | 원본에서 코드 검색 |
| #REF! | 열번호가 범위를 벗어남 | 범위 열 수 확인 |
| 다른 값 | 참조 이동 또는 TRUE | $와 FALSE 확인 |
수식 복사와 절대참조 실수 막기
VLOOKUP을 한 행에서 완성한 뒤 채우기 핸들로 아래로 복사할 때, 찾을값 셀은 행에 맞춰 움직여야 하지만 원본 표 범위는 고정되어야 합니다. 그래서 B2는 그대로 두고 $F$2:$H$200만 고정합니다. F4 키로 참조 형태를 바꾸거나 달러 기호를 직접 넣을 수 있습니다. 수식을 복사한 뒤 중간 행 하나를 선택해 수식 입력줄을 보면, 찾을값은 B3·B4로 변하고 표범위는 동일하게 남아 있어야 합니다.
열 방향으로도 복사할 계획이라면 열번호를 숫자로 고정하기보다 MATCH를 함께 쓰거나, 필요한 결과 열마다 수식을 명확히 작성하는 것이 낫습니다. 초보자가 가장 많이 하는 실수는 원본 범위의 첫 행만 고정하거나 아무것도 고정하지 않는 것입니다. 작은 표에서는 티가 나지 않다가 아래 행에서만 틀린 값이 나와 발견이 늦습니다. 수식 복사 후 첫 행, 중간 행, 마지막 행을 비교하는 세 지점 검사를 체크리스트로 두면 대부분의 참조 오류를 초기에 잡을 수 있습니다.
VLOOKUP이 맞지 않는 경우
VLOOKUP은 기준 열의 오른쪽 값만 가져올 수 있습니다. 예를 들어 상품명이 A열이고 코드가 B열인데 코드로 상품명을 찾아야 한다면, 왼쪽으로 돌아가 값을 가져올 수 없습니다. 이 경우 원본 표 열 순서를 바꾸거나 XLOOKUP, INDEX와 MATCH 같은 대안을 검토합니다. 또한 같은 코드가 여러 번 있다면 VLOOKUP은 첫 번째 행만 반환합니다. 중복 데이터의 모든 값을 합치거나 여러 건을 보여 주는 문제에는 다른 함수나 필터 기능이 더 적합합니다.
파일을 여러 사람이 수정하는 환경에서는 원본 표의 열이 삽입될 때 열번호가 달라질 수 있습니다. 예를 들어 세 번째 열의 단가 앞에 분류 열을 넣으면 기존 수식의 3은 다른 값을 가리킬 수 있습니다. 표 구조 변경 전에는 어떤 수식이 그 범위를 참조하는지 확인하고, 변경 뒤에는 대표 코드로 결과를 테스트하세요. 새 버전의 엑셀을 쓴다면 XLOOKUP은 왼쪽·오른쪽 검색과 찾지 못했을 때의 표시를 더 자연스럽게 처리하지만, 기존 파일과 호환해야 한다면 VLOOKUP의 기본 원리를 알아두는 가치가 있습니다.
검토용 체크리스트와 마무리
VLOOKUP을 입력하기 전에는 찾을값, 원본 표의 첫 열, 반환할 열의 순번, 정확 일치 여부를 순서대로 확인합니다. 입력 뒤에는 표범위가 $ 기호로 고정되었는지, 새 코드와 기존 코드에서 각각 정상 결과가 나오는지 검사합니다. 오류가 보이면 수식을 지우지 말고 원본 데이터와 셀 형식부터 확인하세요. 한 번 제대로 만든 수식은 반복 업무를 줄여 주지만, 데이터가 바뀌는 순간 검토하지 않으면 잘못된 결과도 빠르게 퍼질 수 있습니다.
이 글의 방법은 상품·직원·학생·재고처럼 한 개의 기준값에 한 개의 정보를 연결하는 기본 상황에 맞춥니다. 파일의 보안 설정, 외부 통합 문서 연결, 최신 함수 지원 여부는 사용하는 엑셀 버전과 회사 환경에 따라 다를 수 있습니다. 중요한 정산이나 보고서에는 결과를 원본 표와 대조하고, 수정 이력을 남기는 절차를 더하세요. 기본 문법을 이해한 다음에는 표 서식, 데이터 유효성 검사, 피벗테이블을 함께 익히면 더 안정적인 업무용 파일을 만들 수 있습니다.
파일을 오래 써도 안전하게 관리하는 법
조회 수식이 들어간 파일은 원본 데이터와 입력 데이터의 책임을 분리할수록 안전합니다. 원본 표에는 누가 언제 어떤 기준으로 코드를 추가하거나 수정했는지 알 수 있게 업데이트 날짜와 담당자 열을 둘 수 있습니다. 입력 시트에는 사용자가 직접 고칠 칸과 수식이 들어가는 칸을 색으로 구분하고, 수식 칸은 필요하면 시트 보호로 잠가 두세요. 그래야 결과 셀에 값을 덮어쓰거나 수식을 지우는 사고를 줄일 수 있습니다. 파일을 공유할 때는 복사본을 여러 개 만들기보다 한 곳의 원본을 정하고, 변경 전후에 대표 코드 결과를 비교하는 규칙을 합의하는 편이 좋습니다.
데이터가 커질수록 검색용 코드의 규칙도 중요해집니다. 한 표에서는 A-001, 다른 표에서는 a001처럼 표기법이 섞이면 사람이 보기에는 같아도 함수는 다른 값으로 판단할 수 있습니다. 코드 자릿수, 대소문자, 하이픈 사용, 공백 금지 같은 규칙을 문서 첫 부분에 적고 새 데이터를 넣을 때 검사하세요. 같은 코드가 중복되면 VLOOKUP은 첫 행만 반환하므로, 중복을 허용하는 이유가 있는지와 어느 행이 기준인지도 정해야 합니다. 정리된 데이터와 짧은 검토 절차가 갖춰지면 VLOOKUP은 단순한 함수 이상의 안정적인 업무 흐름이 됩니다.
실무에서 검증 시간을 줄이는 입력 규칙
조회표를 처음 만들 때는 사용자가 직접 입력하는 셀과 수식이 계산하는 셀을 확실히 나누는 것이 좋습니다. 입력 셀에는 연한 색을 사용하고, 코드가 정해진 목록에서만 선택되도록 데이터 유효성 검사를 설정하면 오타를 크게 줄일 수 있습니다. 코드 목록을 원본 표에서 가져오면 새 상품이나 직원이 등록될 때 선택 목록도 함께 관리할 수 있습니다. 수식 결과가 비어 있거나 오류일 때는 담당자가 확인할 수 있도록 별도 상태 열을 두고, 단순히 빈칸으로 감추지 마세요. 특히 정산용 파일에서는 오류 행 수를 세는 작은 점검 칸을 만들어 마감 전에 확인하는 방식이 유용합니다.
파일을 인수인계할 때는 수식 하나만 설명하기보다 원본 표를 어디에 추가하는지, 새 열을 넣으면 열번호를 어떻게 바꾸는지, 오류가 나면 누구에게 확인하는지를 짧게 적어 둡니다. 월별 자료처럼 같은 작업을 반복한다면 지난달 파일을 복사한 뒤 원본 범위와 날짜만 바꾸는 절차도 체크리스트로 남기세요. VLOOKUP 결과는 빠르지만 원본 데이터의 품질을 대신 보장하지는 않습니다. 따라서 중요한 값은 표본으로 대조하고, 코드 중복·공백·형식 차이를 정기적으로 검사해야 합니다. 이런 기본 규칙을 함께 지키면 여러 사람이 사용하는 엑셀 파일에서도 조회 결과를 더 믿을 수 있습니다.
추가 검증 기록
마감 전에는 찾을 코드와 결과값을 세 건 이상 원본 표와 대조하고, 오류 셀 필터로 남은 문제가 없는지 확인합니다. 변경된 표 범위와 담당자를 기록하면 다음 업데이트에서도 같은 확인을 빠뜨리지 않을 수 있습니다.
검증 습관
수식이 맞아도 원본 표의 코드가 잘못되면 결과도 잘못됩니다. 따라서 입력자와 검토자가 서로 다른 행을 확인하고, 수정한 날에는 결과를 PDF나 변경 기록으로 남겨 두는 습관이 필요합니다. 작은 확인 절차가 누적되면 파일 전체의 신뢰도가 높아집니다.
단계형 체크리스트
- 찾을값 셀과 원본 표의 기준 열을 확인합니다.
- 원본 표 전체를 선택하고 범위를 $로 고정합니다.
- 가져올 열의 순번을 왼쪽부터 셉니다.
- 코드 검색에는 FALSE를 명시합니다.
- 첫·중간·마지막 행을 원본과 대조합니다.
자주 묻는 질문
VLOOKUP에서 FALSE는 꼭 써야 하나요?
코드처럼 정확히 같은 값을 찾아야 하면 FALSE를 넣는 것이 안전합니다.
#N/A는 무슨 뜻인가요?
찾을값이 표에 없거나 셀 형식과 공백이 달라 일치하지 않는다는 뜻입니다.
왼쪽 열 값을 가져올 수 있나요?
기본 VLOOKUP은 어렵습니다. 열 순서를 바꾸거나 XLOOKUP 등을 검토하세요.
범위의 달러 표시는 왜 쓰나요?
수식을 복사해도 원본 표 범위가 움직이지 않게 고정합니다.
중복 코드가 있으면 어떻게 되나요?
첫 번째로 발견한 행의 값만 반환하므로 원본의 중복 여부를 먼저 정리하세요.
함께 읽을 글
엑셀 셀 합치기와 병합 단축키, 엑셀 행·열과 셀 참조 이해하기, 엑셀 표를 활용한 일정표 만들기도 같이 보면 좋습니다.
마감 전 결과를 확인하는 방법
수식을 완성한 뒤에는 한 행만 보고 끝내지 말고, 표의 첫 행·중간 행·마지막 행에서 각각 다른 코드를 골라 원본 값과 비교하세요. 원본 표가 자주 갱신되는 파일이라면 새로 추가한 코드가 현재 표 범위 안에 들어 있는지도 함께 확인해야 합니다. 오류가 없더라도 결과 셀의 서식이 통화·날짜·텍스트 중 무엇으로 표시되는지 살피면 보고서에서 생길 수 있는 오해를 줄일 수 있습니다.
공유 파일에서는 수식을 수정할 사람과 원본 데이터를 수정할 사람의 역할을 나누는 편이 좋습니다. 변경 전에는 사본을 남기고, 변경 후에는 오류 표시 셀을 필터로 한 번 확인하세요. 조회 함수의 결과가 맞는지는 결국 원본 데이터의 정확성에 달려 있습니다. 짧은 표본 대조와 변경 기록을 습관으로 만들면 다음 달 같은 파일을 사용할 때도 훨씬 빠르고 안전하게 검토할 수 있습니다.