엑셀·구글시트 VLOOKUP #N/A 오류 해결: 공백·숫자 형식·검색 범위 확인하기
분명히 표에 있는 주문번호인데 VLOOKUP 결과가 #N/A로 나옵니다. 앞뒤 공백 하나 때문일 수도 있고, 한쪽은 숫자이고 다른 쪽은 문자이기 때문일 수도 있습니다. 그렇다고 모든 오류를 빈칸으로 바꾸면 실제로 없는 번호와 수식 실수까지 놓치게 됩니다.
검색 범위 확인 → 양쪽 비교키 정리 → 남은 실패 건 확인 순서로 해결해 봅니다. 가상 주문번호를 사용해 2026년 10월 27일 실제 구글시트에서 검증했습니다. 화면·실행 결과와 따라 하는 절차는 구글시트 기준입니다. Excel에서 이 예제 전체를 실행한 검증은 아직 하지 않았습니다.
먼저 결과를 보면
이번 실습에서는 정리 전 #N/A가 5건, 공백과 자료형을 맞춘 뒤에는 2건이었습니다.
| 사례 | 정리 전 | 정리 후 | 해야 할 일 |
|---|---|---|---|
| 찾는 번호 뒤에 일반 공백 | #N/A | 배송준비 | 찾는 값의 공백 정리 |
| 기준표 번호 앞에 일반 공백 | #N/A | 출고완료 | 기준표 쪽도 공백 정리 |
| 찾는 값은 문자 1004, 기준표는 숫자 1004 | #N/A | 취소 | 같은 식별자로 취급할 수 있는지 확인 후 자료형 통일 |
| 기준표에 없는 ORD-9999 | #N/A | #N/A | 원본 자료와 기준표 확인 |
| 001005를 1005로 입력 | #N/A | #N/A | 사라진 앞자리 0은 원본에서 확인 |
오류 2건을 남겨 둔 것이 이번 예제의 올바른 결과입니다. 없는 번호를 다른 번호에 억지로 맞추지는 않습니다.
1. VLOOKUP의 네 가지 입력부터 확인하기
기본 형태는 다음과 같습니다.
=VLOOKUP(찾는값,검색범위,가져올열번호,FALSE)
- 찾는 값은 검색 범위의 맨 왼쪽 열에서 찾습니다.
- 가져올 열 번호는 선택한 검색 범위 안에서 왼쪽부터 1, 2, 3으로 셉니다.
- 주문번호처럼 정확한 값을 찾을 때는 마지막 인수를
FALSE로 지정합니다. - 아래로 복사해도 기준표 범위가 움직이지 않도록
$A$2:$B$8처럼 고정합니다.
FALSE는 정확히 일치하는 값을 찾는 설정입니다. 생략하면 근사 일치가 기본이라 없는 번호에서도 다른 결과가 나올 수 있습니다. Google VLOOKUP 공식 안내
2. 오류가 나는 기준표와 찾는 값 준비하기
같은 파일에 조회기준, 조회실습 두 탭을 만듭니다. 아래 예제는 원본을 지우지 않고 옆에 비교키를 만들어 확인합니다.
조회기준 탭
A1은 주문번호 원본, B1은 처리상태로 적습니다. 아래의 ‘A열 입력값’을 표시된 셀에 그대로 입력하세요. =로 시작하는 값은 수식이며, 공백이나 문자 형식을 확실히 재현하기 위한 장치입니다.
| 행 | A열 입력값 | B열 처리상태 |
|---|---|---|
| 2 | ORD-1001 | 배송완료 |
| 3 | ORD-1002 | 배송준비 |
| 4 | =" ORD-1003" |
출고완료 |
| 5 | 1004 |
취소 |
| 6 | ="001005" |
접수 |
| 7 | ORD-1006 | 1차처리 |
| 8 | ORD-1006 | 재처리 |
A4 수식의 따옴표 바로 뒤에는 일반 공백 한 칸이 있습니다. A5는 숫자, A6은 앞자리 0이 있는 문자입니다. 구글시트는 직접 입력한 일반 공백을 정리할 수 있어, 이번 실습에서는 수식으로 공백을 만들었습니다. Google TRIM 안내
조회실습 탭
A1:F1에 사례 / 찾는 값 / 정리 전 / 비교키 / 정리 후 / 오류 안내를 적습니다. 먼저 A열과 B열만 입력합니다.
| 행 | A열 사례 | B열 입력값 |
|---|---|---|
| 2 | 정상 | ORD-1001 |
| 3 | 찾는 값 뒤 공백 | ="ORD-1002 " |
| 4 | 기준표 앞 공백 | ORD-1003 |
| 5 | 문자와 숫자 | ="1004" |
| 6 | 기준표에 없음 | ORD-9999 |
| 7 | 앞자리 0 보존 | ="001005" |
| 8 | 앞자리 0 유실 | 1005 |
| 9 | 기준표 중복 | ORD-1006 |
| 10 | 빈 입력 | 아무것도 넣지 않음 |
B3 수식은 닫는 따옴표 직전에 공백 한 칸이 있습니다.
조회실습의 C2에 아래 수식을 입력하고 C10까지 아래로 채웁니다.
=IF(B2="","",VLOOKUP(B2,'조회기준'!$A$2:$B$8,2,FALSE))
정리 전에는 C3·C4·C5·C6·C8이 #N/A입니다. B열이 빈 C10은 빈칸으로 남습니다.
3. 공백과 자료형은 양쪽을 함께 맞추기
찾는 값만 TRIM으로 정리하면 기준표에 붙은 공백은 그대로 남습니다. 기준표와 찾는 값에 같은 정리 규칙을 적용해야 합니다.
조회기준의 C열은 비워 두고, D1은 비교키, E1은 처리상태로 적습니다.
조회기준 D2에 입력한 뒤 D8까지 채웁니다.
=IF(A2="","",TRIM(A2&""))
조회기준 E2에는 아래 수식을 넣고 E8까지 채웁니다.
=B2
왼쪽 A:B는 원본이고, 오른쪽 D:E는 조회에 쓸 표입니다. 이 예제의 처리상태 B2:B8은 모두 채워져 있습니다.
이번에는 조회실습 D2에 아래 수식을 넣고 D10까지 채웁니다.
=IF(B2="","",TRIM(B2&""))
&""는 값을 문자로 다루게 하고, TRIM은 앞뒤 일반 공백을 정리합니다. 이번 주문번호는 내부 공백이 없는 코드라는 전제입니다. TRIM은 내부의 반복 공백도 줄이며 모든 특수 공백을 제거하지는 않습니다.
조회실습 E2에서는 정리된 기준표 D:E를 조회합니다. E10까지 채워 주세요.
=IF(D2="","",VLOOKUP(D2,'조회기준'!$D$2:$E$8,2,FALSE))
이제 E3은 배송준비, E4는 출고완료, E5는 취소로 바뀝니다. 공백 문제 2건과 숫자·문자 차이 1건이 해결된 것입니다.
숫자와 문자를 통일하는 방식은 업무 규칙에 따라 정해야 합니다. 이 예제에서는 숫자 1004와 문자 "1004"를 같은 주문번호로 취급합니다. 두 값을 구분해야 하는 업무라면 이 정리 규칙을 쓰면 안 됩니다. Microsoft도 조회값과 기준표의 자료형 차이, 앞뒤 공백을 #N/A의 점검 항목으로 안내합니다. Microsoft #N/A 오류 해결 안내
001005를 숫자로 바꾸면 안 되는 이유
E7의 001005는 접수가 나오지만, E8의 1005는 계속 #N/A입니다. 문자로 통일해도 이미 없어진 앞자리 0은 되살아나지 않습니다.
주문번호는 계산할 금액이 아니라 식별자입니다. 모든 번호에 VALUE를 적용하거나, 자릿수 규칙도 모른 채 0을 붙이지 마세요. 원본에서 올바른 번호를 확인한 뒤 입력해야 합니다. 실제 6자리 고정 코드라는 규칙이 있다면 그때 별도 정규화 방법을 정합니다.
4. IFNA는 마지막에 안내 문구를 붙이는 용도
조회실습 F2에 아래 수식을 넣고 F10까지 채웁니다.
=IFNA(E2,"조회 실패")
F6과 F8은 ‘조회 실패’로 표시됩니다. IFNA가 원인을 고친 것은 아닙니다. E열의 #N/A를 사람이 읽기 쉬운 문구로 바꾼 것입니다. IFNA는 #N/A일 때 지정한 값을 반환합니다. Google IFNA 공식 안내
‘미등록’이라고 단정하지 않고 ‘조회 실패’로 적은 이유도 있습니다. 검색 범위가 틀렸거나 반환할 셀 자체에 #N/A가 있어도 같은 표시가 생길 수 있기 때문입니다. 원래 결과인 E열을 함께 남겨 두면 원인 확인에 도움이 됩니다.
IFERROR로 모든 오류를 가리면 생기는 문제
아래 수식은 일부러 잘못 만들었습니다. A:B는 2열인데 가져올 열 번호를 3으로 지정했습니다.
=IFNA(VLOOKUP("ORD-1001",'조회기준'!$A$2:$B$8,3,FALSE),"조회 실패")
실제 결과는 #REF!였습니다. IFNA는 이 오류를 가리지 않았습니다. 같은 수식의 IFNA를 IFERROR로 바꾸면 ‘조회 실패’가 표시되어 잘못된 열 번호라는 문제까지 가려졌습니다.
=IFERROR(VLOOKUP("ORD-1001",'조회기준'!$A$2:$B$8,3,FALSE),"조회 실패")
범위가 잘못됐을 때는 범위를 고쳐야 합니다. 오류 처리 함수를 붙이는 작업은 그다음입니다.
5. 오류 없이 다른 값이 나오는 경우도 확인하기
조회실습 E9에는 ORD-1006의 1차처리만 나옵니다. 조회기준에는 같은 번호의 재처리 기록도 있지만, VLOOKUP은 첫 번째 일치 결과를 반환합니다. 오류가 없다고 원하는 기록을 가져왔다는 뜻은 아닙니다.
어떤 기록을 선택할지 먼저 정하거나 비교 기준을 더해야 합니다. 주문번호 중복의 의미는 2편 — 주문번호가 같아도 바로 지우면 안 되는 이유에서 이어서 확인할 수 있습니다.
또 하나는 FALSE를 빼는 경우입니다. 별도 실험으로 조회실습 H1:I4에 아래 숫자 기준표를 넣었습니다.
| 숫자 코드 | 상태 |
|---|---|
| 1000 | 준비 |
| 2000 | 출고 |
| 3000 | 완료 |
=VLOOKUP(1500,$H$2:$I$4,2)
표에 1500은 없지만 결과는 준비였습니다. 오름차순 표에서 근사 일치로 1000의 결과를 가져온 것입니다. 마지막에 FALSE를 넣으면 정확한 1500이 없으므로 #N/A가 나옵니다.
=VLOOKUP(1500,$H$2:$I$4,2,FALSE)
근사 일치는 구간별 요율처럼 의도한 용도에서 쓸 수 있습니다. 이번처럼 정확한 주문번호를 찾는 작업에서는 FALSE를 지정합니다.
추가 실험에서는 검색 범위를 B열부터 잡거나, 원본의 첫 데이터 행을 범위에서 빼도 #N/A가 재현됐습니다. 표에 번호가 보이더라도 수식이 실제로 검색하는 범위에 들어 있는지 확인해야 합니다.
6. 오류 건수와 예외를 검산하기
조회실습의 빈 셀에 아래 수식을 넣어 정리 전후의 오류 건수를 비교할 수 있습니다.
=SUMPRODUCT(--ISNA(C2:C10))
기대값은 5입니다. 정리 후 E열은 아래처럼 확인하며 기대값은 2입니다.
=SUMPRODUCT(--ISNA(E2:E10))
공백 길이는 각각 9, 문자·숫자 확인은 TRUE, 001005의 정리 후 길이는 6이었습니다. 빈 입력은 빈칸으로 유지됐고, 없는 번호 ORD-9999를 ORD-1002로 바꾸면 결과가 배송준비로 갱신되는 것도 확인했습니다. 변경 후에는 원래 번호로 되돌렸습니다.
내 자료에 적용할 때 확인할 순서
- 원본을 남겨 두고 작업용 사본을 만듭니다.
- 마지막 인수가 FALSE인지, 검색 열이 범위 맨 왼쪽인지 확인합니다.
- 범위에 첫 행과 마지막 행이 포함됐는지, 복사할 때 범위가 밀리지 않는지 확인합니다.
- 찾는 값과 기준표 양쪽의 공백·자료형을 점검합니다. 앞자리 0의 의미를 보존합니다.
- 중복 키와 반환할 값의 빈칸·오류도 확인합니다.
- 남은 실패 건을 원본과 대조한 뒤, 필요하면 IFNA로 안내 문구를 붙입니다.
실제 데이터가 늘면 기준표의 $A$2:$B$8·$D$2:$E$8 끝 행과 비교키 수식을 채우는 행도 함께 늘려 주세요. 이 예제는 대문자·숫자·하이픈 코드로 구성했습니다. 대소문자를 구분해야 하거나 *·?가 번호에 들어 있으면 별도 일치 규칙을 검토해야 합니다.
이 글의 절차·실행 결과·캡처는 구글시트 기준입니다. VLOOKUP 등 같은 이름의 함수가 Excel에도 있지만, 이 예제 전체의 Excel 실행은 아직 검증하지 않았습니다. Excel에서 그대로 작동한다고 판단하지 말고, 이 글을 따라 하려면 구글시트에 별도 실습용 문서를 만들어 주세요.
관련 글: 1편 — 두 시트를 비교해서 없는 값 찾기
판매자 인터뷰 모집을 잠시 쉬어갑니다
참여 안내와 개인정보 처리 절차를 점검하고 있어 현재 신청과 응답을 받지 않습니다. 재개 여부는 이 블로그에 안내하겠습니다.
지금은 이메일로도 참여 신청이나 연락처·고객 정보·업무 자료를 보내지 말아 주세요. 무료 샘플은 인터뷰 참여와 관계없이 계속 이용할 수 있습니다.
댓글
댓글 쓰기