엑셀 VLOOKUP 함수 사용법 – 구문부터 오류 해결까지

다른 시트나 표에서 특정 값을 찾아 관련 정보를 가져와야 할 때 VLOOKUP 함수를 사용합니다. 이 글에서는 VLOOKUP의 구문과 인수, 실전 예시, 그리고 자주 발생하는 오류 해결 방법을 다룹니다.

VLOOKUP 함수란

VLOOKUP은 Vertical Lookup의 약자로, 범위의 첫 번째 열에서 찾는 값을 세로 방향으로 검색한 뒤, 같은 행의 지정한 열에서 값을 반환합니다. 제품 코드로 제품명을 찾거나, 직원 번호로 부서를 조회하는 작업에 주로 사용합니다.

함수의 역할과 동작 원리

VLOOKUP은 다음 순서로 동작합니다.

1. 참조 범위의 첫 번째 열을 위에서 아래로 탐색합니다.
2. 찾는 값과 일치하는 행을 찾습니다.
3. 해당 행에서 지정한 열 번호에 해당하는 값을 반환합니다.

첫 번째 열이 기준이 되므로, 찾는 값은 항상 참조 범위의 맨 왼쪽 열에 있어야 합니다. 이 조건을 만족하지 않으면 함수가 제대로 작동하지 않습니다.

VLOOKUP 구문과 인수 설명

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

인수	필수 여부	설명
lookup_value	필수	참조 범위 첫 번째 열에서 찾을 값
table_array	필수	검색할 데이터 범위 (최소 2열)
col_index_num	필수	반환할 값이 위치한 열 번호 (첫 열 = 1)
range_lookup	선택	FALSE(0) = 정확히 일치, TRUE(1) = 유사 일치

각 인수 상세 설명

lookup_value: 셀 참조(예: A2) 또는 직접 값(예: "홍길동")을 입력합니다. 참조 범위 첫 번째 열에 없는 값이면 #N/A 오류가 납니다.

table_array: 조회할 전체 데이터 범위입니다. 수식을 여러 셀에 복사할 경우 범위가 이동하지 않도록 절대참조($)로 고정합니다. 예: $B$2:$E$100

col_index_num: 반환할 값의 열 번호를 참조 범위 기준으로 입력합니다. 참조 범위가 B열~E열이라면 B열이 1, C열이 2, D열이 3, E열이 4입니다.

range_lookup 주의사항

range_lookup을 생략하면 엑셀은 TRUE(유사 일치)로 처리합니다. 유사 일치는 참조 범위의 첫 번째 열이 오름차순으로 정렬되어 있을 때만 정상 동작합니다. 정렬되지 않은 데이터에서 TRUE를 사용하면 엉뚱한 값을 반환할 수 있습니다.

실무에서는 정확히 일치하는 값을 찾는 경우가 대부분이므로, FALSE 또는 0을 명시하는 습관을 들이는 것이 좋습니다.

기본 사용 예시

직원 정보 조회 예시

아래와 같은 직원 정보 표가 A~D열에 있다고 가정합니다.

A열: 직원번호	B열: 이름	C열: 부서	D열: 직급
E001	김민준	영업팀	대리
E002	이서연	개발팀	주임
E003	박지호	인사팀	과장

F2 셀에 조회할 직원번호(E002)가 있을 때, G2 셀에서 해당 직원의 부서를 가져오려면:

=VLOOKUP(F2, $A$2:$D$4, 3, FALSE)

• F2: 찾을 값 (E002)
• $A$2:$D$4: 참조 범위 (절대참조 적용)
• 3: 세 번째 열(C열, 부서) 반환
• FALSE: 정확히 일치

결과: 개발팀

절대참조 설정 방법

수식에서 범위를 선택한 뒤 F4 키를 누르면 자동으로 절대참조($)가 붙습니다. 한 번 더 누르면 행만 고정, 두 번 더 누르면 열만 고정, 세 번 더 누르면 상대참조로 돌아옵니다. 수식을 아래로 복사할 때는 $A$2:$D$4처럼 행과 열 모두 고정하는 것이 일반적입니다.

자주 하는 실수와 오류 해결

#N/A 오류 원인 4가지

VLOOKUP에서 #N/A 오류가 나는 주요 원인입니다.

1. 찾는 값이 범위에 없음: 직원번호 E004를 찾는데 표에 E004가 없는 경우입니다.
2. 데이터 형식 불일치: 찾는 값은 숫자 1234인데 참조 범위의 해당 셀에는 텍스트 "1234"가 저장된 경우입니다. 눈으로 보면 같아 보이지만 엑셀 내부에서는 다른 형식으로 인식합니다.
3. 공백 포함: 셀에 눈에 보이지 않는 앞뒤 공백이 있는 경우입니다. TRIM 함수로 공백을 제거한 뒤 사용합니다.
4. 찾는 값 위치 오류: 찾는 값이 참조 범위의 첫 번째 열이 아닌 다른 열에 있는 경우입니다.

오류를 숨기고 특정 텍스트를 표시하려면 IFERROR와 함께 사용합니다.

=IFERROR(VLOOKUP(F2, $A$2:$D$4, 3, FALSE), "없음")

숫자/텍스트 형식 불일치 해결

참조 범위의 값이 텍스트로 저장된 경우, 셀 왼쪽에 작은 녹색 삼각형이 표시됩니다. 이 경우 두 가지 방법으로 해결할 수 있습니다.

• 참조 범위의 텍스트를 숫자로 변환: 셀 선택 → 경고 아이콘 클릭 → "숫자로 변환" 선택
• 찾는 값을 텍스트로 변환하여 조회: =VLOOKUP(TEXT(F2,"0"), $A$2:$D$4, 3, FALSE)

절대참조 누락 문제

수식을 복사하면 참조 범위가 따라서 이동합니다. 예를 들어 =VLOOKUP(A2, B2:E10, 3, FALSE)를 아래로 한 칸 복사하면 =VLOOKUP(A3, B3:E11, 3, FALSE)로 바뀌어 범위가 밀립니다. 참조 범위에 F4를 눌러 $B$2:$E$10으로 고정하면 이 문제가 생기지 않습니다.

VLOOKUP의 한계와 대안

왼쪽 방향 조회 불가 문제

VLOOKUP은 참조 범위의 첫 번째 열을 기준으로 오른쪽 방향으로만 값을 반환합니다. 예를 들어 이름(B열)으로 직원번호(A열)를 가져오는 작업은 VLOOKUP으로 처리할 수 없습니다. 또한 열을 삽입하거나 삭제하면 col_index_num이 어긋나 오류가 발생할 수 있습니다.

INDEX+MATCH, XLOOKUP 언급

이러한 제약이 문제가 될 때는 두 가지 대안을 고려합니다.

INDEX + MATCH 조합: 왼쪽 방향 조회가 가능하고, 열을 삽입해도 수식이 자동으로 따라갑니다. 구문이 다소 복잡하지만 활용 범위가 넓습니다. 이 시리즈의 다음 편에서 자세히 다룹니다.

XLOOKUP 함수: Microsoft 365 및 Excel 2021 이상 버전에서 사용할 수 있습니다. VLOOKUP의 주요 단점을 대부분 해결하고 구문도 간결합니다. 구버전 엑셀에서는 지원하지 않으므로 사용 환경을 먼저 확인해야 합니다.

마치며

VLOOKUP은 구문 4개만 이해하면 대부분의 조회 작업에 바로 적용할 수 있습니다. 실무에서는 range_lookup을 FALSE로 명시하고, 참조 범위에 절대참조를 설정하는 두 가지 습관이 오류를 크게 줄여 줍니다. 왼쪽 방향 조회나 동적 범위가 필요한 경우에는 INDEX+MATCH 조합을 검토하면 됩니다.