VLOOKUP이 #N/A만 나올 때 — 원인 6가지와 확인 순서
먼저 확인할 것: 순서표
마이크로소프트 문서가 설명하는, VLOOKUP이 값을 찾는 과정에서 #N/A를 내는 대표적인 경우는 두 가지입니다. 마지막 인수가 FALSE(정확히 일치)일 때 같은 값을 못 찾은 경우, 그리고 TRUE(대략 일치)일 때 찾는값이 첫 열의 최솟값보다 작은 경우입니다. 이 밖에 찾는값 자체가 이미 #N/A이거나 가져올 칸에 #N/A가 들어 있으면 그 오류가 그대로 결과로 나옵니다. 원인을 좁힐 때는 아래 순서로 봅니다. 2·3번의 A2는 예시일 뿐이고, 실제로는 찾는값과 같아야 할 표의 그 칸(예: P003이 있는 칸)과 비교합니다.
| 순서 | 점검 | 바로 해 볼 것 |
|---|---|---|
| 1 | 값이 표에 정말 있는가 | =MATCH(E2,$A$2:$A$5,0) — 숫자가 나오면 같은 값이 있음 |
| 2 | 글자 수가 같은가(공백) | =LEN(E2) 와 =LEN(A2) 비교 |
| 3 | 형식이 같은가 | =ISTEXT(E2)·=ISNUMBER(E2) 와 표 쪽 칸의 결과 비교 |
| 4 | 마지막 인수가 FALSE인가 | 수식 끝을 확인 |
| 5 | 표 범위가 찾는 열에서 시작하는가 | 범위 첫 글자(열)를 확인 |
| 6 | 복사 후 범위가 그대로인가 | 수식 칸을 눌러 범위 확인 |
이 글의 예제 표는 모두 A1:C5에 있다고 가정합니다.
| A | B | C | |
|---|---|---|---|
| 1 | 코드 | 품명 | 단가 |
| 2 | P001 | 연필 | 500 |
| 3 | P002 | 노트 | 1500 |
| 4 | P003 | 지우개 | 300 |
| 5 | P004 | 가위 | 2500 |
1. 값이 정말 표에 없다
가장 단순한 경우입니다. =VLOOKUP("P009",$A$2:$C$5,3,FALSE)는 표에 P009가 없으니 #N/A입니다. 이때는 수식이 아니라 데이터 문제입니다. 오타(P0O3처럼 숫자 0 대신 알파벳 O)인지, 표에 아직 추가되지 않은 코드인지 확인합니다.
있는지 확인할 때 Ctrl+F만 믿으면 안 됩니다. 기본 찾기는 글자 일부만 맞아도 찾아 주므로, P003을 검색하면 P003 (뒤에 공백)이나 XP003도 걸립니다. 찾기 창의 「옵션」에서 「전체 셀 내용 일치」를 켜거나, 위 순서표의 =MATCH(E2,$A$2:$A$5,0)처럼 정확히 일치로 검사합니다. MATCH가 #N/A이면, 같은 범위를 대상으로 마지막 인수를 FALSE로 둔 VLOOKUP도 같은 이유로 #N/A입니다. 단, 찾는 값에 *나 ?가 들어 있으면 두 함수 모두 이를 와일드카드로 해석하니, 문자 그대로 찾으려면 ~*, ~?처럼 앞에 ~를 붙입니다.
2. 눈에 안 보이는 공백
찾는값이 "P003 "(뒤에 공백 한 칸)이면 표의 P003과 다른 값입니다. 화면에서는 똑같이 보여도 =VLOOKUP("P003 ",$A$2:$C$5,3,FALSE)는 #N/A입니다. 외부에서 붙여 넣은 데이터에서 흔합니다.
| 상황 | 고치는 수식 |
|---|---|
| 찾는값(E2)에 공백 | =VLOOKUP(TRIM(E2),$A$2:$C$5,3,FALSE) |
| 표 쪽(A열)에 공백 | 빈 열에 =TRIM(A2)를 채운 뒤 값으로 붙여 넣어 A열을 교체 |
마이크로소프트 문서는 첫 열에 앞뒤 공백, 곧은 따옴표와 둥근 따옴표의 혼용, 인쇄되지 않는 문자가 없어야 한다고 설명하며 TRIM과 CLEAN 사용을 권합니다. 다만 TRIM은 일반 공백 위주로 지우므로, 웹 페이지에서 복사한 특수 공백은 남을 수 있습니다. 그럴 때는 LEN으로 글자 수가 어긋나는지 먼저 확인합니다.
3. 숫자와 텍스트가 섞였다
찾는값은 숫자 1002인데 표의 코드는 텍스트로 저장된 "1002"이면(또는 그 반대) 서로 다른 값으로 취급되어 #N/A가 납니다. 마이크로소프트 문서도 숫자나 날짜가 텍스트로 저장되어 있지 않은지 확인하라고 안내합니다. 셀 왼쪽 위에 초록 삼각형이 있거나 기본 정렬이 왼쪽이면 텍스트일 가능성이 높습니다.
| 확인 | 수식 | 결과 읽는 법 |
|---|---|---|
| 찾는값의 형식 | =ISNUMBER(E2) · =ISTEXT(E2) | 앞쪽 TRUE = 숫자, 뒤쪽 TRUE = 텍스트 |
| 표 쪽 형식 | =ISNUMBER(A3) · =ISTEXT(A3) | A3 자리에 찾는값과 같아야 할 표의 칸을 넣습니다 |
한쪽은 숫자(ISNUMBER가 TRUE), 다른 쪽은 텍스트(ISTEXT가 TRUE)로 갈리면 이 원인일 가능성이 높습니다. ISNUMBER가 FALSE라는 것만으로는 텍스트라고 단정할 수 없습니다. 빈 칸·TRUE/FALSE 같은 논리값·오류 값도 FALSE가 나오기 때문에 ISTEXT를 함께 봅니다.
| 상황 | 고치는 수식 |
|---|---|
| 찾는값은 텍스트, 표는 숫자 | =VLOOKUP(VALUE(E2),$A$2:$C$5,3,FALSE) |
| 찾는값은 숫자, 표는 텍스트 | =VLOOKUP(E2&"",$A$2:$C$5,3,FALSE) |
E2&""는 칸에 저장된 숫자 값을 텍스트로 바꿀 뿐, 셀 서식으로 보이는 모양(앞의 0, 쉼표 등)은 따라가지 않습니다. 예를 들어 사용자 지정 서식으로 001002처럼 보이는 숫자 1002도 결과는 "1002"입니다. 그래서 표의 텍스트가 "1002"처럼 숫자를 그대로 적은 모양일 때만 맞습니다. 표의 코드가 "001002"처럼 앞에 0이 붙어 있다면 E2&""는 "1002"가 되어 여전히 #N/A입니다. 이때는 =VLOOKUP(TEXT(E2,"000000"),$A$2:$C$5,3,FALSE)처럼 표와 같은 자릿수를 직접 지정합니다.
근본적으로는 표의 형식을 통일하는 편이 낫습니다. 주의할 점은 셀 서식 대화상자(Ctrl+1)에서 「숫자」를 고르는 것만으로는 이미 텍스트로 저장된 "1002"가 숫자로 바뀌지 않을 수 있다는 것입니다. Ctrl+1은 서식을 확인·지정하는 용도로 쓰고, 실제 값을 바꿀 때는 아래 방법을 씁니다.
- 열 전체: 원하는 서식을 먼저 지정한 뒤 「데이터 > 텍스트 나누기 > 마침」(마이크로소프트 문서가 안내하는 방법)
- 몇 칸만: 초록 삼각형이 있는 칸을 선택하고 옆에 뜨는 오류 표시 단추에서 「숫자로 변환」
- 수식으로: 빈 열에
=VALUE(A2)를 채운 뒤 값으로 붙여 넣기
4. 마지막 인수(TRUE/FALSE)
마지막 인수를 생략하면 대략 일치(TRUE)로 동작합니다. 마이크로소프트 문서는 이 방식이 첫 열이 숫자나 문자 기준 오름차순(작은 값 → 큰 값, ㄱ → ㅎ)으로 정렬되어 있다고 가정하며, 인수를 지정하지 않을 때의 기본값이라고 밝힙니다. 코드·이름처럼 같은 값을 찾는 용도라면 끝에 FALSE를 붙입니다.
=VLOOKUP(E2,$A$2:$C$5,3,FALSE)대략 일치에서 찾는값이 첫 열의 최솟값보다 작으면 #N/A입니다. 예를 들어 첫 열이 60, 70, 80, 90인 기준표에서 =VLOOKUP(50,$A$2:$B$5,2,TRUE)는 #N/A입니다. 정렬되지 않은 표에서 TRUE를 쓰면 #N/A이거나 엉뚱한 행의 값이 나올 수 있어서, 이 경우 오류가 없다고 안심하면 안 됩니다.
5. 찾는 열이 범위의 첫 열이 아니다
마이크로소프트 문서는 찾는 값이 표 범위의 첫 번째 열에 있어야 한다고 설명합니다. 코드(A열)를 찾으려는데 범위를 B2:C5로 잡으면 B열(품명)에서 코드를 찾게 되어 #N/A입니다.
| 수식 | 결과 |
|---|---|
=VLOOKUP("P003",B2:C5,2,FALSE) | #N/A (B열에서 P003을 찾음) |
=VLOOKUP("P003",A2:C5,3,FALSE) | 300 |
범위를 찾는 열부터 시작하고, 열 번호도 그 범위 안에서 다시 셉니다. 찾는 열보다 왼쪽 값을 가져와야 한다면 열 순서를 바꾸거나 XLOOKUP 같은 다른 함수를 써야 합니다. XLOOKUP은 Microsoft 365·Excel 2021 이후 버전에서 쓸 수 있고, Excel 2016·2019에서는 INDEX와 MATCH를 함께 쓰는 방법이 대안입니다.
6. 수식을 복사했더니 범위가 밀렸다
E2에 =VLOOKUP(D2,A2:C5,3,FALSE)를 쓰고 아래로 복사하면 E3은 =VLOOKUP(D3,A3:C6,3,FALSE)가 됩니다. 표 범위가 한 칸씩 내려가므로 위쪽 행의 값은 범위 밖으로 밀려납니다. 예를 들어 D3에 P001을 넣으면 E3의 범위 A3:C6에는 P001(2행)이 없어 #N/A입니다. 표 범위에 $를 붙여 고정합니다.
=VLOOKUP(D2,$A$2:$C$5,3,FALSE)마이크로소프트 문서도 수식을 복사할 때 범위가 바뀌지 않도록 절대 참조를 쓰라고 권합니다. 수식 안에서 범위를 선택하고 F4를 누르면 $가 붙습니다.
#N/A를 숨기는 법과 주의점
값이 없을 수 있는 게 정상인 표라면 오류 대신 문구를 보이게 할 수 있습니다. IFNA는 #N/A만 대체하고 다른 오류는 그대로 둡니다. 다만 IFNA는 「코드가 없다」와 「코드는 있는데 가져올 칸에 #N/A가 들어 있다」를 구별하지 못하고 둘 다 「없음」으로 바꿉니다. 가져올 열에 #N/A가 섞여 있을 수 있다면 먼저 원본 데이터를 확인하세요.
=IFNA(VLOOKUP(D2,$A$2:$C$5,3,FALSE),"없음")IFERROR는 #N/A뿐 아니라 #REF!·#VALUE! 등 모든 오류를 대체합니다. 그래서 열 번호를 잘못 센 실수(#REF!)까지 가려 버립니다. 원인을 먼저 고친 뒤, 정말 「없는 값이 있을 수 있는 경우」에만 IFNA를 씌우는 순서를 권합니다. IFNA는 Excel 2013에서 새로 생긴 함수라(마이크로소프트 함수 목록), Excel 2010 이하로 파일을 공유한다면 IFERROR를 쓰거나 상대 버전을 확인하세요.