엑셀 #N/A 오류는 조회하려는 값이 분명히 있는데도 결과를 찾지 못할 때 특히 답답합니다. VLOOKUP 수식을 다시 입력하거나 원본 데이터를 복사해도 해결되지 않는 경우가 있습니다.
저는 이 문제가 단순히 수식 하나를 잘못 입력해서 생기는 현상으로만 설명되면 독자가 실제 원인을 찾기 어렵다고 판단했습니다. 조회값의 데이터 형식, 숨은 문자, 함수의 일치 조건을 구분해서 확인해야 하기 때문입니다.
핵심 답변
엑셀에서 #N/A가 발생하면 먼저 조회값이 실제로 존재하는지 확인하고, 두 셀의 데이터 형식과 숨은 공백을 비교해야 합니다. 그다음 VLOOKUP의 FALSE, MATCH의 0처럼 정확히 일치하는 조건을 설정하고 조회 범위가 올바른지 점검합니다. IFNA와 IFERROR는 오류를 표시하는 방법이지 원인을 자동으로 해결하는 기능은 아닙니다.
목차 펼쳐보기
엑셀 #N/A 오류가 의미하는 것
#N/A는 수식이나 함수가 필요한 값을 사용할 수 없을 때 표시되는 오류입니다. VLOOKUP, HLOOKUP, XLOOKUP, MATCH 같은 조회 함수에서는 검색 조건에 맞는 값을 찾지 못했을 때 자주 발생합니다.
다만 모든 #N/A가 조회 함수 때문에 발생하는 것은 아닙니다. 원본 데이터에 직접 입력된 #N/A 또는 NA() 값, 배열 수식의 범위 불일치, 사용자 정의 함수와 매크로 문제에서도 나타날 수 있습니다. 따라서 먼저 오류가 발생한 셀의 수식을 확인해야 합니다.
| 발생 상황 | 먼저 확인할 내용 |
|---|---|
| VLOOKUP 오류 | 조회값 존재 여부, 데이터 형식, 정확 일치 조건 |
| MATCH 오류 | match_type과 조회 배열의 정렬 순서 |
| INDEX/MATCH 오류 | MATCH 결과, 배열 범위와 입력 방식 |
| 여러 셀에서 동시에 오류 | 원본 데이터 누락, 공통 조회 범위, 수식 참조 |
값이 있는데도 조회되지 않을 때 확인할 7단계
조회값이 원본 표에 보인다고 해서 Excel이 두 값을 동일하게 인식한다는 뜻은 아닙니다. 다음 순서대로 확인하면 원인을 좁힐 수 있습니다.
1단계. 조회할 값이 실제로 존재하는지 확인
먼저 수식에서 참조하는 셀과 원본 표의 값을 직접 비교합니다. 예를 들어 A2가 조회값이고 E2가 원본 표의 값이라면 다음 수식을 빈 셀에 입력합니다.
=A2=E2
결과가 TRUE이면 Excel이 두 값을 같다고 판단한 것입니다. FALSE이면 화면에 비슷하게 보여도 실제 데이터가 다를 수 있습니다. 이때 데이터 형식과 공백을 확인해야 합니다.
2단계. 숫자와 텍스트 형식이 같은지 확인
숫자 123과 텍스트로 저장된 "123"은 화면에서 같은 숫자처럼 보여도 데이터 형식이 다릅니다. 조회값은 숫자인데 원본 값은 텍스트라면 정확히 일치하는 조회에서 문제가 생길 수 있습니다.
다음 수식으로 각 셀의 형식을 확인할 수 있습니다.
=ISNUMBER(A2) =ISTEXT(A2)
ISNUMBER가 TRUE이면 숫자 데이터이고, ISTEXT가 TRUE이면 텍스트 데이터입니다. 조회값과 원본 값에 각각 적용해 결과를 비교합니다.
숫자로 무조건 변환하면 안 되는 데이터
사번, 상품코드, 우편번호처럼 앞자리 0이 중요한 값은 계산용 숫자가 아니라 식별번호일 수 있습니다. 예를 들어 00125를 숫자 125로 바꾸면 앞자리 0이 사라집니다. 이런 값은 양쪽 데이터를 텍스트로 통일하는 편이 적절합니다.
셀 서식에서 숫자를 선택하는 것만으로 이미 텍스트로 저장된 값이 숫자로 바뀌는 것은 아닙니다. 실제 변환이 필요하다면 데이터의 성격을 확인한 뒤 VALUE 함수나 데이터 탭의 텍스트 나누기 기능 등을 사용해야 합니다.
3단계. 앞뒤 공백과 글자 길이 확인
이름이나 상품코드에 공백이 포함되면 눈으로는 같은 값처럼 보여도 정확히 일치하지 않을 수 있습니다. LEN 함수로 글자 수를 비교하면 차이를 발견하는 데 도움이 됩니다.
=LEN(A2) =LEN(E2)
두 결과가 다르면 앞뒤 공백이나 추가 문자가 있는지 확인합니다. 글자 수가 같더라도 서로 다른 문자가 들어 있을 수 있으므로 LEN만으로 동일성을 확정해서는 안 됩니다.
4단계. 조회 함수의 정확 일치 조건 확인
상품코드나 사번처럼 값이 정확히 일치해야 하는 조회라면 VLOOKUP의 네 번째 인수에 FALSE를 지정합니다.
=VLOOKUP(A2,$E$2:$F$100,2,FALSE)
FALSE는 정확히 일치하는 값을 찾도록 합니다. TRUE 또는 인수 생략은 근사 일치 방식이며, 이 경우 조회 범위의 첫 번째 열을 정렬해야 합니다. 정렬 조건이 맞지 않으면 #N/A뿐 아니라 잘못된 결과가 표시될 수도 있습니다.
5단계. 조회 범위와 열 번호 확인
VLOOKUP은 지정한 범위의 첫 번째 열에서 조회값을 찾습니다. 조회값이 범위의 첫 번째 열에 없으면 원하는 결과를 가져올 수 없습니다. 반환할 열 번호도 지정한 범위의 왼쪽부터 1로 계산해야 합니다.
예를 들어 E열에서 상품코드를 찾고 F열의 가격을 가져오려면 E:F 범위를 지정하고 반환 열 번호를 2로 입력합니다. 수식을 아래로 복사할 예정이라면 조회 범위에 절대 참조 기호 $를 사용합니다.
=VLOOKUP(A2,$E$2:$F$100,2,FALSE)
6단계. 숨은 문자와 특수 공백 정리
외부 웹사이트나 다른 프로그램에서 복사한 데이터에는 일반 공백 외에 줄 바꿈 없는 공백이나 인쇄되지 않는 제어 문자가 포함될 수 있습니다. 이런 문자는 화면에서 쉽게 구분되지 않습니다.
일반적인 공백과 일부 제어 문자를 정리하려면 다음과 같이 사용할 수 있습니다.
=TRIM(CLEAN(A2))
웹에서 가져온 데이터에 CHAR(160)에 해당하는 공백이 있다면 SUBSTITUTE를 추가합니다.
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
이 수식은 CHAR(160)을 일반 공백으로 바꾼 뒤 CLEAN과 TRIM을 적용하는 방식입니다. 정리된 결과를 별도 열에 만든 다음 그 열을 기준으로 조회하면 원본 데이터를 보존하면서 비교할 수 있습니다.
7단계. 수식 복사와 배열 범위 확인
수식을 아래로 복사한 뒤 일부 행에서만 #N/A가 발생한다면 조회 범위가 함께 이동했는지 확인합니다. 예를 들어 E2:F100을 고정하지 않고 상대 참조로 사용하면 아래 행으로 복사할 때 범위가 달라질 수 있습니다.
또한 INDEX와 MATCH를 배열 수식으로 사용하는 오래된 Excel 파일에서는 배열 입력 방식과 참조 범위의 크기도 확인해야 합니다. Microsoft 문서에서는 배열 수식이 참조하는 범위와 수식이 입력된 범위의 행·열 수가 맞지 않는 경우도 #N/A 원인으로 설명합니다.
TRIM과 CLEAN으로 해결되지 않는 공백
TRIM과 CLEAN은 이름이 비슷하지만 처리하는 대상이 다릅니다. 두 함수가 모든 특수 문자를 제거한다고 생각하면 공백을 정리했는데도 조회 오류가 계속되는 이유를 찾기 어렵습니다.
| 함수 | 처리 대상 | 주의할 점 |
|---|---|---|
| TRIM | ASCII 공백 문자 32 | CHAR(160)은 단독으로 제거하지 못함 |
| CLEAN | ASCII 제어 문자 0~31 | 일부 추가 유니코드 문자는 남을 수 있음 |
| SUBSTITUTE | 지정한 문자를 다른 문자로 교체 | 대상 문자를 직접 지정해야 함 |
따라서 TRIM을 적용했는데도 값이 일치하지 않는다면 다른 공백 문자나 특수 문자가 남아 있는지 확인해야 합니다. CLEAN도 유니코드 문자를 모두 제거하지 못하므로 결과를 다시 비교하는 과정이 필요합니다.
VLOOKUP·MATCH·XLOOKUP의 일치 조건
조회 함수는 이름이 비슷해도 기본 일치 조건이 다릅니다. 정확한 값을 찾는 작업이라면 각 함수의 인수를 확인해야 합니다.
| 함수 | 정확 일치 조건 | 기본 설정 |
|---|---|---|
| VLOOKUP | FALSE 또는 0 | TRUE 또는 근사 일치 |
| MATCH | 0 | 1 또는 오름차순 근사 일치 |
| XLOOKUP | 0 | 정확 일치 |
MATCH에서 0을 사용하는 이유
MATCH의 세 번째 인수인 match_type은 검색 방식과 정렬 조건을 결정합니다. 0은 정확히 일치하는 값을 찾으며, 1은 오름차순, -1은 내림차순 정렬 조건을 전제로 근사 일치를 수행합니다.
=MATCH(A2,$E$2:$E$100,0)
사번이나 상품코드처럼 정확히 일치해야 하는 값을 찾을 때는 0을 지정하는 편이 적절합니다. match_type을 생략하면 1이 적용되므로 조회 배열이 오름차순으로 정렬되어 있어야 합니다.
XLOOKUP은 기본적으로 정확히 일치합니다
XLOOKUP은 조회 배열과 반환 배열을 별도로 지정할 수 있어 VLOOKUP처럼 조회값이 반드시 반환 범위의 왼쪽에 있어야 하는 구조가 아닙니다. 기본 일치 방식도 정확히 일치입니다.
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"미등록")
네 번째 인수에 "미등록"을 지정하면 일치하는 값이 없을 때 해당 문구를 반환합니다. 다만 XLOOKUP은 Excel 2016과 Excel 2019에서 사용할 수 없으므로 사용 중인 버전을 먼저 확인해야 합니다.
IFNA와 IFERROR로 오류 표시하기
조회 원인을 확인한 뒤에는 오류가 발생했을 때 표시할 내용을 정할 수 있습니다. IFNA와 IFERROR는 오류를 다른 값으로 바꾸는 함수지만 처리 범위가 다릅니다.
| 함수 | 처리 범위 | 적합한 상황 |
|---|---|---|
| IFNA | #N/A만 처리 | 조회 결과가 없을 때 문구 표시 |
| IFERROR | 여러 수식 오류 처리 | 다양한 오류에 공통 표시 적용 |
IFNA를 이용하면 조회 결과가 없을 때만 안내 문구를 표시할 수 있습니다.
=IFNA(VLOOKUP(A2,$E$2:$F$100,2,FALSE),"조회 결과 없음")
IFERROR는 다음과 같이 사용할 수 있습니다.
=IFERROR(VLOOKUP(A2,$E$2:$F$100,2,FALSE),"확인 필요")
오류를 숨기기 전에 원인을 확인하세요
IFERROR로 모든 오류를 빈칸으로 바꾸면 조회값 누락뿐 아니라 잘못된 참조나 계산 오류도 눈에 띄지 않을 수 있습니다. 원인을 확인하고 수식이 의도대로 작동하는지 검증한 다음 오류 표시 방식을 적용해야 합니다.
엑셀 #N/A 오류 자주 묻는 질문
VLOOKUP에서 값이 분명히 있는데 #N/A가 나오는 이유는 무엇인가요?
조회값과 원본 값의 데이터 형식이 다르거나 앞뒤 공백, 특수 문자, 조회 범위 설정 문제가 있을 수 있습니다. 두 셀에 `=A2=E2`를 적용해 실제로 같은 값으로 인식되는지 먼저 확인합니다.
TRIM을 사용했는데도 #N/A가 해결되지 않는 이유는 무엇인가요?
TRIM은 ASCII 공백 문자 32를 대상으로 하며 CHAR(160)에 해당하는 줄 바꿈 없는 공백은 제거하지 못합니다. SUBSTITUTE로 CHAR(160)을 일반 공백으로 바꾼 뒤 TRIM을 적용해 볼 수 있습니다.
VLOOKUP에서 FALSE를 입력해야 하는 이유는 무엇인가요?
FALSE는 정확히 일치하는 값을 찾도록 지정합니다. 상품코드나 사번처럼 값 자체가 일치해야 하는 조회에서는 TRUE의 근사 일치보다 FALSE를 사용하는 것이 적절합니다.
IFERROR를 사용하면 #N/A 오류가 해결되나요?
오류 표시를 다른 값으로 바꿀 수는 있지만 조회 실패 원인을 해결하는 것은 아닙니다. 데이터 형식이나 조회 범위가 잘못된 상태라면 먼저 원인을 수정해야 합니다.
XLOOKUP이 모든 Excel 버전에서 작동하나요?
아닙니다. Microsoft 공식 문서에 따르면 Excel 2016과 Excel 2019에서는 XLOOKUP을 사용할 수 없습니다. 해당 버전에서는 VLOOKUP 또는 INDEX/MATCH 같은 호환 함수를 검토해야 합니다.
결론: 오류 표시보다 조회 조건이 먼저입니다
이번 자료를 비교하면서 확인한 핵심은 #N/A가 단순히 데이터가 없다는 뜻으로만 해석되면 안 된다는 점입니다. 실제 값이 있어도 형식과 문자, 검색 조건이 다르면 Excel은 일치하는 값으로 판단하지 않을 수 있습니다.
저는 조회 오류를 해결할 때 먼저 두 값의 동일성을 확인하고, 그다음 형식과 숨은 문자, 함수 조건을 점검하는 순서가 가장 합리적이라고 봅니다. 오류를 감추는 것보다 조회 결과가 정확한지 확인하는 일이 우선입니다.
0 댓글