엑셀 vlookup NA 오류를 찾고 고치는 방법
거래처 목록을 엑셀로 맞춰 보다가 분명 있는 거래처 코드에 #N/A가 뜬 적이 있다. 화면에는 같은 값처럼 보였지만 한쪽은 숫자, 다른 쪽은 문자였다. 엑셀 vlookup NA 오류는 이런 식으로 실제 값과 눈에 보이는 값이 다를 때 자주 나온다. 수식을 덮어쓰기 전에 어느 단계에서 일치하지 않는지 확인하는 편이 빠르다.
먼저 수식의 검색 범위를 확인한다
예를 들어 B2의 상품코드를 H열에서 찾아 I열의 가격을 가져온다면 수식은 =VLOOKUP(B2,$H$2:$I$100,2,FALSE)다. VLOOKUP은 지정한 범위의 첫 번째 열에서만 검색한다. 상품코드가 I열에 있는데 범위를 H열부터 잡았다면, 그 코드가 시트 어딘가에 있어도 찾지 못한다.
마지막 인수 FALSE는 정확히 일치하는 값을 찾으라는 뜻이다. 이 인수를 생략하면 근삿값 검색이 기본으로 적용된다. 상품코드처럼 정확해야 하는 값은 FALSE를 명시하는 게 안전하다. Microsoft 지원 문서도 정확한 일치 검색에서 #N/A가 나오면 먼저 검색값이 범위의 첫 열에 있는지 확인하도록 안내한다.
수식을 아래로 복사한 뒤 일부 행에서만 오류가 난다면 범위도 다시 본다. H2:I100을 고정하지 않고 복사하면 검색 범위가 H3:I101, H4:I102처럼 밀린다. 원래 있던 값이 범위 밖으로 빠질 수 있으므로 $H$2:$I$100처럼 고정한다.
같아 보이는 코드의 자료형을 비교한다
실무에서 가장 헷갈리는 건 숫자 1234와 문자 '1234'다. 셀 표시 형식을 숫자로 바꿨다고 저장된 문자 값이 곧바로 숫자로 바뀌지는 않는다. 빈 셀에 =ISNUMBER(B2)를 넣고, 원본 목록의 해당 코드 셀에도 같은 검사를 해보면 차이를 확인할 수 있다. 한쪽만 TRUE라면 두 열의 자료형을 맞춰야 한다.
코드가 순수한 숫자이고 앞의 0이 의미 없다면 문자 쪽을 숫자로 변환할 수 있다. 보조 열에 =VALUE(H2)를 넣어 결과를 확인한 다음, 필요한 범위에 적용한다. 반대로 001234처럼 앞자리 0이 코드의 일부라면 숫자로 바꾸면 안 된다. 이 경우 두 열을 같은 자릿수의 문자 코드로 관리하는 편이 맞다. 숫자 1234를 여섯 자리 문자로 만들 때는 =TEXT(B2,"000000")을 쓸 수 있다.
여기서 중요한 건 변환 후 검색 대상 범위다. 원본 H열을 변환하지 않고 보조 열에 새 코드를 만들었다면 VLOOKUP의 검색 범위도 그 보조 열부터 시작해야 한다. 값을 고쳤는데 수식은 여전히 예전 열을 보고 있는 경우가 의외로 많다.
공백과 보이지 않는 문자를 제거한다
자료형이 같은데도 못 찾는다면 셀 안의 문자를 확인한다. 다른 시스템에서 내보낸 코드에는 앞뒤 공백이나 줄바꿈이 붙어 있을 수 있다. =LEN(B2)와 원본 셀의 LEN 결과를 비교하면 화면에서 안 보이는 글자 수 차이를 발견할 수 있다. 수식 입력줄에서 커서를 끝까지 옮겨 보는 것도 도움이 된다.
일반적인 앞뒤 공백은 =TRIM(H2), 인쇄되지 않는 일부 문자는 =CLEAN(H2)로 처리한다. 웹 화면에서 복사한 데이터에는 일반 공백과 다른 문자 코드 160이 들어가기도 한다. 이때는 보조 열에서 =TRIM(CLEAN(SUBSTITUTE(H2,CHAR(160)," ")))처럼 바꾼 뒤 결과를 확인한다. 원본을 바로 덮어쓰기보다 보조 열 몇 행에서 먼저 비교하는 게 안전하다.
TRIM은 단어 사이의 연속된 일반 공백도 한 칸으로 줄인다. 상품명처럼 내부 공백 자체가 의미 있는 데이터라면 일괄 적용 전에 원본과 변환 결과를 대조해야 한다.
오류를 표시하기 전에 원인을 고친다
검색값이 실제로 목록에 없다면 #N/A는 정상적인 신호다. 새 상품이 아직 가격표에 등록되지 않았거나 코드 입력이 틀렸을 수 있다. 입력값, 검색 범위의 첫 열, 자료형, 숨은 문자를 순서대로 확인한 뒤에 누락 여부를 판단한다.
누락을 확인한 뒤 화면에 안내 문구가 필요하면 =IFNA(VLOOKUP(B2,$H$2:$I$100,2,FALSE),"목록에 없음")을 쓸 수 있다. IFNA는 #N/A만 바꾸므로 다른 종류의 수식 오류는 그대로 드러난다. 재고나 금액을 맞추는 파일이라면 빈칸으로 숨기기보다 ‘목록에 없음’처럼 구분되는 문구가 확인하기 쉽다.
내 경험상 VLOOKUP 수식 자체가 틀린 경우보다 두 목록의 코드가 서로 다른 방식으로 저장된 경우가 더 오래 사람을 붙잡는다. 한 행을 골라 원본 값과 자료형부터 비교하면, 수백 행의 #N/A도 대개 같은 원인으로 묶여 해결된다.
