엑셀 VLOOKUP 함수 사용법, 오류 없이 쓰는 법
목차
엑셀 VLOOKUP(Vertical Lookup, 세로 방향 조회 함수) 사용법은 딱 4개의 인수만 정확히 이해하면 됩니다. 기본 구조는 =VLOOKUP(찾을값, 범위, 열 번호, [일치옵션])이며, 왼쪽 첫 열에서 찾을값을 검색해 같은 행의 지정한 열 번호 값을 가져오는 함수입니다. 이 글에서는 인수별 의미부터 실무에서 가장 많이 만나는 #N/A 오류 원인과 해결법, 그리고 HLOOKUP·INDEX-MATCH·XLOOKUP과의 차이까지 한 번에 정리합니다.
VLOOKUP 함수란? 기본 구문과 원리
VLOOKUP은 사번으로 부서를 찾거나, 제품코드로 단가를 불러오는 등 표에서 특정 값을 자동으로 연결할 때 쓰는 찾기/참조 함수입니다. 함수 하나로 수백~수천 행의 데이터를 수기 대조 없이 매칭할 수 있어 인사·재고·매출 관리 실무에서 가장 기본적으로 쓰입니다.
=VLOOKUP(찾을값, 범위, 열 번호, [일치옵션])
예: =VLOOKUP(A2, 제품표!A:D, 3, FALSE) → A2 값을 제품표 A열에서 찾아 같은 행의 3번째 열(C열) 값을 반환
4가지 인수 이해하기
각 인수는 다음 역할을 합니다.
- 찾을값: 검색 기준이 되는 셀 (예: 사번, 제품코드)
- 범위: 찾을값이 포함된 첫 열부터 결과값이 있는 열까지의 전체 영역
- 열 번호: 범위의 첫 열을 1로 시작해 몇 번째 열의 값을 가져올지 지정
- 일치옵션: 정확히 일치(FALSE 또는 0)와 근사값(TRUE 또는 생략) 중 선택
실무에서 가장 흔한 실수는 범위의 첫 열이 찾을값과 다른 열인 경우입니다. VLOOKUP은 항상 범위의 맨 왼쪽 열에서만 검색하므로, 찾을값이 있는 열이 범위 맨 왼쪽에 오도록 표를 구성해야 합니다.
TRUE/FALSE(근사값/정확히 일치) 차이
일치옵션을 생략하거나 TRUE로 두면 근사값 조회가 되어, 찾을값과 완전히 같지 않아도 그보다 작거나 같은 값 중 가장 가까운 값을 찾습니다. 등급표나 구간별 요율표(예: 매출 구간별 수수료율)처럼 범위 기준으로 값을 찾을 때만 유용하며, 이 경우 기준 열이 반드시 오름차순 정렬되어 있어야 합니다. 사번·제품코드처럼 정확히 일치하는 값을 찾아야 하는 대부분의 경우에는 FALSE(또는 0)를 명시적으로 입력해야 안전합니다.
실전 예제로 배우는 VLOOKUP 사용법
거래처 목록(A열: 거래처코드, B열: 거래처명, C열: 담당자, D열: 미수금)에서 코드로 미수금을 조회하는 상황을 예로 들면, 다음 순서로 작성합니다.
- 결과를 표시할 셀을 클릭하고 =VLOOKUP(을 입력합니다.
- 찾을값(예: F2, 조회하려는 거래처코드가 있는 셀)을 지정하고 쉼표를 입력합니다.
- 범위(A2:D100)를 드래그로 선택한 뒤, F4 키를 눌러 절대참조($A$2:$D$100)로 고정합니다.
- 열 번호를 입력합니다. D열(미수금)은 A열 기준 4번째 열이므로 4를 입력합니다.
- 일치옵션에 FALSE를 입력하고 괄호를 닫은 뒤 Enter를 누릅니다.
- 수식을 아래로 복사(자동 채우기)해 나머지 행에도 동일하게 적용합니다.
3번 단계의 절대참조 고정을 빠뜨리면, 수식을 아래로 복사할 때 범위가 한 칸씩 밀려나면서 엉뚱한 값이 나오거나 #REF! 오류가 발생합니다. 이는 VLOOKUP 실무에서 가장 흔한 실수 중 하나이므로 반드시 체크해야 합니다.
VLOOKUP 자주 발생하는 오류와 해결법
VLOOKUP을 쓰다 보면 대부분 아래 세 가지 유형의 오류를 만나게 됩니다.
#N/A 오류 원인과 해결
#N/A는 '찾을값을 범위에서 찾지 못했다'는 뜻입니다. 실제로 값이 표에 있는데도 이 오류가 뜬다면 다음을 의심해야 합니다.
- 숫자처럼 보이지만 실제로는 텍스트 서식으로 저장된 경우 (셀 왼쪽 정렬이면 의심)
- 다른 시스템에서 복사한 데이터에 보이지 않는 공백이 섞인 경우
- 찾을값이 범위의 첫 열이 아니라 다른 열에 있는 경우
텍스트/숫자 불일치는 양쪽 값 중 하나를 VALUE() 또는 TEXT() 함수로 형식을 통일해 해결하고, 공백 문제는 TRIM() 함수로 앞뒤 공백을 제거하면 대부분 해결됩니다.
절대참조($) 안 걸었을 때 생기는 문제
앞서 언급했듯 범위 인수에 절대참조를 걸지 않으면 수식을 복사할 때 참조 범위가 함께 이동해 값이 밀리거나 #REF! 오류가 발생합니다. 범위 앞에 $ 기호를 붙이거나 셀 참조 선택 후 F4를 누르는 습관을 들이면 이 오류를 원천 차단할 수 있습니다.
또한 오류 메시지를 화면에 그대로 노출하고 싶지 않다면 =IFERROR(VLOOKUP(...), "자료없음")처럼 IFERROR 함수로 감싸 오류 대신 원하는 문구를 표시할 수 있습니다.
VLOOKUP vs HLOOKUP vs INDEX-MATCH vs XLOOKUP 비교
VLOOKUP은 편리하지만 몇 가지 구조적 한계가 있습니다. 표 중간에 열을 삽입하면 열 번호 인수가 자동으로 갱신되지 않아 결과가 틀어지고, 찾을값보다 왼쪽 열의 값은 아예 가져올 수 없습니다. 이런 한계를 보완하는 대안 함수들과 비교하면 다음과 같습니다.
| 함수 | 조회 방향 | 열 삽입 시 영향 | 왼쪽 열 조회 |
|---|---|---|---|
| VLOOKUP | 세로(열 기준) | 열 번호 밀림 위험 있음 | 불가능 |
| HLOOKUP | 가로(행 기준) | 행 번호 밀림 위험 있음 | 불가능 |
| INDEX-MATCH | 양방향 | 영향 없음(열 추가에 안전) | 가능 |
| XLOOKUP | 양방향 | 영향 없음 | 가능(엑셀 2021·M365 이상) |
구버전 엑셀에서도 안정적으로 쓰려면 INDEX-MATCH 조합이, 최신 엑셀(2021 이상 또는 Microsoft 365)을 쓴다면 문법이 더 간결한 XLOOKUP이 대안이 됩니다. 다만 회사 공용 문서라면 상대방 엑셀 버전을 먼저 확인한 뒤 XLOOKUP 사용 여부를 정하는 것이 안전합니다.
VLOOKUP 활용 꿀팁
- 다른 시트/파일 참조: 범위 앞에 시트명을 붙이면(예: 제품표!A:D) 다른 시트의 표도 그대로 조회할 수 있습니다.
- 여러 조건 매칭이 필요하면: VLOOKUP은 조건이 1개일 때만 정확히 동작하므로, 조건이 2개 이상이면 보조 열에 조건을 합친(CONCATENATE) 키 열을 만들거나 INDEX-MATCH의 배열 수식으로 전환하는 편이 낫습니다.
- 표 범위를 미리 넉넉히 잡기: 데이터가 늘어날 것을 대비해 범위를 실제 데이터보다 여유 있게 지정하거나, 표 전체를 '표(테이블)' 기능으로 변환해 자동 확장되게 해두면 유지보수가 편해집니다.
자주 묻는 질문
Q. VLOOKUP에서 찾을값이 범위보다 왼쪽 열에 있으면 어떻게 하나요?
VLOOKUP은 구조상 범위의 첫 열(가장 왼쪽)에서만 검색이 가능하므로, 찾을값이 오른쪽 열에 있다면 열 순서를 바꾸거나 INDEX-MATCH·XLOOKUP 함수로 대체해야 합니다.
Q. VLOOKUP 결과가 0으로 나오는 이유는 무엇인가요?
대상 셀이 빈 셀인데 정상적으로 검색은 되었을 때 흔히 발생합니다. 결과 열의 원본 셀이 실제로 비어 있는지 확인하거나, IF 함수로 빈 값일 때 공란을 표시하도록 처리할 수 있습니다.
Q. VLOOKUP과 XLOOKUP 중 어떤 것을 배우는 게 좋을까요?
실무 문서 대부분이 아직 VLOOKUP 기준으로 작성되어 있어 VLOOKUP을 먼저 익히는 것이 실용적입니다. 이후 여유가 되면 열 삽입에 안전하고 왼쪽 열 조회도 가능한 XLOOKUP을 추가로 익히는 순서를 권장합니다.
Q. 구글 스프레드시트에서도 VLOOKUP 사용법이 동일한가요?
인수 구성과 기본 문법은 동일합니다. 다만 구글 스프레드시트는 시트 간 참조 시 파일명을 별도로 지정할 필요가 없고, 일부 오류 처리 방식(IFERROR 등)의 세부 동작에 소소한 차이가 있을 수 있어 실제 적용 전 결과를 한 번 확인하는 것이 좋습니다.
Q. 정확히 일치(FALSE)를 빼먹으면 어떻게 되나요?
일치옵션을 생략하면 기본값인 TRUE(근사값)로 처리되어, 찾을값과 똑같지 않은데도 비슷한 값을 찾아 결과를 반환할 수 있습니다. 정확한 매칭이 필요한 코드·사번 조회에서는 FALSE를 반드시 명시해야 합니다.
