AI 해결 노트 · 2026-09-18 · 실측 2026-09-18

VLOOKUP 조회가 어긋나는 자리 여섯 군데 — #N/A·#REF!·조용히 틀린 값

한 줄로

품목코드가 표에 분명히 있는데 #N/A가 뜹니다. 더미 단가표 6줄로 흔한 원인 여섯 가지를 따로 내 봤더니 값 끝의 공백·숫자와 글자 불일치·찾는 열의 위치·복사하면서 밀린 범위에서 #N/A가, 열 번호를 잘못 넣은 자리에서 #REF!가, 네 번째 인수를 뺀 자리에서는 오류 없이 엉뚱한 단가 900원이 나왔습니다. 오류가 안 뜬 이 마지막 하나가 제일 위험합니다.

증상

• 품목코드를 눈으로 보면 표에 있는데 #N/A가 뜬다
• 어떤 줄은 잘 나오고 어떤 줄만 #N/A다
• 수식을 아래로 복사했더니 중간 몇 줄만 #N/A로 바뀌었다
• 오류는 없는데 단가가 다른 품목 것으로 들어와 있다

실측 환경

더미 표

단가표 시트에 여섯 줄을 넣었습니다. 품목코드가 A열, 품명이 B열, 단가가 C열입니다.

사번 사례를 위해 사번표 시트도 만들었습니다. A열을 텍스트 서식으로 지정한 뒤 1001·1002·1003을 넣었으니, 엑셀은 이 사번들을 글자로 들고 있습니다.

여섯 가지를 한 화면에

⑥은 수식을 복사해야 나오는 것이라 아래에 따로 뒀습니다.

① 찾는 값 뒤에 공백 한 칸

A-102 를 찾으면 #N/A입니다. 화면으로는 A-102와 구별되지 않습니다. 공백을 어디에 붙였는지에 따라 고치는 방법도 달라져, 네 가지를 따로 넣어 봤습니다.

앞뒤 공백은 TRIM으로 해결됩니다. 가운데 공백은 다릅니다. TRIM이 가운데를 어떻게 다루는지 네 가지를 더 넣어 봤습니다.

TRIM은 앞뒤 공백을 없애고 가운데 연속 공백은 한 칸으로 줄입니다. 한 칸짜리 가운데 공백은 그대로 남기 때문에 TRIM으로는 #N/A가 풀리지 않습니다. 가운데 공백까지 없애려면 SUBSTITUTE로 공백을 전부 지워야 합니다.

=VLOOKUP(SUBSTITUTE(A2," ",""),단가표!$A$2:$C$7,3,FALSE)

줄바꿈이 붙은 경우도 넣어 봤습니다. A-102 뒤에 줄바꿈(CHAR 10)을 붙였더니 TRIM으로는 지워지지 않았고, TRIM(CLEAN(…))을 씌워야 5글자가 됐습니다. 웹 화면이나 다른 시스템에서 복사해 온 값에서 볼 수 있는 모양입니다.

찾는 값이 A2에 있다면 이 세 줄로 진단합니다.

=LEN(A2)                    → 6
=LEN(TRIM(CLEAN(A2)))       → 5
=CODE(RIGHT(A2,1))          → 32

두 LEN이 다르면 TRIM이나 CLEAN이 걷어 내거나 줄여 놓은 글자가 있다는 뜻입니다. 위 표의 「가운데 2칸」처럼 글자가 지워진 게 아니라 줄어든 경우도 여기 걸립니다. CODE가 32면 맨 끝 글자가 공백, 10이면 줄바꿈입니다.

② 한쪽은 숫자, 한쪽은 글자

사번표의 1002는 텍스트 서식으로 넣은 글자이고, 주문표에 친 1002는 숫자입니다. 눈으로는 같은 1002인데 #N/A입니다. 반대 방향도 돌려 봤습니다.

어느 쪽이 글자인지 보려면 찾는 값과 표의 값 양쪽에 ISNUMBER와 ISTEXT를 넣어 자료형을 확인합니다. 방향에 따라 고치는 함수가 갈립니다. 표가 글자면 찾는 값을 TEXT(…,"0")으로, 표가 숫자면 찾는 값을 VALUE(…)로 맞춥니다.

앞자리 0이 있는 사번은 형만 맞춘다고 되지 않습니다. 표에 0123·0456·1002를 글자로 넣고, 앞의 0이 없는 숫자 123으로 찾아 봤습니다.

TEXT(…,"0")은 이미 사라진 앞자리 0을 되살려 주지 않습니다. 자릿수가 정해져 있는 코드라면 "0000"처럼 자릿수를 적어 줘야 맞습니다. 애초에 이런 코드는 입력하는 칸을 텍스트 서식으로 잡아 두는 편이 낫습니다.

③ 찾는 열이 범위의 첫 열이 아님

VLOOKUP은 지정한 범위의 맨 왼쪽 열에서만 찾습니다. 범위를 단가표!$B$2:$C$7로 잡으면 맨 왼쪽은 품명(B열)이라, A-102라는 코드를 품명 목록에서 찾다가 #N/A를 냅니다. 범위를 A열부터 잡고 열 번호를 3으로 고치면 3500이 나옵니다.

표에서 코드가 오른쪽에, 찾아올 값이 왼쪽에 있는 배치라면, 그 범위를 그대로 지정한 VLOOKUP으로는 왼쪽 값을 가져올 수 없습니다. INDEX·MATCH를 쓰거나, 아래 XLOOKUP 쪽을 보십시오.

④ 열 번호가 범위 밖 — #N/A가 아니라 다른 오류

여기서만 #REF!가 나왔습니다. 오류 문구가 다르므로 원인을 가릴 때 쓸 수 있습니다. 같은 범위(2열짜리)에 열 번호만 바꿔 일곱 번 넣어 봤습니다.

#REF!를 봤다면 범위 안의 열 개수부터 세십시오. 열 번호는 1 이상이고 범위의 열 개수 이하여야 합니다. 0이나 음수를 넣으면 #VALUE!로 갈립니다.

처음 만들 때 맞던 수식이 나중에 #REF!로 바뀌는 일도 있습니다. 코드·품명·규격·단가 4열짜리 표에 =VLOOKUP("A-102",A2:D4,4,FALSE)를 넣어 3500을 받은 뒤, 규격 열(C열)을 지워 봤습니다.

지우기 전: =VLOOKUP("A-102",A2:D4,4,FALSE)  → 3500
C열 지운 뒤: =VLOOKUP("A-102",A2:C4,4,FALSE) → #REF!

범위는 3열로 줄었는데 열 번호 4는 그대로 남아 있습니다. 표에서 열을 지울 때는 그 표를 참조하는 수식의 열 번호도 같이 보셔야 합니다.

⑤ 네 번째 인수를 빼면 오류 없이 다른 품목 단가가 들어옵니다

=VLOOKUP(B6,단가표!$A$2:$C$7,3) 처럼 마지막 FALSE를 빼면 근사 일치로 동작합니다. 표에 없는 B-203을 찾았는데 오류 없이 900이 나왔습니다. B-202(집게) 단가입니다. 없는 코드를 조회했는데 앞 줄 품목의 단가가 조용히 들어앉은 셈입니다.

근사 일치는 표가 오름차순으로 정렬돼 있다는 전제 위에서 돕니다. 같은 값 여덟 개를 정렬된 표와 뒤죽박죽 표에 각각 넣어 비교해 봤습니다.

뒤죽박죽 표에서는 여덟 줄 가운데 다섯 줄이 틀렸습니다. 그중 C-301은 표에 멀쩡히 있는 코드인데 68000 대신 3500(노트 단가)이 들어왔습니다. 오류 표시가 없으니 검산하지 않으면 그대로 나갑니다. 정확히 맞는 것만 찾는 일이라면 네 번째 인수에 FALSE나 0을 반드시 적으십시오.

⑥ 범위를 $로 고정하지 않고 복사

첫 줄에 =VLOOKUP(A11,단가표!A2:C7,3,FALSE)를 넣고 아래 네 줄로 복사했습니다. 붙여넣은 줄마다 범위가 한 칸씩 내려가, 다섯 줄 중 두 줄이 #N/A가 됐습니다.

밀려난 범위에서 우연히 찾는 값이 아직 안에 남아 있으면 정답이 나오고, 범위 위쪽으로 빠져나간 값은 #N/A가 됩니다. 세 줄이 멀쩡하니 표를 대충 훑으면 넘어가기 쉽습니다. #N/A 난 칸을 눌러 수식 줄의 범위가 첫 줄과 같은지 보는 것이 제일 빠른 확인입니다.

같은 잘못된 수식인데 아무 일도 안 일어나는 경우도 확인해 뒀습니다. 주문 순서를 단가표 순서와 똑같이 A-101부터 놓고 범위를 고정하지 않은 채 복사했더니, 다섯 줄 모두 정답이 나왔습니다.

범위가 줄마다 밀렸는데도 찾는 값이 그때마다 범위 첫 줄에 있어 다 맞았습니다. 값이 맞게 나왔다고 수식이 맞는 것은 아니라는 뜻입니다. 주문 순서가 바뀌어 찾는 코드가 밀려난 범위 밖으로 빠지면 그때 #N/A가 납니다.

두 번째 인수는 $A$2:$C$7처럼 행·열을 모두 고정합니다. $가 어디에 붙느냐에 따라 무엇이 고정되는지는 수식을 복사해도 기준 셀이 안 바뀌게에 값으로 비교해 두었습니다.

#N/A가 났을 때 무엇부터 보나

찾는 값이 A33에 있다고 할 때, 여덟 줄을 옆 칸에 넣어 봤습니다. A-102 (뒤 공백)를 넣은 결과입니다.

=COUNTIF(표!$A$2:$A$7,A33)              → 0
=COUNTIF(표!$A$2:$A$7,"*"&TRIM(A33)&"*") → 1
=LEN(A33)                                → 6
=LEN(TRIM(CLEAN(A33)))                   → 5
=CODE(RIGHT(A33,1))                      → 32
=EXACT(A33,표!$A$3)                      → FALSE
=ISNUMBER(A33)                           → FALSE
=IFNA(VLOOKUP(A33,표!$A$2:$B$7,2,FALSE),"코드없음") → 코드없음

뒤 공백 사례에서는 정확히 센 COUNTIF가 0, *로 감싼 부분 일치가 1이었습니다. 두 값이 이렇게 갈리면 표에 비슷한 값이 있다는 신호는 되지만, 부분 일치는 표 안의 더 긴 다른 코드에도 걸리므로 이것만으로 원인을 확정할 수는 없습니다. 순서로 정리하면 이렇습니다.

1. COUNTIF로 표 안에 몇 개 있는지 센다. 0이면 찾는 값 쪽을 먼저 의심한다.
2. LEN과 LEN(TRIM(CLEAN(…)))을 비교해 눈에 안 보이는 글자를 확인한다.
3. 찾는 값과 표의 값 양쪽에 ISNUMBER·ISTEXT를 넣어 자료형이 같은지 본다. 1번 결과와 상관없이 이 단계는 거칩니다.
4. 수식 줄에서 범위의 맨 왼쪽 열이 찾는 값의 열인지, 열 번호가 1 이상이고 범위의 열 개수 이하인지 본다.

보고서에 #N/A를 그대로 내보내지 않으려면 IFNA로 감쌉니다. 다만 원인을 고치기 전에 감싸면 틀린 줄이 「코드없음」으로 바뀔 뿐이라, 감싸는 것은 원인을 다 본 뒤에 하십시오.

XLOOKUP이 이 버전에 있었습니다

=XLOOKUP("A-102",단가표!$A$2:$A$7,단가표!$C$2:$C$7,"없음")을 넣었더니 3500이 나왔습니다. 이 Excel 16.0 빌드 20326.0에는 XLOOKUP이 있습니다. 이 수식은 찾는 열($A$2:$A$7)과 가져올 열($C$2:$C$7)을 따로 적습니다. 열 번호 인수가 아예 없고, 일치 방식도 따로 지정하지 않았습니다. ③④⑤가 생기던 인수 두 개가 이 모양에는 없습니다.

일치 방식을 적지 않은 채 없는 코드 B-203을 넣었더니 「없음」이 나왔습니다. 같은 값을 VLOOKUP에 네 번째 인수 없이 넣었을 때 900이 나온 것과 다릅니다. 네 번째 자리에 적어 둔 「없음」이 그대로 나오므로 IFNA를 따로 씌우지 않아도 됩니다.

고쳐 주지 않는 것도 있습니다. ①②를 XLOOKUP으로 다시 돌려 봤습니다.

값 자체가 어긋나 있는 ①②는 함수를 바꿔도 그대로 남습니다. 값 쪽을 손봐야 합니다.

실패한 것

• 첫 PNG를 뽑을 때 wb.ExportAsFixedFormat으로 내보냈더니 시험 시트가 아니라 첫 시트인 단가표가 PDF로 나왔습니다. 시트를 Activate() 한 뒤 ws.ExportAsFixedFormat으로 바꿔 다시 뽑았습니다. 렌더 로그는 스크립트가 끝날 때 한 번에 쓰이고 다시 돌릴 때마다 덮어쓰므로, 02_png_렌더로그.txt에 남은 것은 고친 뒤의 출력뿐입니다.
• 초안을 쓴 뒤 GPT에 사실 대조를 돌렸더니 「TRIM은 가운데 공백을 손대지 않는다」, 「앞자리 0은 글자 쪽으로 맞추면 된다」, 「열을 중간에 지우면 #REF!가 난다」 세 문장이 돌려 보지 않은 추측으로 잡혔습니다. 세 가지를 실제로 돌린 결과(04_보탬시험2.txt) 앞의 두 문장을 고쳤습니다. TRIM은 가운데 연속 공백을 한 칸으로 줄였고, 앞자리 0은 TEXT(…,"0")으로는 못 맞추고 자릿수를 적은 TEXT(…,"0000")으로만 맞았습니다.
• ⑥ 사례에서 주문 순서를 단가표 순서와 같게 놓으면 어떻게 되는지도 같은 대조에서 지적받아 따로 돌렸습니다(04_보탬시험2.txt). 다섯 줄 모두 정답이 나와, 잘못된 수식이 겉으로 드러나지 않는 것을 확인했습니다.

확인하지 않은 것

• Excel 16.0 빌드 20326.0 한 가지에서만 돌렸습니다. 2016·2019나 웹용 엑셀, 구글 시트에서는 확인하지 않았습니다.
• 근사 일치가 정렬이 어긋난 표에서 어느 값을 집는지는 위 여덟 줄에서 관측한 결과이며, 내부 탐색 방식이 문서에 어떻게 적혀 있는지는 찾아보지 않았습니다.
• 숫자 코드에 대한 근사 일치(구간 조회, 예를 들어 금액 구간별 수수료율)는 넣어 보지 않았습니다. 이 글의 ⑤는 문자 코드 기준입니다.
• XLOOKUP이 없는 버전에서 이 파일을 열었을 때 어떻게 표시되는지는 확인하지 않았습니다. 다른 사람과 주고받을 파일이라면 받는 쪽 버전을 먼저 물어보십시오.
• INDEX·MATCH 조합, 다른 파일을 참조하는 VLOOKUP, 파워 쿼리 병합은 이번에 다루지 않았습니다.
• 표에 같은 코드가 두 줄 이상 있을 때 어느 줄을 가져오는지는 시험하지 않았습니다.
• 전각 공백이나 한글 자모가 섞인 코드는 넣어 보지 않았습니다. 시험한 눈에 안 보이는 글자는 공백(코드 32)과 줄바꿈뿐입니다.

함께 보기

• 수식을 복사해도 기준 셀이 안 바뀌게 — $ 절대참조
• 엑셀 두 명단 비교 — 공통·한쪽에만 있는 항목 찾기
• 엑셀 표 정리 순서 — 병합 셀·빈 행·문자 날짜 고치기
• 엑셀 수식이 계산되지 않을 때