엑셀 vlookup #N/A: 값이 같아 보여도 공백 하나면 안 잡힙니다

눈으로 보면 같은 값인데 #N/A가 뜹니다.

찾을 값도 있고 범위도 맞고 열 번호도 맞습니다. 그런데 안 잡힙니다. 수식이 틀린 줄 알고 고치는데, 대부분은 수식이 맞고 값이 다릅니다.

이 글은 엑셀 데이터 정리 여덟 단계의 세 번째 단계를 조회 함수 쪽에서 다시 본 것입니다.

엑셀 vlookup #N/A는 값이 다를 때 뜹니다

같아 보이는 두 값이 다른 경우는 다섯 가지입니다.

① 앞뒤 공백. 다운로드한 파일에서 가장 흔합니다. A123과 A123은 다른 값입니다. ② 가운데 이중 공백. 사람이 친 이름에서 자주 나옵니다. 주식회사 한국처럼 두 칸이 들어갑니다. ③ 숫자처럼 보이는 텍스트. 한쪽은 숫자 12345, 다른 쪽은 텍스트 "12345"입니다. 오른쪽 정렬인지 왼쪽 정렬인지로 대개 구분됩니다. ④ 눈에 안 보이는 문자. 웹에서 복사하면 줄바꿈이나 비표준 공백이 딸려옵니다. ⑤ 전각과 반각. ABC와 ＡＢＣ, 하이픈과 대시가 섞입니다.

엑셀 vlookup 함수는 이 다섯을 전부 다른 값으로 봅니다. 사람 눈에만 같습니다.

무엇이 다른지 세 수식으로 확인합니다

고치기 전에 원인을 봅니다. 빈 열에 셋을 넣습니다.

=LEN(A2)&" / "&LEN(TRIM(A2))     길이가 다르면 공백이 있습니다
=EXACT(A2,B2)                     FALSE 면 두 값이 실제로 다릅니다
=CODE(RIGHT(A2,1))                160 이면 비표준 공백입니다

첫 줄이 대부분을 잡습니다. A123이면 5와 4가 나옵니다. 세 번째는 웹에서 복사한 표를 다룰 때만 필요한데, 한 번 걸리면 원인을 못 찾고 반나절을 씁니다.

=CODE(RIGHT(A2,1))이 32면 보통 공백, 160이면 비표준 공백, 10이면 줄바꿈입니다.

엑셀 공백 제거는 TRIM 하나로 끝나지 않습니다

=TRIM(A2)는 앞뒤 공백을 지우고 가운데 이중 공백을 한 칸으로 줄입니다. 여기까지가 TRIM이 하는 일입니다.

나머지는 따로 처리합니다.

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

안쪽부터 읽습니다. SUBSTITUTE가 비표준 공백을 보통 공백으로 바꾸고, CLEAN이 줄바꿈 같은 제어 문자를 지우고, TRIM이 남은 공백을 정리합니다. 엑셀 공백 제거는 이 세 겹으로 거의 다 잡힙니다.

숫자와 텍스트가 섞인 경우는 다릅니다. 조회하는 쪽을 =VALUE(A2)로 숫자로 바꾸거나, 반대로 원본을 =A2&""로 텍스트로 맞춥니다. 어느 쪽으로 맞출지는 원본이 매달 어떤 형태로 오는지에 따라 정합니다.

정제한 값은 원본 옆에 새 열로 둡니다

원본 열을 직접 고치면 나중에 못 되돌립니다.

주문번호_정제 같은 열을 만들어 위 수식을 걸고, 조회는 그 열로 합니다. 양쪽 표에 같은 방식으로 정제 열을 하나씩 두면 조회가 붙습니다.

이렇게 두면 두 가지가 좋아집니다. 원본이 그대로 남아서 나중에 원자료와 대조할 수 있고, 다음 달 파일에도 같은 열을 그대로 복사해 쓸 수 있습니다.

안 잡히는 줄이 몇 개인지 세어 둡니다

=COUNTIF(조회범위,정제값)이 0인 줄이 안 잡히는 줄입니다. 이 개수를 매달 기록해 둡니다.

숫자가 갑자기 늘면 원본 형식이 바뀐 것입니다. 채널에서 파일 양식을 고쳤거나 새 담당자가 다른 방식으로 입력하기 시작한 경우입니다. 안 잡히는 줄을 세어 두지 않으면 이걸 몇 달 뒤에야 알게 됩니다.

중복 정리에서 기준 열을 정할 때도 같은 정제를 먼저 걸어야 합니다. 공백이 남아 있으면 같은 건이 다른 건으로 남습니다.

매번 TRIM을 거는 대신 정제를 앞으로 옮깁니다

여기까지 하면 조회는 붙습니다. 그런데 다음 달 파일에도 같은 작업을 다시 합니다.

정제 수식을 시트에 걸어두는 방식은 파일이 바뀔 때마다 손이 갑니다. 파워 쿼리에서 파일을 읽을 때 공백 다듬기와 형식 변환을 단계로 저장해 두면, 새 파일을 넣고 새로 고침만 하면 정제된 표가 나옵니다.

엑셀 자동화는 이 순서로 붙습니다. 수식을 자동화하는 쪽이 아니고, 정제를 앞 단계로 옮겨서 수식이 필요 없게 만드는 쪽입니다.

거래처명 앞에 공백 하나가 붙으면 계정별 집계가 두 줄로 갈라지듯, 주문번호 뒤 공백 하나면 조회가 통째로 실패합니다. 둘 다 원본을 받는 단계에서 한 번 정리하면 뒤에서 안 터집니다.

자주 받는 질문

Q. VLOOKUP 대신 XLOOKUP을 쓰면 해결되나요

A. 안 됩니다. XLOOKUP도 값이 다르면 못 찾습니다. 다만 못 찾았을 때 반환할 값을 인수로 넣을 수 있어서 #N/A 대신 "미매칭"처럼 표시할 수 있고, 그러면 몇 건이 안 잡혔는지 세기 쉬워집니다.

Q. 엑셀 trim을 걸었는데도 #N/A가 뜹니다

A. 비표준 공백일 가능성이 높습니다. TRIM은 CHAR(32)만 지우고 웹에서 딸려온 CHAR(160)은 그대로 둡니다. =CODE(RIGHT(A2,1))로 확인하고 SUBSTITUTE로 먼저 바꾼 뒤 TRIM을 겁니다.

Q. 대소문자 때문에 안 잡히기도 하나요

A. VLOOKUP은 대소문자를 구분하지 않아서 이 경우는 잡힙니다. 다만 EXACT나 일부 조건 수식은 구분하므로, 검증할 때 EXACT가 FALSE라고 해서 VLOOKUP도 실패한다고 단정하지 않습니다.

Q. 원본을 고치지 말라는데 열이 계속 늘어납니다

A. 정제 열은 계산 시트에 두고 원본 시트에는 두지 않습니다. 원본은 받은 그대로, 계산은 별도 시트에서 합니다. 이렇게 나눠두면 다음 달에 원본만 갈아 끼우면 됩니다.

공백을 지우는 데 일 분이 걸리고, 안 지우면 수식을 세 번 고쳐도 같은 화면을 봅니다.