엑셀 VLOOKUP에서 #N/A가 뜨는 가장 흔한 이유는 조회값을 원본 표에서 찾지 못했기 때문입니다. 특히 숫자와 텍스트 형식이 다르거나 앞뒤 공백이 섞이면 눈으로 같은 값처럼 보여도 일치하지 않으므로, 수식보다 데이터 형식을 먼저 점검하는 것이 빠릅니다.
📌 핵심 5줄 요약
1. 조회값이 원본 표의 첫 번째 열에 실제로 존재하는지 먼저 확인합니다.
2. 일반적인 항목 조회는 마지막 인수를 FALSE로 지정해 정확히 일치시킵니다.
3. 숫자로 보이는 값이 한쪽은 숫자, 다른 쪽은 텍스트인지 확인합니다.
4. TRIM, CLEAN, VALUE 또는 숫자 변환으로 숨은 공백과 형식을 정리합니다.
5. IFERROR는 오류를 숨기는 용도이므로 원인 확인 후 마지막에 적용합니다.
📌 목차
01 | 기본 수식과 #N/A의 의미
02 | 조회값이 실제로 존재하는지 확인
03 | FALSE와 TRUE 옵션 구분
04 | 숫자와 텍스트 형식 점검
05 | 숫자 텍스트를 숫자로 변환
06 | 텍스트 숫자로 통일하기
07 | 앞뒤 공백 제거
08 | 숨은 문자와 줄바꿈 정리
09 | 조회 범위와 첫 열 확인
10 | 열 번호와 참조 고정 점검
11 | IFERROR를 올바르게 사용
12 | 실제 사례로 오류 수정
13 | 다른 오류와 구분하는 방법
14 | VLOOKUP 대신 XLOOKUP을 쓸 때
01 | 기본 수식과 #N/A의 의미
VLOOKUP의 기본 구조는 =VLOOKUP(조회값, 조회범위, 반환열번호, 일치방식)입니다. 조회값은 조회범위의 첫 번째 열에서 찾고, 찾은 행의 지정된 열에서 결과를 반환합니다. 마지막 인수를 생략하면 근사값 일치가 적용될 수 있으므로, 일반적인 코드·상품명·사번 조회에는 FALSE를 명시하는 편이 안전합니다.
#N/A는 수식이 요청한 값을 찾지 못했다는 뜻입니다. 수식 자체가 반드시 틀렸다는 의미는 아니며, 실제로 원본 표에 값이 없거나 값의 형식·공백이 서로 다른 경우에도 발생합니다.
02 | 조회값이 실제로 존재하는지 확인
가장 먼저 조회값을 복사해 원본 표의 첫 번째 열에서 검색해 보세요. 원본 표에 값이 없다면 VLOOKUP은 정상적으로 #N/A를 반환합니다. 비슷한 이름이나 일부만 같은 값은 정확히 일치하는 값으로 처리되지 않습니다.
예를 들어 조회 셀에 ‘A-100’을 입력했지만 원본에는 ‘A-100-’ 또는 ‘A-100 ’처럼 다른 문자가 포함되어 있으면 서로 다른 값입니다. 원본 데이터와 조회 셀을 직접 비교하고, 필요하면 COUNTIF로 존재 여부를 확인할 수 있습니다.
=COUNTIF($A$2:$A$100,D2)의 결과가 0이면 D2와 정확히 같은 값이 조회 범위에 없다는 뜻입니다.
03 | FALSE와 TRUE 옵션 구분
일반적인 상품번호, 직원번호, 고객코드 조회는 다음처럼 작성합니다.
=VLOOKUP(D2,$A$2:$C$100,3,FALSE)
FALSE는 정확히 같은 값을 찾습니다. 반면 TRUE 또는 생략은 근사값을 찾으며, 첫 번째 열이 오름차순으로 정렬되어 있어야 합니다. 정렬되지 않은 표에서 TRUE를 사용하면 #N/A뿐 아니라 잘못된 결과가 반환될 수 있습니다.
구간별 세율, 등급, 할인율처럼 기준값에 따라 범위를 찾는 경우가 아니라면 TRUE를 기본값처럼 사용하지 않는 것이 좋습니다.
04 | 숫자와 텍스트 형식 점검
VLOOKUP #N/A의 대표적인 원인은 한쪽 값은 숫자이고 다른 쪽 값은 텍스트인 경우입니다. 두 셀에 모두 100이 표시되어도 실제 저장 형식이 다르면 정확한 일치가 되지 않을 수 있습니다. Microsoft도 조회값과 원본 데이터의 데이터 형식을 동일하게 맞추도록 안내합니다.
확인 방법은 간단합니다. 셀을 선택했을 때 숫자가 왼쪽에 정렬되거나 셀 왼쪽 위에 초록색 표시가 있으면 텍스트 숫자일 가능성이 있습니다. 다만 셀 서식만 숫자로 바꾸는 것으로는 이미 텍스트로 저장된 값이 자동 변환되지 않을 수 있습니다.
05 | 숫자 텍스트를 숫자로 변환
원본 데이터가 텍스트로 저장된 숫자라면 빈 셀에 숫자 1을 입력하고 복사한 뒤, 대상 범위를 선택해 선택하여 붙여넣기 → 곱하기를 적용하는 방법이 있습니다. 텍스트 숫자가 실제 숫자로 변환되면서 VLOOKUP이 정상 작동할 수 있습니다.
보조 열을 사용할 수 있다면 다음 수식도 활용할 수 있습니다.
=VALUE(A2)
변환된 결과를 복사한 뒤 값으로 붙여넣으면 원본 열을 정리할 수 있습니다. 단, 사번이나 우편번호처럼 앞자리 0이 중요한 코드는 숫자로 바꾸면 0이 사라질 수 있으므로 텍스트 형식으로 통일해야 합니다.
06 | 텍스트 숫자로 통일하기
코드값이 숫자처럼 보이지만 실제로는 문자로 관리되어야 한다면 조회값과 원본값을 모두 텍스트로 맞추세요. 조회 셀에 다음처럼 입력할 수 있습니다.
=VLOOKUP(TEXT(D2,"0"),$A$2:$C$100,3,FALSE)
다만 코드의 자릿수가 정해져 있다면 TEXT(D2,"00000")처럼 필요한 형식을 지정해야 합니다. 원본에도 같은 규칙을 적용해야 하며, 한쪽만 변환하면 문제가 계속될 수 있습니다.
07 | 앞뒤 공백 제거
복사·붙여넣기나 외부 파일 가져오기 과정에서 이름과 코드 앞뒤에 공백이 들어갈 수 있습니다. 이때는 TRIM으로 불필요한 일반 공백을 제거합니다.
=TRIM(A2)
원본 표의 조회 열에 보조 열을 만들고 TRIM 결과를 채운 뒤, 결과를 값으로 붙여넣어 정리하면 됩니다. Microsoft는 VLOOKUP 조회 과정에서 셀의 앞뒤 공백이 원인이 될 수 있으며 TRIM으로 제거할 수 있다고 안내합니다.
08 | 숨은 문자와 줄바꿈 정리
TRIM으로도 해결되지 않는다면 줄바꿈이나 인쇄되지 않는 문자가 포함되었을 수 있습니다. CLEAN은 일부 제어 문자를 제거하는 데 사용할 수 있습니다.
=TRIM(CLEAN(A2))
웹페이지나 CSV 파일에서 가져온 데이터에는 일반 공백과 다른 특수 공백이 들어오는 경우도 있습니다. 이런 경우에는 찾아 바꾸기로 문제 문자를 제거하거나, 데이터 원본 단계에서 불필요한 문자를 정리한 후 조회하는 것이 안정적입니다.
09 | 조회 범위와 첫 열 확인
VLOOKUP은 조회범위의 가장 왼쪽 첫 번째 열에서만 조회값을 찾습니다. 조회값이 B열에 있는데 범위를 A열부터 잡으면 원하는 값을 찾지 못할 수 있습니다.
예를 들어 조회값이 B열에 있다면 수식 범위도 B열에서 시작해야 합니다.
=VLOOKUP(F2,$B$2:$D$100,3,FALSE)
VLOOKUP은 기본적으로 왼쪽에서 오른쪽 방향으로 값을 반환합니다. 조회 기준 열이 반환할 데이터보다 오른쪽에 있다면 범위를 다시 설계하거나 다른 조회 함수를 검토해야 합니다.
10 | 열 번호와 참조 고정 점검
세 번째 인수인 열 번호는 시트의 실제 열 번호가 아니라 조회범위 안에서의 순서입니다. 범위가 B:D라면 B열은 1, C열은 2, D열은 3입니다.
수식을 아래로 복사할 때 범위가 움직이면 결과가 달라질 수 있으므로 $를 사용해 범위를 고정하세요.
=VLOOKUP(F2,$B$2:$D$100,3,FALSE)
범위 안의 열 수보다 큰 번호를 입력하면 #REF!가 발생합니다. VLOOKUP의 오류가 #N/A가 아니라면 열 번호와 범위 크기도 함께 확인해야 합니다.
11 | IFERROR를 올바르게 사용
원인 해결 후에도 조회되지 않는 항목을 빈칸이나 안내 문구로 표시하고 싶다면 IFERROR를 사용할 수 있습니다.
=IFERROR(VLOOKUP(D2,$A$2:$C$100,3,FALSE),"조회 안 됨")
다만 IFERROR는 잘못된 데이터나 수식 오류를 가리는 역할을 할 뿐, 조회 실패의 원인을 고치지는 않습니다. 먼저 원본 값, 형식, 공백, 범위를 확인한 뒤 마지막 단계에서 표시 형식을 정리하세요.
12 | 실제 사례로 오류 수정
예를 들어 D2에 고객번호 1001이 있고, 원본 표 A열에도 1001이 보이는데 VLOOKUP이 #N/A를 반환한다고 가정해 보겠습니다. 먼저 =COUNTIF($A$2:$A$100,D2)를 입력해 0이 나오는지 확인합니다.
결과가 0이면 A열의 1001이 텍스트로 저장되었거나 공백이 포함되었을 가능성이 큽니다. A열 보조 열에 =VALUE(TRIM(A2))를 적용하고, D2도 같은 형식으로 정리한 뒤 다음 수식을 다시 사용합니다.
=VLOOKUP(D2,$E$2:$G$100,3,FALSE)
반대로 고객번호가 ‘001001’처럼 앞자리 0을 포함하는 코드라면 VALUE로 변환하지 말고 양쪽을 동일한 텍스트 형식으로 유지해야 합니다.
13 | 다른 오류와 구분하는 방법
| 오류 | 주요 확인 사항 |
|---|---|
| #N/A | 조회값 없음, 데이터 형식 불일치, 공백, TRUE 사용 여부 |
| #REF! | 조회범위에 비해 반환 열 번호가 너무 큰지 확인 |
| #VALUE! | 인수 형식이나 조회범위가 올바른지 확인 |
| #NAME? | 함수명 오타, 따옴표, 시트명 표기 확인 |
| #SPILL! | 전체 열 참조나 주변 셀의 방해 여부 확인 |
#N/A인데 무조건 IFERROR로 감추면 실제 누락 데이터까지 정상처럼 보일 수 있습니다. 오류 종류를 먼저 구분하고 원인에 맞는 조치를 적용하세요.
14 | VLOOKUP 대신 XLOOKUP을 쓸 때
사용 중인 Excel 버전이 XLOOKUP을 지원한다면 대체 함수로 검토할 수 있습니다. XLOOKUP은 조회 범위와 반환 범위를 따로 지정하고, 기본적으로 정확히 일치하는 값을 찾으며, 찾지 못했을 때 표시할 문구도 별도 인수로 지정할 수 있습니다.
예시는 다음과 같습니다.
=XLOOKUP(D2,$A$2:$A$100,$C$2:$C$100,"조회 안 됨")
다만 파일을 공유하는 상대방이 구버전 Excel을 사용한다면 호환성을 먼저 확인해야 합니다. 기존 파일을 계속 사용해야 한다면 VLOOKUP 수식을 유지하고 데이터 형식과 조회 조건을 정리하는 것이 안전합니다.
☑ 체크리스트
- 조회값이 원본 표의 첫 번째 열에 있는지 확인했나요?
- VLOOKUP 마지막 인수를 FALSE로 입력했나요?
- 조회값과 원본값이 모두 숫자 또는 모두 텍스트인가요?
- 앞뒤 공백과 줄바꿈이 포함되어 있지 않나요?
- 조회 범위를 $ 기호로 고정했나요?
- 반환 열 번호가 조회 범위 안의 순서와 일치하나요?
- IFERROR를 적용하기 전에 원인을 먼저 해결했나요?
📖 자주하는 질문 Q&A
VLOOKUP에서 #N/A가 뜨면 가장 먼저 무엇을 확인해야 하나요?
조회값이 원본 표의 첫 번째 열에 실제로 존재하는지 확인하세요. 그다음 숫자·텍스트 형식, 앞뒤 공백, FALSE 옵션 순서로 점검하면 대부분의 원인을 빠르게 찾을 수 있습니다.
셀 서식을 숫자로 바꾸면 VLOOKUP 오류가 해결되나요?
이미 텍스트로 저장된 숫자는 셀 서식만 바꿔도 실제 숫자로 변환되지 않을 수 있습니다. VALUE 함수, 곱하기 붙여넣기, 텍스트 나누기 등을 사용해 값 자체를 변환해야 합니다.
IFERROR로 #N/A를 빈칸으로 바꿔도 되나요?
보고서 표시용으로는 가능하지만, 원인 확인 전에 사용하면 누락 데이터가 감춰질 수 있습니다. 먼저 조회 실패 원인을 해결한 뒤 최종 화면에서만 적용하는 것이 좋습니다.
VLOOKUP에서 TRUE를 사용해도 되나요?
구간별 기준값을 찾는 등 근사값 조회가 필요한 경우에 사용할 수 있습니다. 이때 첫 번째 열은 오름차순으로 정렬되어야 하며, 일반 코드 조회에는 FALSE가 적합합니다.
📝 마무리
VLOOKUP #N/A 오류는 대부분 조회값 누락, 숫자와 텍스트의 형식 차이, 불필요한 공백, 잘못된 일치 옵션에서 발생합니다. FALSE를 기본으로 사용하고 데이터 형식과 조회 범위를 차례로 점검하면 수식을 무작정 다시 작성하지 않고도 문제를 해결할 수 있습니다.