VLOOKUP #N/A 원인을 AI 로 좁히고 셀에서 확인하기

20행 중 3행만 #N/A 가 나옵니다. 거래처 코드는 눈으로 보면 단가표에 분명히 있습니다. AI 에 「왜 안 되죠」라고만 물으면 흔한 원인 목록만 돌아옵니다. 수식·몇 행의 값·셀 서식을 함께 주고 셀에 넣어 볼 수식까지 받습니다.

[1] 자료를 정리합니다. 시트 「주문」의 A열은 주문코드, B열은 단가이고 단가표는 E2:F21 에 있습니다. B2 의 수식을 B21 까지 끌어 복사했습니다. 정상 행과 문제 행을 섞어 몇 줄만 뽑습니다.

단가표 쪽에서는 E3=A-102, E5=A-104, E6=B-201, E17=1023(숫자, 오른쪽 정렬)입니다. 정렬은 숫자인지 텍스트인지 보여 주는 단서입니다. 코드 뒤 공백은 화면에 안 보여 줄 수 없습니다.

[2] 프롬프트를 만듭니다. AI 는 파일을 볼 수 없다는 것, 원인은 가능성 순으로 4개까지, 원인마다 확인 수식 한 줄이라는 형식을 못 박았습니다. 맥락이 없는 동료에게 일을 맡기듯 형식과 제약을 구체적으로 적으라는 Anthropic 프롬프트 가이드의 원칙을 따른 것입니다.

엑셀 VLOOKUP 이 #N/A 를 돌려줍니다. 아래 <sheet> 안의 정보만 근거로 진단해 주세요. 당신은 이 파일을 볼 수 없다는 점을 전제로 하세요. 규칙:- 가능한 원인을 가능성이 높은 순서로 최대 4개 적습니다. 각 원인마다 "어느 행에 해당하는지"를 표시된 정보에서 짚고, 짚을 수 없으면 "확인 필요"라고 씁니다.- 원인마다 빈 셀에 넣어 확인할 수식을 한 줄 줍니다(영문 함수명, 2행 기준, 아래로 복사할 수 있게). 어떤 결과가 나오면 그 원인이 맞는지도 한 줄로 적습니다.- 데이터를 보지 못했으므로 추측과 확정을 구분합니다. 확정하는 문장을 쓰지 않습니다.- 마지막에 모든 원인이 고쳐졌는지 세는 수식을 한 줄 줍니다. COUNTIF 대신 오류 값을 직접 세는 방식으로.- 그 밖의 설명은 쓰지 않습니다. <sheet>{수식 · 시트 구조 · 행별 표시값/서식/정렬/결과 · 단가표 일부}</sheet>

[3] { } 자리에 [1] 의 내용을 붙여 실행했습니다. 아래는 첫 번째 결과에서 설명 문장을 줄인 것입니다.

**원인 1. 조회 범위가 아래로 밀림 (E2:F21이 상대 참조일 가능성)**- 해당 행: 10행으로 보입니다. A-104는 E5에 있는데, 10행 수식의 범위는 E10:F29로 밀렸다면 E5가 범위 밖입니다. 4행의 B-201(E6)은 밀린 범위(E4:F23) 안에 있어서 이 원인으로는 설명되지 않습니다. …- 확인 수식(빈 셀, 2행 기준): `=IFERROR(MATCH(A2,$E$2:$E$21,0),"없음")`- 해석: B열이 #N/A인 행에서 숫자(표 안의 위치)가 나오면, 범위를 고정했을 때는 찾아지므로 이 원인일 가능성이 큽니다. "없음"이면 이 원인만으로는 설명되지 않습니다.**원인 2. 텍스트와 숫자 형식 불일치**- 해당 행: 13행으로 보입니다. A13은 서식이 텍스트인 "1023"이고, E17은 숫자(오른쪽 정렬)입니다. …- 확인 수식: `=IFERROR(AND(ISERROR(MATCH(A2,$E$2:$E$21,0)),ISNUMBER(MATCH(VALUE(A2),$E$2:$E$21,0))),FALSE)`**원인 3. 보이지 않는 공백이나 제어문자 (끝 공백, 줄바꿈, CHAR(160) 등)**- 해당 행: 4행(B-201, E6에는 있는데 #N/A)이 의심되지만, 화면만으로는 A4와 E6 중 어느 쪽 문제인지 확인 필요입니다.- 확인 수식: `=LEN(A2)-LEN(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))`. E열 쪽도 같은 식에 E2를 넣어 확인합니다.…**전체 확인 (0이면 오류가 남아 있지 않음):** `=SUMPRODUCT(--ISERROR(B2:B21))`

세 행이 서로 다른 원인으로 갈렸습니다. 10행은 범위가 밀림, 13행은 텍스트 대 숫자, 4행은 보이지 않는 문자입니다. 두 번째 실행도 같은 세 원인을 같은 행에 붙였고, 확인 수식만 달랐습니다. 이쪽은 =IF(ISNA(B2),…,"") 로 감싸 #N/A 인 행에서만 값을 내게 했습니다. 두 번 모두 네 번째 원인(단가표에 코드가 없음)은 짚을 행이 없다고 적었습니다.

Google 도 Sheets 의 Gemini 가 수식 실패 이유(예: 날짜가 텍스트 형식)를 설명한다고 안내합니다. 어느 도구든 「왜」는 짐작이고, 맞는지는 시트가 답합니다.

[4] AI 가 준 수식을 시트에 넣어 확인합니다. 빈 열 H 에 =LEN(A2)-LEN(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))) 를 넣고 21행까지 끌어 내립니다. I 열에는 =IFERROR(MATCH(A2,$E$2:$E$21,0),"없음") 를 넣습니다. 엑셀이 아니라 이 규칙을 계산하는 스크립트로 같은 모형을 돌린 예상값은 다음과 같습니다.

I 에 위치(숫자)가 나오는데 B 가 #N/A 인 행(10행)만 범위 문제입니다. 10행의 B 셀을 눌러 수식을 보면 E10:F29 로 바뀌어 있습니다. 13행은 =ISNUMBER(A13) 이 FALSE 이고 =ISNUMBER(E17) 이 TRUE 면 확정입니다.

[5] 세 가지를 고칩니다.

• 4행: 뒤 공백을 걷습니다. 아래 B2 수식이 텍스트 칸의 공백을 걷습니다. CHAR(160) 이 섞였다면 SUBSTITUTE 로 먼저 바꿉니다.
• 13행: 셀을 숫자로 바꿉니다. 단가표 쪽이 텍스트라면 반대로 맞춥니다.
• 범위: B2 를 =VLOOKUP(IF(ISNUMBER(A2),A2,TRIM(A2)),$E$2:$F$21,2,FALSE) 로 바꿔 다시 끌어 내립니다. $ 가 범위를 고정합니다. 여기에 TRIM(A2) 만 쓰면 안 됩니다. TRIM 은 항상 텍스트를 돌려주므로 13행을 숫자로 고친 뒤에는 1025 같은 숫자 코드가 텍스트가 되어 단가표의 숫자 키와 맞지 않습니다. IF(ISNUMBER(…)) 는 숫자는 그대로 두고 텍스트만 다듬습니다.

이 샘플에 파이썬 formulas 패키지(엑셀 수식을 그대로 계산하는 라이브러리이지만 엑셀 자체는 아닙니다)로 계산해 보았습니다. 원래 B2 는 #N/A 3개(4·10·13행)였습니다. 13행을 숫자로 고친 뒤 TRIM(A2) 만 쓰면 다시 3개(13·16·17행, 모두 숫자 코드)가 나왔고, 위 수식은 0개였습니다.

[6] 마지막으로 남은 #N/A 를 셉니다. AI 는 =SUMPRODUCT(--ISERROR(B2:B21)) 를 줬는데, ISERROR 는 #VALUE! · #REF! 까지 세므로 #N/A 만 보려면 이렇게 바꿉니다.

=SUMPRODUCT(--ISNA(B2:B21))

고치기 전 이 값은 3 이고, 위 세 가지를 모두 고치면 0 이어야 합니다(같은 계산에 [5] 의 수식을 쓰면 3 → 0). 0 이 아니면 새 원인이 있는 것이니, 남은 행으로 [2] 를 다시 돌립니다.

빗나가는 곳

• 뒤 공백 행은 AI 가 「추측」으로만 썼습니다. 화면에 안 보이는 것은 짚지 못하므로 [4] 의 LEN 차이로 직접 확인해야 합니다.
• 확인 수식이 실행마다 달랐습니다. 첫 번째는 원인마다 열 하나씩, 두 번째는 IF(ISNA(B2),…) 로 오류 행에서만 값을 내는 방식이라 H~K 열을 한꺼번에 깔아 볼 수 있습니다. 어느 쪽이든 수식 하나가 한 원인만 잡는지 [4] 처럼 알려진 행으로 먼저 시험하세요.
• 네 번째 원인은 이 샘플에서 짚을 행이 없는 칸이었습니다. 세 가지를 고친 뒤에도 #N/A 가 남을 때 보는 칸으로 쓰면 됩니다.
• 단가표 쪽 코드에 공백이 있는 경우는 샘플에 넣지 않아 시험하지 못했습니다.
• 실제 거래처명·단가는 가상값으로 바꿔 붙이세요.