거래처명도 상품코드도 눈으로 보면 똑같은데 결과는 계속 #N/A. 같은 셀을 몇 번이나 다시 확인하게 되죠. 엑셀 VLOOKUP N A 오류는 눈에 보이지 않는 공백이나 서로 다른 데이터 형식 때문에 생길 수 있습니다.
오류를 빈칸으로 가리기 전에 검색값과 원본을 나란히 살펴보세요. 값이 실제로 없는 건지, 겉모양만 같은 건지부터 갈라내면 됩니다.
먼저 3초 판독표로 원인을 좁히세요
| 내 화면의 상황 | 확인할 것 | 먼저 할 일 |
|---|---|---|
| 이름이 같아 보이는데 글자 수가 다름 | 앞뒤 일반 공백 | LEN으로 길이를 비교하고 TRIM 적용 |
| 복사한 이름에 줄바꿈이 섞임 | 인쇄되지 않는 문자 | CLEAN으로 정리 |
| TRIM을 써도 공백처럼 보이는 부분이 남음 | CHAR(160) 특수 공백 가능성 | SUBSTITUTE로 일반 공백으로 바꾼 뒤 TRIM 적용 |
| 상품코드가 같은 숫자로 보임 | 한쪽은 숫자, 다른 쪽은 텍스트인지 | 두 셀의 실제 데이터 형식을 맞춤 |
| 정리 후에도 못 찾음 | 조회 범위의 첫 열과 실제 원본 값 | 범위와 검색값을 다시 확인 |
예를 들어 검색값이 ‘사과’이고 원본이 ‘사과 ’라면 눈으로는 지나치기 쉽습니다. 두 값의 길이를 각각 =LEN(D2), =LEN(A2)로 비교해 보세요.
길이가 같아도 문자가 다를 수 있습니다. 길이 비교는 출발점이지 일치 여부를 보증하는 검사는 아닙니다.
스마트폰 화면에서 본인에게 해당하는 항목을 직접 눌러 체크하고, 아래 상황별 판독 결과를 펼쳐 확인해보세요.
👉 [내 상황 판독 1] 이름이 같아 보이는데 글자 수가 다름결과 보기 ▼
👉 [내 상황 판독 2] 복사한 이름에 줄바꿈이 섞임결과 보기 ▼
👉 [내 상황 판독 3] TRIM을 써도 공백처럼 보이는 부분이 남음결과 보기 ▼
원본을 복사하고 보조열에서 공백을 정리하세요
원본 파일을 먼저 복사하세요. 아래 예제는 A열에 거래처명, B열에 반환할 부서명, D2에 찾을 거래처명이 있는 상황입니다.
비어 있는 F2에 아래 수식을 입력하고 원본 데이터의 마지막 행까지 채웁니다. A열의 이름을 직접 덮어쓰지 않고 정리한 이름을 따로 만듭니다.
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
이 수식은 CHAR(160) 특수 공백을 일반 공백으로 바꾸고, CLEAN으로 줄바꿈 등 일부 인쇄되지 않는 문자를 제거한 다음, TRIM으로 앞뒤 일반 공백과 반복된 일반 공백을 정리합니다.
모든 종류의 특수 공백이나 보이지 않는 문자가 한 번에 사라지는 것은 아닙니다. 정리 전후 이름이 의도대로 유지되는지도 확인하세요.
이제 G2에는 =B2를 입력하고 같은 행까지 채워 F열의 정리된 이름 옆에 부서명을 놓습니다. 검색값 D2도 같은 방식으로 정리해야 양쪽 기준이 맞습니다.
=TRIM(CLEAN(SUBSTITUTE(D2,CHAR(160)," ")))
이 수식을 E2에 넣었다면, 예를 들어 데이터가 100행까지 있을 때 다음 수식으로 재조회합니다.
=VLOOKUP(E2,$F$2:$G$100,2,FALSE)
F열은 정리된 이름, G열은 부서명입니다. 찾을 값이 조회 범위의 첫 번째 열에 있어야 하고, FALSE는 정확히 일치하는 값을 찾도록 합니다. 데이터가 100행을 넘어가면 범위의 끝 행도 늘려 주세요.
공백을 지웠는데도 #N/A가 남는다면
첫째, 숫자와 텍스트를 구분하세요. 상품코드 123이 양쪽에 보이더라도 한쪽은 숫자, 다른 쪽은 텍스트일 수 있습니다. 셀의 실제 형식을 확인하고 양쪽을 같은 형식으로 맞춘 뒤 다시 조회하세요.
특히 앞자리 0이 의미 있는 코드라면 숫자로 바꾸면서 0이 사라지지 않도록 주의하세요. 표시 형식만 바꿔서는 이미 저장된 값의 형식이 바뀌지 않을 수 있습니다.
둘째, 조회 범위의 첫 열을 확인하세요. 찾을 이름이 G열에 있는데 범위가 F열부터 시작한다면 VLOOKUP은 F열에서 이름을 찾습니다. 정리한 검색 기준을 범위의 맨 왼쪽에 두세요.
셋째, 원본에 값이 실제로 있는지 확인하세요. 정확히 일치하는 항목이 없으면 FALSE를 써도 #N/A가 나옵니다. 반대로 마지막 인수를 생략하면 기본값이 유사 일치라 잘못된 값을 가져올 수도 있습니다.
오류를 IFERROR로 빈칸 처리하면 화면은 깔끔해지지만 누락된 항목도 보이지 않습니다. 원인을 확인한 뒤 표시 방식이 필요할 때 적용하세요.
관련 기능은 Microsoft의 #N/A 오류 안내와 VLOOKUP 함수 설명에서도 확인할 수 있습니다.
지금 원본 파일을 복사한 뒤 오류 셀 하나를 고르세요. 검색값과 원본의 길이와 데이터 형식을 비교하고, 보조열 정리 수식을 아래로 채운 다음 FALSE로 다시 조회해 보세요.
