인터넷에서 복사한 VLOOKUP 수식을 그대로 붙여 넣었는데 #N/A가 떴다면, 함수가 잘못된 게 아니다. 엑셀 브이룩업(VLOOKUP)은 표에서 기준 값을 찾아 같은 행의 지정 열 데이터를 반환하는 함수이며, 오류의 80%는 네 번째 인수 하나를 잘못 이해한 데서 비롯된다. 이 글은 인수 4개를 하나씩 해체하고, 오류 유형별 원인과 해결 경로를 짚는다.
VLOOKUP이 하는 일, 딱 하나다
VLOOKUP은 Vertical Lookup의 약자다. 범위의 첫 번째 열에서 찾을 값을 세로 방향으로 검색해, 같은 행의 지정 열 값을 가져온다. 사원번호로 이름을 찾거나, 상품코드로 단가를 불러오는 상황이 대표적이다.
핵심 제약은 명확하다. 찾을 값이 반드시 범위의 첫 번째 열에 있어야 한다. 이 조건을 모르고 범위를 잘못 잡으면 어떤 인수를 바꿔도 올바른 값이 나오지 않는다.
인수 4개, 순서가 전부다
기본 문법은 다음과 같다.
=VLOOKUP(찾을값, 범위, 열번호, 일치유형)
| 인수 | 역할 | 주의사항 |
|---|---|---|
| ① 찾을값 | 기준이 되는 셀 또는 값 | 범위 첫 열의 데이터 형식과 반드시 일치해야 함 |
| ② 범위 | 검색 대상 테이블 전체 | 첫 번째 열에 찾을값이 있어야 하며, 절대참조($) 권장 |
| ③ 열번호 | 범위 내 몇 번째 열 값을 반환할지 | 범위 열 수보다 크면 #REF! 오류 발생 |
| ④ 일치유형 | FALSE=정확 일치, TRUE=근사 일치 | 생략 시 TRUE가 기본값으로 작동해 예상 외 값 반환 가능 |
네 번째 인수, 여기서 대부분 틀린다
일치유형은 FALSE(또는 0)와 TRUE(또는 1) 두 가지다. 문제는 인수를 생략하면 TRUE가 기본값으로 적용된다는 점이다. 대부분의 입문 자료가 이 인수를 “선택 사항”이라고 소개하지만, 생략은 사실상 가장 위험한 선택이다.
TRUE는 근사 일치 모드로, 범위 첫 열이 오름차순 정렬되지 않은 상태에서 쓰면 전혀 엉뚱한 값을 반환한다. 오류도 나지 않고 틀린 값이 조용히 들어오기 때문에 더 위험하다.
- FALSE(0): 찾을값과 완전히 일치하는 셀만 검색. 일반적인 데이터 참조 작업에는 항상 이것을 쓴다.
- TRUE(1): 찾을값 이하에서 가장 가까운 값을 반환. 세금 구간·등급 산출처럼 범위 기반 매핑에 적합하며, 반드시 첫 열 오름차순 정렬이 선행되어야 한다.
오류 유형별 원인과 해결
#N/A — 가장 흔한 오류
범위 첫 열에 찾을값이 존재하지 않을 때 발생한다. 실제로는 값이 있는데 #N/A가 뜬다면 다음 두 가지를 먼저 확인한다.
- 공백 포함 여부: 눈에 보이지 않는 선행·후행 공백이 붙어 있으면 불일치로 처리된다.
=TRIM(셀)로 공백을 제거하거나, 원본 데이터 자체를 텍스트 나누기로 정리한다. - 숫자-텍스트 형식 불일치: 찾을값은 숫자인데 범위 첫 열의 같은 숫자가 텍스트로 저장된 경우(또는 반대)에도 일치하지 않는다. 셀 왼쪽 상단에 작은 녹색 삼각형이 표시되면 형식 문제를 의심하자.
=VALUE()또는=TEXT()로 형식을 통일한다.
#REF! — 열번호 초과
세 번째 인수인 열번호가 범위의 실제 열 수보다 클 때 발생한다. 예컨대 범위가 A:C(3열)인데 열번호에 4를 넣으면 #REF!가 뜬다. 범위를 D열까지 넓히거나 열번호를 줄여 해결한다.
#VALUE! — 열번호에 0 이하 입력
열번호는 1 이상의 정수여야 한다. 0이나 음수를 입력하면 #VALUE! 오류가 발생한다. 흔치 않지만 수식을 복사하다 열번호 셀 참조가 잘못 연결될 때 나타나기도 한다.
범위 고정: 절대참조를 빠뜨리면 복사가 깨진다
VLOOKUP을 여러 행에 복사해 쓸 때 범위가 아래로 밀리면 안 된다. 범위 인수를 입력하거나 드래그로 선택한 직후 F4 키를 누르면 $A$2:$D$100처럼 행과 열이 고정된다. 이 설정 하나로 수식 복사 시 범위가 이동해 데이터가 뒤틀리는 문제를 원천 차단할 수 있다.
찾을값 인수는 반대로 상대참조를 유지해야 한다. 그래야 수식을 아래로 복사할 때 각 행의 기준 셀이 자동으로 바뀐다.
VLOOKUP의 구조적 한계 세 가지
VLOOKUP은 단순한 작업에 강하지만, 다음 세 상황에서 한계가 드러난다.
- 왼쪽 열 검색 불가: 범위 첫 열 기준으로 오른쪽 열만 반환할 수 있다. 찾을값 열보다 왼쪽 데이터가 필요하면 VLOOKUP으로는 처리할 수 없다.
- 열 삽입 시 오류: 범위 중간에 열을 삽입하면 열번호가 자동 갱신되지 않아 엉뚱한 열이 반환된다. 수식이 많은 파일일수록 이 문제가 조용히 축적된다.
- 복수 조건 결합 어려움: 단일 열만 기준으로 삼으므로, 두 조건을 동시에 적용하려면 보조 열을 만들어 두 값을 합친 뒤 그 열을 검색해야 한다. 번거롭고 오류 가능성도 높다.
대안 함수: INDEX/MATCH와 XLOOKUP
INDEX/MATCH — 방향 제한 없는 조합
INDEX와 MATCH를 조합하면 왼쪽·오른쪽 방향 제한 없이 검색이 가능하다. 열을 삽입해도 참조 범위가 자동으로 확장되어 안정적이다. 수식이 길어지는 단점이 있지만, 대규모 테이블에서 VLOOKUP보다 훨씬 신뢰성 있게 작동한다.
=INDEX(반환범위, MATCH(찾을값, 검색범위, 0))
MATCH의 세 번째 인수 0이 VLOOKUP의 FALSE와 같은 역할을 한다. 이 부분을 빠뜨리면 근사 일치로 작동하니 주의한다.
XLOOKUP — 현대적 대안
Microsoft 365와 Excel 2021부터 사용 가능한 XLOOKUP은 기본 인수가 셋으로 줄고, 기본값이 정확 일치라 실수 여지가 적다. 오류 발생 시 대체값을 네 번째 인수로 직접 지정할 수 있어 IFERROR 중첩도 필요 없다. 왼쪽 열 검색, 역방향 검색, 여러 열 동시 반환도 지원한다.
다만 Excel 2019 이하나 구버전 .xls 파일을 공유해야 하는 환경에서는 지원되지 않는다. 조직 내 모든 사용자의 버전을 확인하지 않고 XLOOKUP 수식을 공유하면 파일이 열리지 않거나 오류가 발생할 수 있다.
함수 선택 기준 한눈에 보기
| 상황 | 권장 함수 |
|---|---|
| 간단한 단일 조건, 오른쪽 열 참조, 구버전 Excel | VLOOKUP |
| 왼쪽 열 참조 또는 열 삽입이 잦은 테이블 | INDEX/MATCH |
| Microsoft 365 또는 Excel 2021 이상 | XLOOKUP |
| 세금 구간·등급 등 범위 기반 매핑 (정렬 필수) | VLOOKUP (TRUE) |
정리
엑셀 브이룩업 오류 대부분은 함수 자체의 결함이 아니라, 네 번째 인수 생략·데이터 형식 불일치·절대참조 누락 세 가지 중 하나에서 온다. 이 체크리스트를 습관으로 만들면 #N/A가 뜰 때마다 당황하는 상황은 크게 줄어든다. 데이터 구조가 복잡해지면 INDEX/MATCH나 XLOOKUP으로 넘어갈 시점을 자연스럽게 알게 된다. 도구를 선택할 때 “어떤 함수가 더 좋은가”보다 “지금 내 데이터 구조에 어떤 함수가 맞는가”를 먼저 물어보는 것이 실수를 줄이는 가장 빠른 길이다.
자주 묻는 질문
엑셀 VLOOKUP에서 #N/A 오류가 나는 가장 흔한 이유는 무엇인가요?
찾을값과 범위 첫 열의 데이터 형식 불일치(숫자 vs 텍스트)와 선행·후행 공백 포함이 가장 흔한 원인입니다. =TRIM()으로 공백을 제거하고, VALUE() 또는 TEXT()로 형식을 통일하면 해결되는 경우가 많습니다.
VLOOKUP 네 번째 인수를 생략하면 어떻게 되나요?
인수를 생략하면 TRUE(근사 일치)가 기본값으로 적용됩니다. 범위 첫 열이 오름차순 정렬되지 않은 경우 엉뚱한 값이 반환될 수 있으므로, 일반적인 데이터 참조에서는 반드시 FALSE(또는 0)를 명시해야 합니다.
수식을 여러 행에 복사하면 범위가 밀리는데 어떻게 고치나요?
범위 인수에 절대참조($)를 적용하세요. 범위 선택 후 F4 키를 누르면 $A$2:$D$100처럼 행과 열이 고정되어, 수식을 복사해도 범위가 이동하지 않습니다.
VLOOKUP으로 왼쪽 열 데이터를 가져올 수 없나요?
맞습니다. VLOOKUP은 범위 첫 열 기준으로 오른쪽 열만 반환할 수 있습니다. 왼쪽 열을 참조해야 한다면 INDEX/MATCH 조합을 사용하거나, Microsoft 365 이상 환경에서는 XLOOKUP을 활용하면 됩니다.
XLOOKUP이 VLOOKUP보다 무조건 나은가요?
기능적으로는 XLOOKUP이 더 유연하고 실수 여지도 적습니다. 다만 Excel 2019 이하나 구버전 파일을 공유해야 하는 환경에서는 VLOOKUP이나 INDEX/MATCH가 호환성 면에서 현실적인 선택입니다.