상품코드를 입력하면 상품명과 가격을 자동으로 불러오거나, 직원번호를 입력하면 이름과 부서를 찾아오는 작업은 VLOOKUP 함수를 사용하면 간단하게 처리할 수 있습니다.
VLOOKUP은 왼쪽 표에서 원하는 값을 찾아 같은 행에 있는 다른 열의 데이터를 가져오는 함수입니다. 실무에서는 특히 정확히 일치하는 값을 찾는 FALSE 옵션을 많이 사용합니다.
1. VLOOKUP 함수는 무엇인가요?
VLOOKUP은 엑셀에서 특정 값을 기준으로 다른 표에서 관련 데이터를 찾아오는 함수입니다.
예를 들어 상품코드가 A열에 있고 상품명과 가격이 각각 B열과 C열에 있다면, 상품코드만 입력해도 해당 상품의 이름이나 가격을 자동으로 가져올 수 있습니다.
• 상품코드 → 상품명 찾기
• 상품코드 → 가격 찾기
• 직원번호 → 직원 이름 찾기
• 사번 → 부서 찾기
• 거래처코드 → 거래처명 찾기
• 학생번호 → 점수 찾기
특히 여러 개의 표를 서로 연결해야 할 때 VLOOKUP을 사용하면 데이터를 일일이 복사하지 않아도 필요한 값을 자동으로 가져올 수 있습니다.
2. VLOOKUP 기본 사용법과 수식 구조
VLOOKUP의 기본적인 형태는 다음과 같습니다.
각 항목은 다음과 같은 의미입니다.
표범위 → 값을 찾을 표 전체 범위
열번호 → 표 범위에서 가져올 데이터가 몇 번째 열인지 지정
찾는방법 → 정확하게 찾을지(FALSE), 비슷한 값을 찾을지(TRUE)
예를 들어 다음과 같은 표가 있다고 가정해 보겠습니다.
| 상품코드 | 상품명 | 가격 |
|---|---|---|
| A001 | 키보드 | 30000 |
| A002 | 마우스 | 20000 |
| A003 | 모니터 | 180000 |
A002를 기준으로 상품명을 찾아오려면 다음과 같이 사용할 수 있습니다.
그러면 결과로 마우스가 표시됩니다.
여기에서 A2:C4가 표의 범위이고, 2는 해당 범위에서 두 번째 열인 상품명을 가져오겠다는 의미입니다.
3. 가장 많이 사용하는 정확한 일치 FALSE
VLOOKUP을 처음 사용할 때 가장 중요하게 알아야 할 부분이 마지막 인수입니다.
FALSE를 사용하면 조회값과 정확히 일치하는 데이터를 찾습니다.
=VLOOKUP(A2,$F$2:$H$100,2,FALSE)
상품코드, 직원번호, 주문번호, 회원번호처럼 정확하게 동일한 값을 찾아야 하는 데이터라면 일반적으로 FALSE를 사용하는 것이 적합합니다.
Microsoft 공식 문서에서도 FALSE 또는 0을 사용하면 첫 번째 열에서 정확한 일치값을 찾도록 지정할 수 있다고 설명합니다.
VLOOKUP에서 마지막 항목을 생략하면 기본적으로 근사값 검색이 사용될 수 있기 때문에, 정확한 값을 찾는 일반적인 업무에서는 FALSE를 명시적으로 입력하는 습관이 편리합니다.
4. 셀에 입력한 값을 기준으로 자동으로 찾아오기
실제로는 수식 안에 상품코드를 직접 입력하기보다 특정 셀에 입력한 값을 기준으로 검색하는 경우가 많습니다.
예를 들어 B2 셀에 상품코드가 있고, F2:H100에 상품정보가 있다면 다음과 같이 사용할 수 있습니다.
이제 B2의 상품코드만 바꾸면 해당 코드에 맞는 상품명이 자동으로 표시됩니다.
B2 → A001 입력 → 키보드 표시
B2 → A002 입력 → 마우스 표시
B2 → A003 입력 → 모니터 표시
이 방식은 입력값만 바꾸면서 여러 데이터를 검색할 수 있기 때문에 재고관리, 견적서, 거래처 관리, 직원 관리 등에 활용하기 좋습니다.
5. 상품명 대신 가격을 가져오려면 열 번호만 바꾸세요
VLOOKUP은 표 범위에서 몇 번째 열의 값을 가져올지 숫자로 지정합니다.
상품코드가 첫 번째 열, 상품명이 두 번째 열, 가격이 세 번째 열이라면 가격을 가져올 때는 열 번호를 3으로 변경하면 됩니다.
예를 들어 B2에 A002가 입력되어 있다면 결과로 20000이 표시됩니다.
첫 번째 열 = 1
두 번째 열 = 2
세 번째 열 = 3
네 번째 열 = 4
중요한 것은 시트 전체의 열 번호가 아니라 VLOOKUP으로 지정한 표 범위의 첫 번째 열을 1로 계산한다는 것입니다.
6. 수식을 아래로 복사할 때 표 범위를 고정하는 방법
VLOOKUP을 여러 행에 적용할 때 자주 발생하는 문제가 표 범위가 같이 움직이는 것입니다.
예를 들어 다음 수식을 사용했다고 가정해 보겠습니다.
이 수식을 아래 셀로 복사하면 F2:H100이 F3:H101처럼 이동할 수 있습니다.
따라서 검색할 표의 범위를 고정하려면 $ 기호를 사용합니다.
이렇게 하면 수식을 아래로 복사해도 검색 범위가 그대로 유지됩니다.
=VLOOKUP(B2,$F$2:$H$100,2,FALSE)
특히 여러 행에 같은 VLOOKUP 수식을 적용해야 한다면 조회 범위를 고정하는 것이 중요합니다.
7. VLOOKUP에서 #N/A 오류가 나오는 이유
VLOOKUP을 사용하다 보면 #N/A 오류가 표시되는 경우가 있습니다.
가장 흔한 원인은 조회하려는 값이 검색 범위의 첫 번째 열에 존재하지 않는 경우입니다. Microsoft에서도 VLOOKUP의 #N/A 오류가 조회값을 찾지 못했을 때 발생할 수 있다고 설명합니다.
• 조회할 상품코드가 원본 표에 없음
• 숫자와 텍스트 형식이 서로 다름
• 앞뒤에 불필요한 공백이 있음
• 표 범위에 조회 대상 열이 포함되지 않음
• 오타가 있음
예를 들어 원본 표에는 A001이 있는데 검색하는 셀에는 A001 뒤에 공백이 들어 있다면 정확히 일치하는 값으로 인식되지 않을 수 있습니다.
따라서 #N/A가 나오면 먼저 실제로 조회값이 원본 표에 존재하는지부터 확인하는 것이 좋습니다.
8. #N/A 오류를 표시하지 않고 원하는 문구로 바꾸기
검색 결과가 없는 경우 #N/A 대신 "찾을 수 없음" 같은 문구를 표시하고 싶다면 IFERROR를 함께 사용할 수 있습니다.
이렇게 하면 정상적인 상품코드가 입력된 경우에는 상품명이 표시되고, 원본 표에서 값을 찾지 못하면 찾을 수 없음이라는 문구가 표시됩니다.
=IFERROR(VLOOKUP(B2,$F$2:$H$100,2,FALSE),"")
마지막 부분을 큰따옴표 두 개로 입력하면 값을 찾지 못했을 때 빈 셀처럼 표시할 수도 있습니다.
다만 IFERROR는 오류 메시지만 숨기는 기능이므로, 먼저 VLOOKUP 수식 자체가 올바르게 작성되었는지 확인하는 것이 좋습니다.
9. VLOOKUP을 사용할 때 자주 하는 실수
VLOOKUP은 간단해 보이지만 표의 구조 때문에 원하는 결과가 나오지 않는 경우가 있습니다.
VLOOKUP에서는 조회값이 지정한 표 범위의 첫 번째 열에 있어야 합니다.
실수 2. 열 번호를 잘못 입력함
표 범위에서 첫 번째 열을 1로 계산해야 합니다.
실수 3. FALSE를 빼먹음
정확한 값을 찾으려는 경우 FALSE를 명시하는 것이 안전합니다.
실수 4. 수식을 복사하면서 범위가 이동함
$F$2:$H$100처럼 절대참조를 사용해 범위를 고정합니다.
또한 VLOOKUP은 기본적으로 조회 기준 열의 오른쪽에 있는 데이터를 가져오는 방식입니다. 따라서 가져오려는 값이 조회 기준 열의 왼쪽에 있다면 VLOOKUP만으로는 원하는 방식의 검색이 어렵습니다. Microsoft는 이런 경우 XLOOKUP 같은 다른 조회 함수를 함께 안내하고 있습니다.
10. VLOOKUP 실무용 예제로 한 번에 익히기
이번에는 실제 업무에서 사용할 수 있는 형태로 정리해 보겠습니다.
F열 = 상품코드
G열 = 상품명
H열 = 판매가격
B2 셀에 상품코드를 입력하고 C2에 상품명을 자동으로 표시하려면 다음 수식을 사용합니다.
D2에 가격을 표시하려면 다음과 같이 입력합니다.
그리고 아래 행까지 수식을 복사하면 B열의 상품코드에 따라 상품명과 가격이 자동으로 표시됩니다.
상품명 → =VLOOKUP(B2,$F$2:$H$100,2,FALSE)
가격 → =VLOOKUP(B2,$F$2:$H$100,3,FALSE)
이 구조만 이해하면 직원번호로 이름을 찾거나 거래처코드로 거래처명을 찾는 등 다양한 업무에 VLOOKUP을 적용할 수 있습니다.
자주 묻는 질문 Q&A
Q1. VLOOKUP의 마지막 FALSE는 왜 넣나요?
FALSE는 조회값과 정확하게 일치하는 데이터를 찾도록 지정하는 옵션입니다. 상품코드나 직원번호처럼 정확한 값을 찾아야 할 때 주로 사용합니다.
Q2. VLOOKUP을 복사했더니 다른 행에서 결과가 이상하게 나옵니다.
표 범위가 수식을 복사하면서 함께 이동했을 가능성이 있습니다. $F$2:$H$100처럼 절대참조를 사용해 검색 범위를 고정하면 해결할 수 있습니다.
Q3. VLOOKUP에서 #N/A가 나오는 이유는 무엇인가요?
조회하려는 값이 검색 범위의 첫 번째 열에 없거나 숫자·텍스트 형식이 다르거나 공백 등의 문제로 정확히 일치하지 않을 때 발생할 수 있습니다.
Q4. #N/A 대신 "없음"이라고 표시할 수 있나요?
가능합니다. IFERROR와 VLOOKUP을 함께 사용하면 됩니다. 예를 들어 =IFERROR(VLOOKUP(B2,$F$2:$H$100,2,FALSE),"없음")처럼 사용할 수 있습니다.
Q5. VLOOKUP은 왼쪽에 있는 값을 가져올 수도 있나요?
일반적인 VLOOKUP은 조회 기준 열의 오른쪽에 있는 값을 반환하는 방식입니다. 왼쪽 값을 가져오거나 보다 유연한 조회가 필요하다면 XLOOKUP이나 INDEX와 MATCH 같은 다른 함수를 검토할 수 있습니다.
마무리
엑셀 VLOOKUP은 한 표의 기준값을 이용해 다른 표에서 원하는 정보를 자동으로 찾아오는 함수입니다.
처음에는 =VLOOKUP(찾을값,표범위,열번호,FALSE) 형태만 익혀도 충분히 활용할 수 있습니다.
특히 실무에서는 FALSE로 정확한 값을 찾고, $ 기호로 표 범위를 고정하며, IFERROR로 조회 실패 상황을 처리하는 방법을 함께 알아두면 활용도가 크게 높아집니다.
상품코드, 직원번호, 거래처코드처럼 반복적으로 데이터를 찾아야 하는 작업이라면 VLOOKUP을 활용해 수작업을 크게 줄일 수 있습니다.
댓글 쓰기