표에 값이 분명히 보이는데 VLOOKUP이 #N/A를 돌려준다면 원인은 대개 둘 중 하나입니다. 수식 인수가 잘못됐거나, 화면에서는 같아 보이는 두 값을 엑셀이 다른 값으로 보는 경우입니다. 이 글은 원인을 늘어놓는 대신 확인할 순서를 정리했습니다. 인수 확인, LEN으로 공백 찾기, ISNUMBER·ISTEXT로 자료형 확인, 수정, 재검증 순서로 따라가면 됩니다. 예제 표는 설명용 가상 데이터이고, 함수 동작은 Microsoft 지원 문서를 기준으로 했습니다.
설명용 그림: 값이 있는데 #N/A가 나올 때 확인 순서를 정리한 대표 이미지입니다.
값이 보이는데 왜 #N/A가 나오나요?
정확히 일치로 찾을 때 VLOOKUP은 찾는 값이 조회 범위에 없으면 #N/A를 반환합니다. 여기서 없다는 판단은 화면에 보이는 모양이 아니라 셀에 실제로 저장된 값을 기준으로 합니다.
Microsoft 안내에 따르면 앞뒤 공백, 곧은 따옴표와 둥근 따옴표의 혼용, 인쇄되지 않는 문자가 섞여 있거나 숫자·날짜가 텍스트로 저장돼 있으면 VLOOKUP이 예상과 다른 결과를 낼 수 있습니다. 눈으로는 둘 다 `A-101`로 보여도 한쪽 끝에 공백이 하나 붙어 있으면 다른 값으로 봅니다.
설명용 그림: #N/A 원인을 찾는 다섯 단계의 확인 순서입니다.
1단계: 네 번째 인수와 조회 범위부터 확인
수식 입력줄에서 아래 세 가지를 먼저 봅니다.
- 네 번째 인수(range_lookup)를 생략하면 기본값은 TRUE, 즉 근사 일치입니다. 정확히 찾으려면 `FALSE`나 `0`을 직접 적습니다. 근사 일치에서는 찾는 값이 첫 열의 최솟값보다 작을 때 #N/A가 나오고, 정렬되지 않은 데이터에서는 엉뚱한 값이 나올 수도 있습니다.
- 찾는 값은 조회 범위의 첫 번째 열에 있어야 합니다. 열 번호는 범위의 가장 왼쪽 열을 1로 셉니다.
- 수식을 아래로 복사했다면 조회 범위가 함께 밀려 내려가지 않았는지 봅니다. `$A$2:$B$7`처럼 고정해 두면 범위가 바뀌지 않습니다.
| 확인 결과 | 의심할 원인 |
|---|---|
| 네 번째 인수가 비었거나 TRUE | 근사 일치로 찾는 중 |
| 찾는 값이 범위 첫 열이 아닌 곳에 있음 | 범위 지정 오류 |
| #N/A가 아니라 #REF!가 나옴 | 열 번호가 범위의 열 수보다 큼 |
| 셋 다 문제없음 | 2단계로 이동 |
2단계: 공백이 있는지 LEN으로 구분하기
LEN은 문자 수를 반환하고 공백도 한 글자로 셉니다. 보이는 글자 수와 LEN 결과가 다르면 숨은 문자가 있습니다.
가상 예제로 조회값 D2에 `A-101`, 원본 표 A3에 끝에 공백이 붙은 `A-101 `이 들어 있다고 해 보겠습니다.
| 셀 | 수식 | 결과 |
|---|---|---|
| D2 | `=LEN(D2)` | 5 |
| A3 | `=LEN(A3)` | 6 |
한 글자가 더 있다면 그 문자가 무엇인지 `=CODE(RIGHT(A3,1))`로 코드값을 확인합니다. 앞쪽이 의심되면 RIGHT 대신 LEFT를 씁니다.
| CODE 결과 | 정체 | 처리 함수 |
|---|---|---|
| 32 | 일반 공백 | TRIM |
| 160 | 비분리 공백 | TRIM만으로는 안 지워짐, SUBSTITUTE 필요 |
| 10 | 줄바꿈 | CLEAN |
웹에서 복사한 데이터에 160번 문자가 섞이는 경우가 있습니다. TRIM은 코드 32 공백만 지우므로 TRIM을 써도 LEN이 그대로라면 이 문자를 의심합니다.
설명용 그림: CODE 결과별 문자의 정체와 처리 함수를 정리한 표입니다.
3단계: 숫자와 텍스트를 ISNUMBER·ISTEXT로 구분하기
LEN이 같은데도 #N/A가 나오면 자료형을 봅니다. 조회값과 원본 첫 열 셀에 각각 `=ISNUMBER(D2)`, `=ISTEXT(A3)`를 넣습니다. IS 함수는 인수를 변환하지 않으므로 `ISNUMBER("19")`는 FALSE입니다. 따옴표에 든 숫자, 즉 텍스트 숫자는 숫자로 판정되지 않습니다.
가상 예제로 D2에 숫자 1001, A3에 텍스트 1001이 있으면 두 셀 모두 `1001`로 보이지만 결과가 갈립니다.
| D2 ISNUMBER | A3 ISNUMBER | 판정 |
|---|---|---|
| TRUE | TRUE | 자료형은 같음, 다른 원인 확인 |
| TRUE | FALSE | 원본이 텍스트 숫자 |
| FALSE | TRUE | 조회값이 텍스트 숫자 |
`00123`처럼 앞자리 0이 있는 코드는 숫자로 바꾸면 0이 사라집니다. 이런 열은 텍스트로 두는 편이 맞습니다. 셀을 텍스트 서식으로 바꿔도 그 뒤에 입력한 값에만 적용되고 이미 사라진 0은 돌아오지 않습니다. 이때는 `=TEXT(A3,"00000")`로 자릿수를 맞춘 텍스트를 만듭니다.
4단계: TRIM·CLEAN·SUBSTITUTE·VALUE 중 무엇으로 고칠까
2~3단계 결과에 맞춰 함수를 고릅니다. 원본을 바로 덮어쓰지 말고 옆에 보조 열을 만들어 결과를 확인합니다.
| 진단 결과 | 수식 예 |
|---|---|
| 일반 공백(32) | `=TRIM(A3)` |
| 줄바꿈(10) | `=CLEAN(A3)` |
| 비분리 공백(160) | `=TRIM(CLEAN(SUBSTITUTE(A3,CHAR(160)," ")))` |
| 텍스트 숫자 → 숫자 | `=VALUE(A3)` |
| 숫자 코드 → 앞자리 0 텍스트 | `=TEXT(A3,"00000")` |
Microsoft는 TRIM·CLEAN·SUBSTITUTE를 함께 쓰라고 권장합니다. SUBSTITUTE로 160번 문자를 일반 공백으로 바꾸면 TRIM이 지울 수 있습니다. CLEAN은 코드 0~31 문자를 지우지만 127, 129, 141, 143, 144, 157번 문자는 못 지웁니다.
VALUE는 숫자로 읽을 수 있는 텍스트만 바꾸고, 그렇지 못한 형식이면 #VALUE!를 반환합니다. #VALUE!가 나오면 셀에 공백이나 다른 문자가 남은 것이니 2단계로 돌아갑니다.
조회값과 원본 표 중 어느 쪽을 고쳐야 하나요?
문제가 있는 쪽을 고칩니다. 공백이 원본 표에 있는데 조회값만 TRIM으로 정리하면 원본의 `A-101 `과는 여전히 맞지 않아 #N/A가 계속 나옵니다. 여러 수식이 같은 원본 표를 참조한다면 원본 첫 열을 정리하는 쪽이 재발이 적습니다.
원본을 건드릴 수 없을 때는 수식 안에서 맞춥니다. 조회값 쪽은 `=VLOOKUP(TRIM(D2),$A$2:$B$7,2,FALSE)`처럼 감싸고, 원본 쪽 공백은 조회 범위를 TRIM으로 감쌉니다.
`=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)`
동적 배열을 지원하는 버전에서는 Enter만 누르면 되고, 이전 버전에서는 Ctrl+Shift+Enter로 배열 수식으로 입력해야 합니다.
IFERROR로 #N/A를 숨겨도 되나요?
권하지 않습니다. IFERROR는 #N/A뿐 아니라 #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!까지 모두 잡습니다. 열 번호를 잘못 적어 생긴 #REF!도 빈칸이나 "없음"으로 바뀌므로 수식이 틀린 줄 알아채기 어렵습니다.
찾는 값이 실제로 없을 때만 표시를 바꾸려면 IFNA를 씁니다. IFNA는 #N/A일 때만 지정한 값을 반환하고 다른 오류는 그대로 보여 줍니다. Microsoft 365, 2024, 2021, 2019, 2016에서 지원합니다.
`=IFNA(VLOOKUP(D2,$A$2:$B$7,2,FALSE),"없음")`
IFNA를 씌우기 전에 정말 값이 없는지부터 가려야 합니다. LEN과 자료형이 같아도 두 값이 같은 문자열이라는 보장은 없습니다. 곧은 따옴표와 둥근 따옴표, 단어 사이에 낀 일반 공백과 비분리 공백(CHAR(160))은 글자 수가 같습니다. 하이픈(-)과 en dash(–), 전각 문자와 반각 문자처럼 눈으로는 같아 보여도 서로 다른 문자일 수 있습니다.
그래서 조회값과 원본 셀을 `=EXACT(D2,A3)`로 직접 비교합니다. EXACT는 두 텍스트가 정확히 같을 때만 TRUE를 반환하고 대소문자도 구분합니다. FALSE가 나오면 `=CODE(MID(A3,n,1))`의 n에 1, 2, 3을 차례로 넣어 한 글자씩 코드값을 보고 D2에도 같은 수식을 넣어 어느 위치가 다른지 찾습니다. 이렇게 확인해도 원본 첫 열에 같은 값이 없을 때 원본에 값이 없다고 판단합니다.
고친 뒤 검증하는 방법과 재발 방지
수정한 셀에 2~3단계 수식을 다시 넣어 봅니다. 조회값과 원본의 LEN이 같고 ISNUMBER(또는 ISTEXT) 결과가 같아야 합니다. 그다음 VLOOKUP을 아래로 복사해 다른 행에서도 결과가 나오는지 확인합니다.
재발을 막으려면 코드 열처럼 앞자리 0이 있는 열은 값을 입력하기 전에 텍스트 서식을 지정합니다. 서식은 지정한 뒤에 입력한 값에만 적용되기 때문입니다.
XLOOKUP을 쓸 수 있는 환경이라면 대안으로 검토할 만합니다. match_mode 기본값이 정확히 일치이므로 FALSE를 빠뜨릴 일이 없고, if_not_found 인수로 값이 없을 때 보여 줄 내용을 정할 수 있습니다. 생략하면 #N/A를 반환합니다. 다만 XLOOKUP 지원 여부는 엑셀 버전마다 다르니 사용 중인 버전을 먼저 확인하세요.
정리
VLOOKUP이 값이 보이는데도 #N/A를 낼 때는 다음 순서로 확인합니다. 1) 네 번째 인수 FALSE, 범위 첫 열, 열 번호, $ 고정 확인 2) LEN과 CODE로 공백(32), 비분리 공백(160), 줄바꿈(10) 찾기 3) ISNUMBER·ISTEXT로 텍스트 숫자 가리기 4) TRIM·CLEAN·SUBSTITUTE·VALUE·TEXT로 문제 있는 쪽 고치기 5) LEN과 자료형을 다시 비교해 검증하기. 오류를 숨길 때는 IFERROR보다 IFNA가 안전합니다.
참고한 자료
- VLOOKUP function (https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1)
- How to correct a #N/A error (https://support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error)
- TRIM function (https://support.microsoft.com/en-us/office/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9)
- Top ten ways to clean your data (https://support.microsoft.com/office/top-ten-ways-to-clean-your-data-2844b620-677c-47a7-ac3e-c2e157d1db19)
- CLEAN function (https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41)
- VALUE function (https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)
- IS functions (https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)
- SUBSTITUTE function (https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)
- LEN, LENB functions (https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)
- IFERROR function (https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)
- IFNA function (https://support.microsoft.com/en-us/office/ifna-function-6626c961-a569-42fc-a49d-79b4951fd461)
- Keeping leading zeros and large numbers (https://support.microsoft.com/en-us/office/keeping-leading-zeros-and-large-numbers-1bf7b935-36e1-4985-842f-5dfa51f85fe7)
- XLOOKUP function (https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)
- VLOOKUP (https://support.google.com/docs/answer/3093318?hl=en)
- EXACT function (https://support.microsoft.com/en-us/office/exact-function-d3087698-fc15-4a15-9631-12575cf29926)
- CODE function (https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928)