VLOOKUP 수식을 분명히 제대로 썼는데 결과 칸에 #N/A만 뜨는 경우가 있습니다. 눈으로 봐도 데이터가 분명히 있는데 말이죠.
#N/A는 “찾는 값이 없다”는 뜻입니다. 함수가 고장 난 게 아니라, 엑셀이 보기에 정말로 그 값이 없다는 겁니다. 원인은 대부분 아래 다섯 가지 중 하나입니다.
1. 숫자와 텍스트가 섞여 있다
가장 흔한 원인입니다. 같은 1234라도 한쪽은 숫자로, 다른 쪽은 텍스트로 저장되어 있으면 엑셀은 다른 값으로 봅니다.
확인 방법은 간단합니다. 셀 왼쪽 위에 녹색 삼각형 표시가 있다면 텍스트로 저장된 숫자입니다. 또 값이 셀 안에서 왼쪽 정렬되어 있으면 텍스트, 오른쪽 정렬이면 숫자입니다.
해결하려면 한쪽 형식을 맞춰주면 됩니다. 텍스트를 숫자로 바꾸려면 해당 열을 선택하고 데이터 탭의 텍스트 나누기를 눌러 그대로 마침을 클릭하면 일괄 변환됩니다.
2. 눈에 안 보이는 공백이 있다
외부 시스템에서 내려받은 데이터에서 특히 자주 생깁니다. 값 끝에 공백이 하나 붙어 있으면 “홍길동”과 “홍길동 “은 다른 값입니다.
TRIM 함수로 정리할 수 있습니다.
=TRIM(A2)또는 수식 안에서 바로 처리해도 됩니다.
=VLOOKUP(TRIM(E2), $A$2:$C$100, 2, 0)3. 찾을값이 범위의 첫 열에 없다
VLOOKUP은 지정한 범위의 첫 번째 열에서만 값을 찾습니다. 범위를 B열부터 잡았다면 B열에서 찾습니다.
이름으로 사원번호를 찾는 것처럼 오른쪽 값으로 왼쪽 값을 가져오는 건 VLOOKUP으로 불가능합니다. 그럴 때는 INDEX와 MATCH를 조합하세요.
=INDEX($A$2:$A$100, MATCH(E2, $B$2:$B$100, 0))방향에 제약이 없어서 실무에서는 이 조합이 더 편할 때가 많습니다.
4. 범위가 밀려 내려갔다
수식을 아래로 끌어내릴 때 범위에 달러 표시를 안 했다면, 행이 내려갈 때마다 범위도 같이 내려갑니다. 처음 몇 줄은 맞다가 아래쪽에서 #N/A가 뜨는 패턴이면 이걸 의심하세요.
범위 부분에 커서를 두고 F4 키를 누르면 달러 표시가 붙습니다. 이런 단축키들은 업무 시간 줄여주는 엑셀 단축키 모음에 정리해 두었습니다.
5. 정말로 값이 없다
의외로 이 경우도 많습니다. 신규 데이터라 마스터 표에 아직 등록이 안 되어 있거나, 코드 체계가 바뀌었거나 하는 경우입니다.
이건 수식 문제가 아니라 데이터 문제입니다. 오류를 감추지 말고 목록을 확인해 보세요. 목록에 중복이 섞여 있는지 점검하려면 엑셀 중복값 찾기와 제거하는 세 가지 방법이 도움이 됩니다.
오류를 보기 좋게 처리하기
원인을 다 확인했는데도 일부는 값이 없을 수 있습니다. 보고서에 #N/A가 그대로 나오면 보기 안 좋으니 IFERROR로 감쌉니다.
=IFERROR(VLOOKUP(E2, $A$2:$C$100, 2, 0), "미등록")다만 원인을 파악하기 전에 IFERROR부터 씌우는 건 위험합니다. 진짜 오류까지 가려버려서 나중에 문제를 찾기 어려워집니다. 먼저 원인을 확인하고, 정상적으로 값이 없는 경우에만 쓰세요.
다른 오류 메시지들
#N/A 말고도 VLOOKUP에서 나오는 오류가 몇 가지 더 있습니다. 각각 원인이 다릅니다.
#REF! — 참조가 잘못됨
지정한 범위보다 큰 열 번호를 요구했을 때 나옵니다. 범위가 A열부터 C열까지 3개인데 4번째 열을 가져오라고 하면 이 오류가 뜹니다.
범위를 넓히거나 열 번호를 줄이면 해결됩니다. 중간에 열을 삭제했을 때도 생기니, 최근에 시트 구조를 바꿨다면 그 부분을 확인해 보세요.
#VALUE! — 값의 형태가 잘못됨
열 번호 자리에 숫자가 아닌 값이 들어갔거나, 0 이하의 숫자를 넣었을 때 나옵니다. 수식을 다시 살펴보면 대개 오타가 발견됩니다.
#NAME? — 함수 이름이 틀림
VLOOKUP을 VLOOKUP이 아닌 다른 철자로 썼을 때입니다. 또는 큰따옴표를 빼먹었을 때도 나옵니다. 텍스트는 반드시 큰따옴표로 감싸야 합니다.
원인을 빠르게 찾는 요령
수식 대신 값으로 직접 테스트
수식이 복잡할 때는 문제를 쪼개서 확인하는 게 빠릅니다. 빈 셀에 이렇게 입력해 보세요.
=COUNTIF(D:D, A2)결과가 0이면 정말로 그 값이 D열에 없다는 뜻입니다. 데이터 문제입니다. 1 이상인데도 VLOOKUP이 안 된다면 범위 지정이나 열 번호가 잘못된 것입니다.
이 방법 하나로 원인을 절반은 좁힐 수 있습니다.
수식 계산 단계 보기
수식 탭에 수식 계산이라는 기능이 있습니다. 오류가 난 셀을 선택하고 이걸 누르면 계산 과정을 한 단계씩 볼 수 있습니다.
어느 부분에서 값이 이상해지는지 눈으로 확인할 수 있어서, 복잡한 중첩 수식을 다룰 때 특히 유용합니다.
F9 키로 부분 확인
수식 편집 상태에서 특정 부분만 드래그해 선택한 뒤 F9를 누르면 그 부분의 결과값이 표시됩니다. 범위가 제대로 잡혔는지 즉시 확인할 수 있습니다.
확인 후에는 반드시 Esc를 눌러 취소하세요. 엔터를 치면 계산된 값이 수식에 그대로 박혀버립니다.
애초에 오류를 줄이는 방법
원본 데이터를 표로 지정하세요. 범위를 선택하고 Ctrl+T를 누르면 표가 됩니다. 그러면 범위를 이름으로 참조할 수 있어서 달러 표시를 신경 쓸 필요가 없고, 행이 늘어나도 자동으로 확장됩니다.
데이터를 받으면 먼저 정리하세요. TRIM으로 공백을 없애고, 텍스트 나누기로 형식을 통일하는 것을 습관으로 만들면 오류의 상당수가 애초에 발생하지 않습니다.
VLOOKUP 함수 자체의 기본 사용법이 헷갈린다면 마이크로소프트 엑셀 공식 지원 센터에서 함수 문법을 다시 확인해 보세요.