VLOOKUP 함수: 예제로 배우는 완벽 가이드

VLOOKUP은 역대 가장 널리 사용되는 Excel 함수 중 하나입니다. 고객 ID를 이름과 연결하거나, 제품 카탈로그에서 가격을 가져오거나, 두 시트의 데이터를 합치는 작업 모두 VLOOKUP 하나로 해결할 수 있습니다. 이 가이드에서는 구문, 실전 예제, 흔히 발생하는 함정, 그리고 다른 함수가 더 나은 선택이 되는 시점까지 필요한 모든 내용을 다룹니다.

VLOOKUP이란 무엇인가요?

VLOOKUP은 세로 방향 조회(Vertical Lookup)를 의미합니다. 범위의 첫 번째 열에서 값을 검색한 뒤, 같은 행에 있는 지정된 열의 값을 반환합니다. 쉽게 말해 정밀한 검색 작업과 같습니다. Excel에 키 값을 건네주고 어디서 찾을지 알려주면, 같은 레코드에서 원하는 정보를 가져다 줍니다.

실무에서 자주 쓰이는 사례는 다음과 같습니다:

• SKU 코드로 제품 가격 조회하기
• 직원 ID로 부서명 가져오기
• 청구서 번호를 고객 이름과 매칭하기
• 점수표를 기준으로 시험 점수 등급 매기기

VLOOKUP 구문 설명

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

각 인수의 역할은 다음과 같습니다:

인수	필수 여부	의미
lookup_value	필수	찾으려는 값 — 셀 참조, 숫자 또는 텍스트 문자열.
table_array	필수	데이터가 포함된 범위. 조회 열은 이 범위의 가장 왼쪽 열이어야 합니다.
col_index_num	필수	반환할 값이 있는 열 번호(table_array의 왼쪽부터 계산).
range_lookup	선택	정확히 일치하는 경우 FALSE(또는 0), 근사 일치는 TRUE(또는 1). 생략하면 기본값은 TRUE입니다.

중요: 정렬된 표를 사용하면서 근사 일치가 실제로 필요한 경우(예: 등급 구간 또는 세율 구간 조회)가 아니라면, 네 번째 인수에는 항상 FALSE를 사용하세요. 생략하거나 정렬되지 않은 데이터에 TRUE를 사용하면 잘못된 결과가 반환되는 주요 원인이 됩니다.

단계별 실전 예제

Sheet1에서 소규모 제품 카탈로그를 관리하고 Sheet2의 주문 양식에 가격을 가져온다고 가정해 보겠습니다. Sheet1의 데이터는 다음과 같습니다:

A — SKU	B — 제품명	C — 가격
P001	무선 마우스	$29.99
P002	USB-C 허브	$49.99
P003	기계식 키보드	$89.99
P004	모니터 스탠드	$34.99

Sheet2의 A열에는 사용자가 입력한 SKU가 있습니다. Sheet2의 B열에 제품명을 반환하려면 다음을 입력합니다:

=VLOOKUP(A2, Sheet1!$A$2:$C$5, 2, FALSE)

Sheet2의 C열에 가격을 반환하려면 열 인덱스를 3으로 변경합니다:

=VLOOKUP(A2, Sheet1!$A$2:$C$5, 3, FALSE)

절대 참조를 사용하는 이유

Sheet1!$A$2:$C$5의 달러 기호에 주목하세요. 이 기호는 범위를 고정하여 수식을 아래 행으로 복사해도 table_array가 이동하지 않도록 합니다. 셀 참조 방식이 생소하다면, 엑셀 셀 참조 설명: 상대 참조 vs 절대 참조 문서에서 이 개념을 자세히 다루고 있습니다.

근사 일치 VLOOKUP 사용하기

조회 테이블이 오름차순으로 정렬되어 있고 조회 값보다 크지 않은 가장 가까운 값을 찾으려면 네 번째 인수를 TRUE로 설정하세요. 원점수를 등급으로 변환하는 것이 대표적인 예입니다:

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

E — 최소 점수	F — 등급
0	F
60	D
70	C
80	B
90	A

85점은 80 행과 일치하여 "B"를 반환합니다. 이는 최소 점수 열이 낮은 값에서 높은 값 순으로 정렬되어 있기 때문에 올바르게 작동합니다.

자주 발생하는 VLOOKUP 오류 해결하기

#N/A 오류

가장 자주 발생하는 오류입니다. VLOOKUP이 테이블의 첫 번째 열에서 lookup_value를 찾지 못했다는 의미입니다. 다음 사항을 확인하세요:

• 앞뒤 공백 — 조회 값과 테이블 데이터 모두에 TRIM()을 적용하세요.
• 데이터 형식 불일치 — 텍스트로 저장된 숫자는 실제 숫자와 일치하지 않습니다. 열을 선택한 후 데이터 → 텍스트 나누기로 이동하여 마침을 클릭해 변환하세요.
• 오타 — 한 글자만 잘못 입력해도 #N/A가 발생합니다.
• 조회 값이 첫 번째 열 밖에 있는 경우 — VLOOKUP은 왼쪽에서 오른쪽으로만 검색하므로, 조회 열이 범위의 첫 번째 열이어야 합니다.

디버깅 중 오류를 숨기려면 수식을 다음과 같이 감싸세요: =IFERROR(VLOOKUP(A2, $D$2:$F$10, 2, FALSE), "찾을 수 없음")

#REF! 오류

col_index_num이 table_array의 열 수보다 클 때 발생합니다. 예를 들어 범위가 3열뿐인데 5열을 지정한 경우입니다. 열 수를 확인하고 인덱스를 적절히 줄이세요.

#VALUE! 오류

일반적으로 col_index_num이 0이거나 숫자가 아닌 값일 때 발생합니다. 열 인덱스는 1 이상의 양의 정수여야 합니다.

VLOOKUP이 잘못된 값을 반환하는 경우

네 번째 인수를 생략하거나 TRUE로 설정했는데 테이블이 정렬되지 않은 경우, VLOOKUP은 오류 메시지 없이 잘못된 근사 일치 값을 반환할 수 있습니다. 정확한 일치에는 항상 FALSE를 사용하세요.

VLOOKUP과 IF 함수 중첩하기

VLOOKUP을 논리 함수와 결합하면 더욱 정교한 결과를 얻을 수 있습니다. 예를 들어, 조회가 성공했을 때만 할인율을 표시하려면 다음과 같이 작성합니다:

=IF(IFERROR(VLOOKUP(A2, $D$2:$F$10, 3, FALSE), "") = "", "할인 없음", VLOOKUP(A2, $D$2:$F$10, 3, FALSE))

수식 내에서 논리 테스트를 작성하는 방법에 대해 더 자세히 알아보려면 IF 함수: 논리 테스트와 중첩 IF 완벽 가이드를 참고하세요.

여러 시트 및 통합 문서에서 VLOOKUP 사용하기

범위 앞에 시트 이름을 붙이면 다른 시트의 데이터를 참조할 수 있습니다:

=VLOOKUP(A2, Catalog!$A$2:$C$100, 2, FALSE)

다른 통합 문서를 참조하려면(해당 파일이 열려 있는 경우):

=VLOOKUP(A2, [PriceList.xlsx]Sheet1!$A$2:$C$100, 2, FALSE)

통합 문서가 닫혀 있으면, 두 파일이 모두 열린 상태에서 연결할 때 Excel이 전체 파일 경로를 자동으로 표시합니다.

VLOOKUP과 대안 함수 비교

VLOOKUP의 한계

• 왼쪽에서 오른쪽으로만 조회 가능 — 조회 열이 항상 범위의 가장 왼쪽에 있어야 합니다.
• 조회당 하나의 값만 반환합니다.
• table_array 중간에 열을 삽입하거나 삭제하면 하드코딩된 col_index_num이 깨집니다.
• 기본적으로 대소문자를 구분하지 않습니다.

INDEX MATCH: 유연한 대안

INDEX MATCH 조합은 왼쪽 열 제한이 없으며 열을 추가하거나 재배치할 때 더욱 안정적입니다. VLOOKUP의 한계와 씨름하고 있다면, INDEX MATCH: 더 뛰어난 조회 방법 전용 문서에서 단계별 전환 방법을 안내합니다.

XLOOKUP: 최신 대체 함수

Excel 365 및 Excel 2021에서 사용할 수 있는 XLOOKUP은 더 간단하고 강력합니다:

=XLOOKUP(A2, Sheet1!$A$2:$A$5, Sheet1!$C$2:$C$5, "찾을 수 없음")

모든 방향으로 검색하고, 누락된 값을 기본적으로 처리하며, 숫자로 된 열 인덱스가 필요 없습니다. 사용 중인 Excel 버전이 지원한다면 모든 새 프로젝트에 XLOOKUP 사용을 고려해 보세요.

실제 비즈니스 시나리오에서의 VLOOKUP

VLOOKUP은 다양한 Excel 워크플로와 잘 어울립니다. 예를 들어, KPI 및 성과를 추적하는 영업 대시보드에서는 참조 테이블에서 제품명이나 담당자 지역을 요약 보고서로 가져오기 위해 VLOOKUP을 자주 사용합니다. 마찬가지로 전문적인 청구를 위한 청구서 템플릿을 만들 때도 사용자가 입력한 품목 코드를 기반으로 제품 목록에서 단가를 가져오기 위해 VLOOKUP이 거의 항상 사용됩니다.

대용량 데이터셋을 다루는 팀이라면 VLOOKUP과 피벗 테이블을 결합하는 것이 생산적인 워크플로입니다. VLOOKUP으로 원시 데이터에 범주 레이블을 추가한 뒤 피벗 테이블에서 요약하는 방식입니다.

평범한 말로 VLOOKUP 수식 작성하기

필요한 내용은 알지만 정확한 구문이 기억나지 않을 때 — 예를 들어 "HR 시트의 A열에서 직원 ID를 찾아 D열에서 급여를 반환해줘" — GPTExcel에 평범한 말로 설명하면 올바른 VLOOKUP 수식을 즉시 생성해 스프레드시트에 바로 붙여넣을 수 있습니다.

자주 묻는 질문

일부 행에서는 결과가 나오는데 다른 행에서 #N/A가 발생하는 이유는 무엇인가요?

가장 가능성 높은 원인은 특정 셀의 데이터 형식 불일치 또는 앞뒤 공백입니다. 조회 값에 =TRIM(A2)를 적용하고, 조회 열의 모든 항목이 동일한 데이터 형식(모두 텍스트 또는 모두 숫자)으로 저장되어 있는지 확인하세요. 또한 =IFERROR(VLOOKUP(...), "데이터 확인")을 사용하면 나머지 보고서에 영향을 주지 않으면서 실패하는 행을 파악할 수 있습니다.

VLOOKUP으로 여러 열의 값을 한 번에 반환할 수 있나요?

전통적인 방식으로는 하나의 수식으로는 불가능합니다. 반환하려는 각 열마다 col_index_num만 바꾸어 별도의 VLOOKUP이 필요합니다. 대신 Excel 365의 XLOOKUP은 다중 열 반환 배열을 지정하여 수식 하나로 전체 행의 결과를 반환할 수 있습니다.

조회 열에 중복 값이 있을 경우 VLOOKUP은 어떻게 작동하나요?

VLOOKUP은 위에서 아래로 검색하면서 첫 번째 일치 항목에 해당하는 값을 항상 반환합니다. 조회 열에 중복이 있으면 이후 일치 항목은 무시됩니다. 중복이 포함된 시나리오에서는 피벗 테이블을 사용하거나 조회 전에 보조 열로 중복을 제거하는 것을 고려하세요.

VLOOKUP은 대소문자를 구분하나요?

구분하지 않습니다. VLOOKUP은 대문자와 소문자를 동일하게 처리합니다. "apple"을 검색하면 "Apple"이나 "APPLE"과도 일치합니다. 대소문자를 구분하는 조회가 필요하다면 EXACT()와 INDEX/MATCH를 결합한 배열 수식을 사용해야 합니다.