엑셀을 사용하다 보면 다른 표에 있는 정보를 찾아 현재 작업 중인 표에 자동으로 가져와야 하는 경우가 많습니다. 예를 들어 거래처 코드만 입력되어 있는 자료에 거래처명을 자동으로 표시하거나, 상품코드를 기준으로 상품명과 가격을 찾아 입력해야 하는 경우가 있습니다.
이런 업무를 하나씩 직접 찾아서 입력하면 데이터가 많아질수록 시간이 오래 걸리고 입력 실수가 발생할 가능성도 높아집니다. 이때 활용할 수 있는 대표적인 엑셀 함수가 바로 VLOOKUP 함수입니다.
VLOOKUP 함수는 엑셀에서 특정 값을 기준으로 다른 표에서 원하는 데이터를 찾아 가져올 때 사용하는 함수입니다. 특히 거래처 관리, 상품 관리, 직원 정보 관리, 매출 자료 정리 등 다양한 직장 업무에서 활용도가 높습니다.
이번 글에서는 엑셀 VLOOKUP 함수의 기본 개념부터 함수 작성 방법, 실무 예제, 자주 발생하는 오류와 해결 방법까지 초보자도 이해하기 쉽게 정리해 보겠습니다.
VLOOKUP 함수란 무엇인가?
VLOOKUP은 Vertical Lookup의 약자로, 세로 방향으로 데이터를 검색하여 원하는 값을 찾아주는 함수입니다.
쉽게 설명하면 “이 값을 기준으로 다른 표에서 관련된 정보를 찾아줘”라고 엑셀에 요청하는 기능이라고 생각하면 됩니다.
예를 들어 다음과 같은 상품 목록이 있다고 가정해 보겠습니다.
상품코드 | 상품명 | 가격
A001 | 노트북 | 1,200,000
A002 | 모니터 | 350,000
A003 | 키보드 | 80,000
A004 | 마우스 | 40,000
주문서에 상품코드만 입력되어 있고 상품명과 가격을 자동으로 가져오고 싶다면 VLOOKUP 함수를 사용할 수 있습니다.
상품코드 A002를 기준으로 상품 목록에서 A002를 찾으면 해당 행에 있는 “모니터”와 “350,000”이라는 정보를 가져올 수 있습니다.
VLOOKUP 함수 기본 구조
VLOOKUP 함수의 기본적인 문법은 다음과 같습니다.
=VLOOKUP(찾을값, 데이터범위, 열번호, 검색방법)
각 항목이 어떤 의미인지 이해하면 VLOOKUP 함수를 훨씬 쉽게 사용할 수 있습니다.
찾을값은 다른 표에서 검색할 기준이 되는 값입니다. 예를 들어 상품코드나 직원번호, 거래처코드 등이 여기에 해당합니다.
데이터범위는 검색할 전체 표의 범위를 의미합니다.
열번호는 데이터를 가져올 열의 순서를 의미합니다.
검색방법은 정확하게 일치하는 값을 찾을 것인지, 비슷한 값을 찾을 것인지 결정하는 부분입니다.
실무에서는 정확하게 일치하는 데이터를 찾는 경우가 많기 때문에 대부분 FALSE 또는 0을 사용하는 경우가 많습니다.
VLOOKUP 기본 사용법
앞에서 살펴본 상품 목록을 기준으로 VLOOKUP 함수를 작성해 보겠습니다.
상품코드가 A002이고 상품명이 자동으로 표시되도록 하려면 다음과 같이 입력할 수 있습니다.
=VLOOKUP(A2,$F$2:$H$5,2,FALSE)
여기에서 A2는 찾으려는 상품코드입니다.
$F$2:$H$5는 상품코드, 상품명, 가격이 들어 있는 데이터 범위입니다.
2는 해당 범위에서 두 번째 열의 값을 가져오라는 의미입니다.
FALSE는 상품코드가 정확하게 일치하는 경우에만 결과를 가져오라는 의미입니다.
따라서 A2에 A002가 입력되어 있다면 결과에는 “모니터”가 표시됩니다.
VLOOKUP으로 가격 자동으로 가져오기
상품명뿐만 아니라 상품 가격도 VLOOKUP을 이용해 자동으로 가져올 수 있습니다.
상품 가격이 세 번째 열에 있다면 다음과 같이 입력할 수 있습니다.
=VLOOKUP(A2,$F$2:$H$5,3,FALSE)
앞의 수식과 비교하면 열번호만 2에서 3으로 변경되었습니다.
VLOOKUP 함수에서 열번호는 선택한 데이터 범위에서 몇 번째 열의 데이터를 가져올 것인지 결정합니다.
따라서 상품코드가 첫 번째 열이고 상품명이 두 번째 열, 가격이 세 번째 열이라면 상품명은 2, 가격은 3을 입력하면 됩니다.
VLOOKUP에서 데이터 범위를 고정하는 이유
VLOOKUP 함수를 사용할 때 초보자가 자주 놓치는 부분이 데이터 범위입니다.
예를 들어 다음과 같이 수식을 작성할 수도 있습니다.
=VLOOKUP(A2,F2:H5,2,FALSE)
그런데 이 수식을 아래쪽 셀로 복사하면 데이터 범위가 함께 이동할 수 있습니다.
이를 방지하려면 데이터 범위를 절대참조로 고정하는 것이 좋습니다.
=VLOOKUP(A2,$F$2:$H$5,2,FALSE)
여기서 $ 기호가 붙어 있는 것이 중요합니다.
$F$2:$H$5는 수식을 다른 셀에 복사하더라도 동일한 데이터 범위를 유지합니다.
실무에서는 VLOOKUP 함수를 여러 행에 적용하는 경우가 많기 때문에 데이터 범위를 고정하는 습관을 들이는 것이 좋습니다.
VLOOKUP 함수에서 FALSE를 사용하는 이유
VLOOKUP 함수의 마지막 부분에는 검색방법을 입력합니다.
대표적으로 FALSE와 TRUE가 사용됩니다.
FALSE는 정확하게 일치하는 값을 찾습니다.
TRUE는 정확하게 일치하는 값이 없을 경우 비슷한 값을 찾는 방식입니다.
거래처 코드, 상품 코드, 직원 번호처럼 정확한 값을 찾아야 하는 업무에서는 일반적으로 FALSE를 사용하는 것이 안전합니다.
예를 들어 상품코드 A002를 찾는데 A002와 정확하게 일치하는 데이터가 없으면 결과를 표시하지 않도록 하려면 FALSE를 사용하는 것이 적합합니다.
따라서 VLOOKUP을 처음 배우는 단계라면 “정확한 값 찾기에는 FALSE”라고 기억해두면 이해하기 쉽습니다.
VLOOKUP 실무 예제 1: 거래처명 자동 입력
회사에서 거래처 코드를 기준으로 거래처명을 자동으로 입력해야 한다고 가정해 보겠습니다.
거래처 정보가 다음과 같이 구성되어 있습니다.
거래처코드 | 거래처명 | 담당자
C001 | 한국상사 | 김철수
C002 | 대한유통 | 이영희
C003 | 미래기업 | 박민수
주문서에는 거래처 코드만 입력되어 있다고 가정하겠습니다.
주문서의 거래처 코드가 A2에 있고 거래처 정보가 F2:H4에 있다면 다음과 같은 수식을 사용할 수 있습니다.
=VLOOKUP(A2,$F$2:$H$4,2,FALSE)
그러면 거래처 코드를 기준으로 거래처명을 자동으로 가져올 수 있습니다.
VLOOKUP 실무 예제 2: 담당자 자동 입력
같은 거래처 정보를 이용해 담당자 이름도 자동으로 가져올 수 있습니다.
담당자가 세 번째 열에 있기 때문에 다음과 같이 작성하면 됩니다.
=VLOOKUP(A2,$F$2:$H$4,3,FALSE)
이렇게 하면 거래처 코드만 입력해도 거래처명과 담당자 정보를 자동으로 표시할 수 있습니다.
수백 개의 주문 데이터를 관리해야 하는 상황이라면 이러한 방식으로 반복 입력을 크게 줄일 수 있습니다.
VLOOKUP 실무 예제 3: 직원번호로 부서 찾기
직원 정보를 관리하는 업무에서도 VLOOKUP을 활용할 수 있습니다.
직원번호 | 이름 | 부서
1001 | 김민수 | 영업팀
1002 | 박지영 | 인사팀
1003 | 이준호 | 기획팀
직원번호를 기준으로 부서를 자동으로 표시하고 싶다면 다음과 같은 수식을 사용할 수 있습니다.
=VLOOKUP(A2,$F$2:$H$4,3,FALSE)
직원번호만 입력하면 해당 직원의 부서가 자동으로 표시됩니다.
VLOOKUP 실무 예제 4: 상품코드로 가격 가져오기
상품코드와 가격 정보가 별도의 표에 있을 때도 VLOOKUP을 사용할 수 있습니다.
상품코드 | 상품명 | 판매가격
P001 | 키보드 | 50000
P002 | 마우스 | 30000
P003 | 모니터 | 300000
주문서에 상품코드가 입력되어 있다면 다음과 같은 수식을 사용할 수 있습니다.
=VLOOKUP(A2,$F$2:$H$4,3,FALSE)
이렇게 하면 상품코드에 해당하는 판매가격을 자동으로 불러올 수 있습니다.
상품 가격이 변경되었을 때 기준표의 가격만 수정하면 VLOOKUP 결과에도 변경된 가격이 반영되기 때문에 가격표 관리에도 유용합니다.
VLOOKUP 함수에서 자주 발생하는 #N/A 오류
VLOOKUP을 사용하다 보면 “#N/A”라는 오류가 나타나는 경우가 있습니다.
이 오류는 대부분 찾으려는 값이 기준 데이터 범위에서 발견되지 않았을 때 발생합니다.
예를 들어 주문서에 상품코드가 A005라고 입력되어 있는데 상품 목록에는 A001부터 A004까지만 있다면 VLOOKUP은 A005를 찾을 수 없기 때문에 #N/A 오류를 표시합니다.
이 경우 먼저 찾으려는 값이 기준표에 실제로 존재하는지 확인해야 합니다.
또한 숫자와 문자가 서로 다른 형식으로 저장되어 있는 경우에도 검색 결과가 제대로 나오지 않을 수 있으므로 데이터 형식도 확인하는 것이 좋습니다.
VLOOKUP 함수에서 자주 발생하는 #REF! 오류
#REF! 오류는 VLOOKUP의 열번호가 실제 데이터 범위를 벗어났을 때 발생할 수 있습니다.
예를 들어 데이터 범위가 F2:H10이라면 해당 범위에는 세 개의 열만 있습니다.
그런데 다음과 같이 열번호에 4를 입력하면 문제가 발생합니다.
=VLOOKUP(A2,$F$2:$H$10,4,FALSE)
선택한 범위가 세 개의 열로 구성되어 있기 때문에 네 번째 열은 존재하지 않습니다.
이 경우 열번호를 실제 범위에 맞게 수정해야 합니다.
VLOOKUP 함수 사용 시 가장 중요한 규칙
VLOOKUP을 사용할 때 가장 중요한 규칙 중 하나는 “찾을 값이 선택한 범위의 가장 왼쪽 열에 있어야 한다”는 것입니다.
예를 들어 데이터 범위가 F2:H10이라면 F열이 검색 기준이 되는 열이어야 합니다.
상품코드를 기준으로 상품명을 찾고 싶다면 상품코드가 데이터 범위의 첫 번째 열에 있어야 합니다.
이 규칙 때문에 VLOOKUP을 사용하면서 원하는 결과가 나오지 않는 경우가 있습니다.
이러한 구조적인 제한은 VLOOKUP을 사용할 때 반드시 기억해야 합니다.
VLOOKUP 함수와 IFERROR 함수 함께 사용하기
VLOOKUP을 사용하면서 오류 메시지가 화면에 그대로 표시되는 것이 보기 좋지 않은 경우에는 IFERROR 함수를 함께 사용할 수 있습니다.
예를 들어 다음과 같이 사용할 수 있습니다.
=IFERROR(VLOOKUP(A2,$F$2:$H$10,2,FALSE),”확인 필요”)
이렇게 하면 VLOOKUP 결과가 정상적으로 나오면 해당 값을 표시하고, 검색할 데이터를 찾지 못해 오류가 발생하면 “확인 필요”라는 문구를 표시할 수 있습니다.
업무용 보고서나 고객에게 전달해야 하는 자료에서는 오류 메시지를 그대로 보여주는 것보다 필요한 안내 문구를 표시하는 방식이 보다 깔끔할 수 있습니다.
VLOOKUP 함수를 사용할 때 주의할 점
VLOOKUP은 매우 편리하지만 몇 가지 주의할 점이 있습니다.
첫 번째는 검색 기준이 되는 데이터의 형식을 통일해야 한다는 것입니다. 숫자와 문자 형식이 서로 다르면 같은 값처럼 보여도 검색되지 않을 수 있습니다.
두 번째는 검색 기준이 되는 값에 불필요한 공백이 들어가 있지 않은지 확인해야 합니다. 예를 들어 “A001″과 “A001 “은 화면에서는 비슷하게 보이지만 실제 데이터에는 차이가 있을 수 있습니다.
세 번째는 데이터 범위를 정확하게 지정해야 합니다. 특히 여러 행에 수식을 복사해서 사용하는 경우에는 절대참조를 활용해 데이터 범위를 고정하는 것이 좋습니다.
네 번째는 VLOOKUP으로 가져오는 데이터의 열번호를 정확하게 입력해야 합니다. 열번호가 잘못되면 원하지 않는 데이터가 표시될 수 있습니다.
VLOOKUP 함수는 직장인에게 왜 유용할까?
직장에서 엑셀을 사용하는 가장 큰 목적 중 하나는 반복적인 데이터 입력과 정리 작업을 효율적으로 처리하는 것입니다.
VLOOKUP을 활용하면 코드나 번호를 기준으로 다른 표에 있는 정보를 자동으로 가져올 수 있기 때문에 수작업으로 데이터를 검색하고 입력하는 시간을 줄일 수 있습니다.
특히 거래처 관리, 상품 관리, 재고 관리, 고객 정보 관리, 직원 정보 관리, 매출 자료 정리 등 다양한 업무에 활용할 수 있습니다.
데이터의 양이 많아질수록 VLOOKUP의 활용 효과는 더욱 커질 수 있습니다.
마무리
엑셀 VLOOKUP 함수는 특정 값을 기준으로 다른 데이터에서 원하는 정보를 찾아오는 대표적인 업무용 함수입니다.
기본적인 사용 방법은 다음과 같이 정리할 수 있습니다.
=VLOOKUP(찾을값, 데이터범위, 열번호, FALSE)
여기서 찾을값은 검색할 기준이 되는 데이터이고, 데이터범위는 검색 대상이 되는 표입니다. 열번호는 가져올 데이터의 위치이며 FALSE는 정확하게 일치하는 값을 찾는다는 의미입니다.
VLOOKUP을 처음 접하는 사람이라면 함수의 구조를 모두 외우려고 하기보다는 상품코드로 상품명을 찾거나 직원번호로 부서를 찾는 것처럼 간단한 실무 예제부터 연습하는 것이 좋습니다.
특히 데이터 범위를 절대참조로 고정하는 방법과 정확한 일치를 위해 FALSE를 사용하는 방법을 익혀두면 실무에서 VLOOKUP을 보다 안정적으로 활용할 수 있습니다.
엑셀을 업무에서 자주 사용하는 직장인이라면 VLOOKUP 함수는 한 번 익혀두었을 때 활용도가 높은 기능입니다. 반복적으로 다른 표에서 데이터를 찾아 입력하고 있다면 VLOOKUP을 활용해 작업 과정을 자동화하고 불필요한 반복 작업을 줄여보는 것이 좋습니다.
답글 남기기