엑셀에서 VLOOKUP을 썼는데 #N/A 오류가 뜨는 경우, 값이 없어서가 아닐 때가 훨씬 많습니다.
표에 분명히 있는데도 못 찾는 겁니다. #N/A는 "그런 값이 없다"가 아니라 "내가 못 찾았다" 는 뜻이라서,
원인은 대부분 값이 아니라 찾는 방식에 있습니다.
실무에서 자주 나오는 순서대로 다섯 가지를 정리했습니다.
먼저 30초 만에 확인하는 법
원인을 하나씩 뒤지기 전에 빈 칸에 이것부터 쳐보세요.
=A2=D2
A2가 찾을 값, D2가 표에 있는 값입니다.
눈으로 봐서 똑같은데 결과가 FALSE로 나오면, 두 값이 겉보기만 같고 실제로는 다르다는 뜻입니다.
그러면 아래 1번이나 2번입니다. TRUE가 나오는데도 #N/A라면 3~5번을 보시면 됩니다.
1. 눈에 안 보이는 공백
"김주임 "과 "김주임"은 엑셀에게 다른 값입니다.
뒤에 붙은 스페이스 하나가 보이지 않을 뿐이죠. 시스템에서 내려받은 자료에 특히 많습니다.
=VLOOKUP(TRIM(A2), $D$2:$E$50, 2, FALSE)
여기서 놓치기 쉬운 게 하나 있습니다. 공백이 표 쪽에 붙어 있으면 찾을 값만 TRIM해서는 안 잡힙니다.
이럴 땐 표의 열을 통째로 정리해야 합니다.
빈 열에 =TRIM(D2)를 넣어 끌어내린 다음, 그 결과를 복사해서 값으로 붙여넣기 하면 됩니다.
2. 숫자처럼 보이는 글자
회원번호나 상품코드처럼 앞에 0이 붙는 값이 대표적입니다.
'001은 글자이고 1은 숫자라서, 화면에 똑같이 001로 보여도 엑셀은 다르게 봅니다.
구분하는 방법은 간단합니다. 엑셀은 숫자를 칸 오른쪽에, 글자를 칸 왼쪽에 붙입니다.
붙는 방향이 서로 다르면 형식이 다른 겁니다.
급할 때 쓰는 방법도 있습니다.
- 찾을 값이 글자, 표가 숫자일 때 → =VLOOKUP(A2*1, ...)
- 찾을 값이 숫자, 표가 글자일 때 → =VLOOKUP(A2&"", ...)
다만 이건 임시방편입니다. 매주 쓰는 표라면 양쪽 형식을 처음부터 맞춰두는 편이 낫습니다.
3. 찾을 값이 표의 첫 열에 없을 때
VLOOKUP은 지정한 범위의 첫 번째 열에서만 찾습니다.
이걸 모르고 범위를 넓게 잡으면 #N/A가 뜹니다.
예를 들어 D열에 사번, E열에 이름, F열에 부서가 있는데 이름으로 부서를 찾고 싶다면,
범위를 $D$2:$F$50으로 잡으면 안 됩니다. 엑셀은 D열(사번)에서 이름을 찾다가 못 찾고 #N/A를 냅니다.
범위를 $E$2:$F$50처럼 찾을 값이 든 열부터 잡아야 합니다.
그런데 이름보다 왼쪽에 있는 사번을 가져와야 한다면 VLOOKUP으로는 안 됩니다. 이때는 INDEX와 MATCH를 씁니다.
4. 끌어내릴 때 범위가 같이 밀렸을 때
첫 줄은 잘 나오는데 아래로 갈수록 #N/A가 늘어난다면 이겁니다.
=VLOOKUP(A2, D2:E50, 2, FALSE)
이 수식을 끌어내리면 범위가 D3:E51, D4:E52로 한 줄씩 같이 내려갑니다.
표 위쪽 값들이 범위 밖으로 밀려나면서 못 찾게 되는 겁니다.
=VLOOKUP(A2, $D$2:$E$50, 2, FALSE)
달러 표시로 묶으면 끌어내려도 범위가 그대로 있습니다. 달러를 붙이는 단축키는 F4입니다.
5. 마지막 인자를 비웠을 때
=VLOOKUP(A2, $D$2:$E$50, 2)
마지막 FALSE를 빼면 엑셀은 비슷한 값 찾기로 동작합니다.
표가 오름차순으로 정렬돼 있지 않으면 엉뚱한 값이 나오거나 #N/A가 뜹니다.
찾을 값이 표의 가장 작은 값보다 작을 때도 #N/A입니다.
특별한 이유가 없으면 마지막에 FALSE나 0을 꼭 붙이세요. 둘은 같은 뜻입니다.
다 확인했는데도 #N/A가 뜬다면
정말 그 값이 표에 없는 경우입니다. 이때는 오류를 그냥 두지 말고 말로 바꿔주는 게 좋습니다.
=IFNA(VLOOKUP(A2, $D$2:$E$50, 2, FALSE), "명단에 없음")
여기서 IFERROR가 아니라 IFNA를 쓴 이유가 있습니다.
IFERROR는 오류 종류를 안 가리고 전부 덮습니다.
함수 이름을 잘못 쳐서 난 오류도, 참조하던 열을 지워서 난 오류도 똑같이 "명단에 없음"으로 바뀝니다.
내가 수식을 잘못 만든 게 화면에서 사라지는 겁니다.
IFNA는 #N/A만 덮고 나머지 오류는 그대로 보여줍니다. 엑셀 2013 이상에서 쓸 수 있습니다.
정리
| 증상 | 원인 | 해결 |
|---|---|---|
| =A2=D2가 FALSE | 공백 또는 형식 차이 | TRIM, 형식 통일 |
| 첫 줄만 되고 아래는 오류 | 범위가 같이 밀림 | $로 묶기 (F4) |
| 전부 다 오류 | 찾을 값이 첫 열에 없음 | 범위를 다시 잡거나 INDEX와 MATCH |
| 값이 엉뚱하게 나옴 | 마지막 인자 생략 | FALSE 또는 0 붙이기 |
#N/A는 고장이 아니라 "여기를 보라"는 안내문입니다. 위에서부터 순서대로 짚으면 대부분 5분 안에 잡힙니다.