구글 스프레드시트 VLOOKUP 안 될 때: #N/A·#REF!·엉뚱한 값 해결
구글 스프레드시트에서 VLOOKUP이 안 될 때는 수식을 처음부터 다시 쓰기보다 조회값, 범위의 첫 열, 열 색인, 일치 방식을 차례로 확인하는 편이 빠릅니다.
특히 일반적인 상품코드·사번 조회라면 마지막 인수를 FALSE로 두고, 조회하려는 값이 범위의 첫 번째 열에 있는지 먼저 보세요. TRUE를 쓰거나 마지막 인수를 생략하면 오류 대신 그럴듯한 오답이 나올 수도 있습니다.
#N/A: 조회값이 실제로 있는지, 공백·숫자/텍스트 형식이 다른지 확인#REF!: 열 색인이 범위의 열 수를 넘었는지 확인#VALUE!: 열 색인이 1보다 작은지 확인- 오류 없이 엉뚱한 값:
TRUE또는 생략된 마지막 인수와 정렬 상태 확인
VLOOKUP 결과를 결정하는 네 가지
VLOOKUP은 네 인수 중 하나만 어긋나도 결과가 달라집니다. 기본 구조는 다음과 같습니다.
=VLOOKUP(조회값, 범위, 열_색인, 정렬여부)- 조회값: 찾으려는 상품코드나 사번
- 범위: 조회표 전체. 조회값은 반드시 이 범위의 첫 번째 열에 있어야 함
- 열 색인: 범위 첫 열을 1로 세어 가져올 열의 번호
- 정렬여부:
FALSE는 정확히 같은 값,TRUE는 정렬된 구간표의 근사값
예제 표의 범위는 A2:C5이고, A열은 상품코드, B열은 상품명, C열은 단가입니다. P-1002의 단가를 찾는 수식은 아래와 같고 결과는 18,000이었습니다.
=VLOOKUP(E2,$A$2:$C$5,3,FALSE)$는 수식을 아래로 복사해도 조회 범위가 움직이지 않게 합니다. 한 셀만 확인할 때는 없어도 되지만, 여러 행에 채울 때는 절대참조로 고정하는 편이 안전합니다.
오류 문구별로 가장 먼저 볼 곳
오류 문구는 원인을 좁히는 출발점입니다. 같은 예제 표에서 정상값과 세 오류를 직접 재현한 결과는 다음과 같습니다.
| 증상 | 재현 조건 | 먼저 고칠 곳 |
|---|---|---|
#N/A | 없는 코드 P-9999 | 조회값·공백·형식·범위 첫 열 |
#REF! | A:C 범위에 색인 4 | 색인을 1~3으로 수정 |
#VALUE! | 열 색인 0 | 색인을 1 이상으로 수정 |
| 엉뚱한 값 | 미정렬 구간표에 TRUE | FALSE 또는 오름차순 정렬 |
#N/A는 조회값과 첫 열부터 확인
#N/A는 정확히 일치하는 조회값을 찾지 못했다는 뜻입니다. 값이 정말 없는 경우뿐 아니라 눈에 보이지 않는 공백이나 조회 범위의 방향 때문에도 발생합니다.
없는 값과 숨은 공백
예제에서 P-9999는 A열에 없으므로 FALSE 조회 결과가 #N/A였습니다. 또 P-1002 뒤에 공백 한 칸을 넣자 화면에서는 비슷해 보여도 일치하지 않았습니다.
조회값 쪽의 불필요한 앞뒤 공백이라면 TRIM으로 정리할 수 있습니다. 같은 테스트에서 아래 수식은 다시 18,000을 반환했습니다.
=VLOOKUP(TRIM(E6),$A$2:$C$5,3,FALSE)원본 표의 코드에도 공백이 섞여 있다면 조회 수식만 고치기보다 보조 열에서 =TRIM(A2)로 정리한 값을 만든 뒤 그 열을 기준으로 조회하는 편이 재사용하기 쉽습니다.
조회값은 범위의 첫 번째 열에 있어야 함
VLOOKUP은 지정한 범위의 첫 열에서만 값을 찾습니다. 무선 마우스를 A2:C5에서 찾으면 상품명이 B열에 있어도 #N/A가 나왔습니다.
범위를 B2:C5로 바꾸고 단가가 범위의 두 번째 열이라는 뜻으로 색인 2를 쓰자 결과가 18,000으로 복구됐습니다.
=VLOOKUP(E9,$B$2:$C$5,2,FALSE)숫자와 텍스트 형식도 맞춰야 함
겉으로 같은 1002라도 한쪽이 숫자이고 다른 쪽이 텍스트면 정확 일치가 실패할 수 있습니다. 먼저 셀의 표시 형식과 실제 입력값을 확인하고, 코드처럼 앞자리 0이 의미가 있는 값은 양쪽을 텍스트로 통일하세요.
#N/A면 실제 미등록 값인지 확인합니다.#REF!와 #VALUE!는 열 색인을 점검
범위가 세 열이면 열 색인은 1, 2, 3만 사용할 수 있습니다. 예제의 A2:C5에서 색인 4를 입력하자 가져올 네 번째 열이 없어 #REF!가 발생했습니다.
=VLOOKUP(E4,$A$2:$C$5,4,FALSE)단가가 C열이라면 색인을 3으로 고치면 됩니다. 중간 열을 삽입하거나 범위를 줄인 뒤 갑자기 #REF!가 생겼다면 범위와 색인을 함께 다시 세어 보세요.
색인을 0으로 입력한 테스트에서는 #VALUE!가 발생했습니다. 열 색인은 범위의 첫 열을 1로 시작하는 양의 숫자여야 합니다.
=VLOOKUP(E5,$A$2:$C$5,0,FALSE)오류 없이 엉뚱한 값이면 TRUE를 확인
상품코드처럼 하나의 값을 정확히 찾을 때는 FALSE가 기본 선택입니다. TRUE는 점수 구간, 배송 등급처럼 기준값 이하에서 가장 가까운 구간을 찾을 때 사용합니다.
문제는 TRUE가 첫 열이 오름차순으로 정렬됐다고 가정한다는 점입니다. 마지막 인수를 생략해도 근사 일치로 처리되므로 같은 주의가 필요합니다.
검증 시트에서 기준값을 0, 100, 50, 200 순으로 일부러 섞고 조회값 120에 TRUE를 적용하자 오류 메시지 없이 등급 B가 나왔습니다. 기준값을 0, 50, 100, 200으로 정렬한 뒤에는 의도한 등급 A가 반환됐습니다.
=VLOOKUP(120,$I$7:$J$10,2,TRUE)FALSE로 바꾸세요.오류를 가리기 전에 원인을 고치는 순서
IFERROR로 모든 오류를 바로 숨기면 범위나 색인 문제까지 놓칠 수 있습니다. 먼저 원인을 고친 뒤, 실제로 등록되지 않은 코드만 사용자에게 안내해야 할 때 IFNA를 덧붙이는 방식이 안전합니다.
=IFNA(VLOOKUP(TRIM(E2),$A$2:$C$5,3,FALSE),"미등록")IFNA를 빼고 원래 오류를 확인합니다.- 조회값, 범위 첫 열, 색인,
FALSE순서로 고칩니다. - 정상 코드가 원하는 값을 반환하는지 확인합니다.
- 업무상 존재하지 않을 수 있는 코드만
IFNA로 안내 문구를 표시합니다.
이렇게 하면 정상적인 미등록 값은 읽기 쉽게 처리하면서도 #REF!나 #VALUE! 같은 수식 구조 오류는 그대로 드러나 수정할 수 있습니다.
마지막 점검 체크리스트
- 조회하려는 값이 선택한 범위의 첫 열에 있는가?
- 눈에 안 보이는 앞뒤 공백을 제거했는가?
- 숫자와 텍스트 형식이 양쪽에서 같은가?
- 열 색인이 1 이상이고 범위 열 수를 넘지 않는가?
- 정확 일치는
FALSE, 근사 일치는 오름차순 정렬을 사용했는가? - 수식을 복사할 때 조회 범위를
$로 고정했는가?
이 순서대로 확인하면 #N/A, #REF!, #VALUE!뿐 아니라 오류 없이 잘못된 값이 나오는 경우까지 구분할 수 있습니다.
공식 도움말과 관련 글
- Google 문서 편집기 공식 VLOOKUP 함수 도움말: 인수 구조, 정확·근사 일치, 오류별 조건 확인
- 구글 스프레드시트 중복값을 표시하고 안전하게 정리하는 방법: 조회 원본의 중복 데이터를 먼저 점검할 때 참고
- 구글 스프레드시트 드롭다운으로 입력값을 통일하는 방법: 상품코드 입력 실수를 줄이고 싶을 때 참고