수식 계산기를 직접 만들면서 수천 건의 질문과 오류 데이터를 다뤄보면, VLOOKUP에서 #N/A가 뜨는 원인은 거의 네 가지 실수로 압축됩니다. 눈으로 볼 때는 분명 양쪽 표에 똑같은 사번이나 이름이 적혀 있는데도 엑셀은 "찾을 수 없다(Not Available)"며 에러를 뱉어냅니다.
원인을 모르면 데이터 전체를 하나씩 수작업으로 다시 치게 됩니다. 출근길에 5분만 투자해 아래 네 가지 체크리스트를 순서대로 확인해 보세요. 모니터 앞에서 헤매는 시간을 즉시 줄여줍니다.
보이지 않는 앞뒤 공백 제거하기
눈에 보이지 않는 빈칸 하나 때문에 엑셀은 두 값을 완전히 다른 글자로 인식합니다.
사람 눈에는 "김철수"와 "김철수 "가 똑같아 보입니다. 하지만 컴퓨터는 3글자와 4글자로 구별합니다. ERP 시스템이나 사내 인트라넷에서 엑셀로 데이터를 내려받을 때 이름 앞뒤에 보이지 않는 스페이스나 줄 바꿈이 딸려 들어오는 일이 흔합니다.
이 문제가 의심된다면 빈 셀에 글자 수를 세어주는 =LEN(셀주소)를 걸어보세요. 세 글자 이름인데 결과값이 4나 5로 나온다면 공백이 끼어 있는 상태입니다.
해결책은 간단합니다. 검색할 기준 열이나 원본 테이블 옆에 임시 열을 하나 파고 =TRIM(셀주소)를 입력하십시오. TRIM 함수는 글자 사이에 들어간 정상적인 띄어쓰기 한 칸을 뺀 나머지 불필요한 앞뒤 공백을 전부 지워줍니다. 공백을 정리한 값으로 VLOOKUP을 다시 걸면 #N/A가 즉시 사라집니다.
겉모습만 숫자인 텍스트 서식 일치시키기
문자 형태로 저장된 숫자와 진짜 숫자는 서로 엮이지 않습니다.
사번, 품번, 계약번호처럼 숫자로만 이루어진 항목에서 가장 빈번하게 터지는 문제입니다. 원본 데이터의 사번 '10203'은 텍스트 서식인데, 내가 VLOOKUP 첫 번째 인수로 찍은 값은 일반 숫자 서식이라면 엑셀은 일치하는 값을 찾지 못합니다. 셀 왼쪽 위에 초록색 작은 삼각형이 붙어 있다면 백발백중 텍스트로 저장된 숫자입니다.
두 데이터의 서식을 하나로 통일해야 합니다.
- 숫자를 텍스트로 바꾸려면 수식의 첫 번째 인수에 따옴표를 붙여
=VLOOKUP(A2&"", 범위, 열번호, FALSE)형태로 연결합니다. - 텍스트를 숫자로 바꾸려면 첫 번째 인수에 1을 곱해
=VLOOKUP(A2*1, 범위, 열번호, FALSE)형태로 강제 변환합니다.
데이터 양이 많다면 해당 열 전체를 잡은 뒤 데이터 탭의 '텍스트 나누기'를 누르고 별다른 설정 없이 바로 '마침'을 누르세요. 텍스트로 굳어 있던 숫자 데이터가 한꺼번에 실제 숫자 형태로 새로고침됩니다.
💡 수식 기호와 콤마 위치가 헷갈려 에러를 잡기 어렵다면?
원본 열과 가져올 데이터 위치만 지정해 조건에 맞는 정확한 수식을 한 번에 뽑아내고 싶을 때 엑셀 수식 생성기를 이용해 보세요.
수식을 밑으로 내릴 때 범위가 밀리는 절대참조 누락
첫 번째 행은 값이 잘 나오는데 아래로 드래그할수록 아래쪽 셀들만 줄줄이 #N/A로 바뀐다면 참조 범위를 고정하지 않은 탓입니다.
수식을 작성할 때 참조할 참조 표 범위를 A2:D50처럼 그냥 두면, 수식을 밑으로 한 칸 복사할 때마다 범위도 A3:D51, A4:D52로 한 줄씩 밀려 내려갑니다. 결국 표의 위쪽에 있던 데이터들이 검색 범위 밖으로 벗어나면서 아래쪽 행들은 데이터를 찾지 못하고 에러를 냅니다.
범위를 지정할 때는 반드시 키보드 F4 키를 눌러 달러 기호($)를 붙여야 합니다.
| 구분 | 잘못된 수식 입력 형태 | 올바른 절대참조 입력 형태 |
|---|---|---|
| 범위 지정 | =VLOOKUP(A2, C2:D50, 2, FALSE) |
=VLOOKUP(A2, $C$2:$D$50, 2, FALSE) |
| 열 전체 지정 | =VLOOKUP(A2, C:D, 2, FALSE) |
열 전체를 잡을 때는 밀림이 없으나 불필요한 연산 부하가 생김 |
수식 입력줄에서 테이블 범위 주소를 마우스로 드래그해 블록을 씌운 뒤 F4를 한 번 누르세요. $C$2:$D$50 형태로 행과 열 앞에 모두 달러 기호가 붙어야 수식을 수천 행 아래로 내리더라도 검색 기준 테이블이 제자리에 고정됩니다.
마지막 인수에 0(FALSE)을 빠뜨린 유사 일치
네 번째 인수인 일치 옵션을 생략하면 엑셀은 멋대로 비슷한 값을 찾아 헤매다가 에러를 냅니다.
VLOOKUP 수식의 기본 구조는 =VLOOKUP(찾을값, 참조범위, 열번호, [일치옵션])입니다. 여기서 마지막 네 번째 대괄호 항목은 생략이 가능하도록 설계되어 있습니다. 하지만 이 마지막 자리를 비워두면 엑셀은 값을 생략한 것이 아니라 숫자 1, 즉 '유사 일치(TRUE)'로 받아들입니다.
유사 일치는 원본 데이터의 첫 번째 열이 오름차순(1, 2, 3... 또는 가, 나, 다...)으로 완벽하게 정렬되어 있을 때만 정상 작동합니다. 뒤죽박죽 섞여 있는 일반적인 업무 표에서 마지막 인수를 빼먹으면, 엑셀은 엉뚱한 행의 값을 가져오거나 심지어 데이터가 버젓이 있는데도 #N/A를 표시합니다.
토씨 하나 틀리지 않고 똑같은 값을 찾아오려면 수식 맨 끝에 반드시 , 0 또는 , FALSE를 적어야 합니다.
=VLOOKUP(A2, $D$2:$F$100, 3, 0)
쉼표를 찍고 0을 입력하는 습관만 들여도 이유를 알 수 없던 #N/A 에러의 상당 부분을 사전에 차단할 수 있습니다. 최신 엑셀을 사용 중이라면 이런 구조적 한계가 개선되어 기본값이 정확한 일치로 고정되어 있는 XLOOKUP 함수로 갈아타는 것도 확실한 대안입니다.