구글시트 VLOOKUP·XLOOKUP #N/A 해결: 값이 있는데 못 찾을 때

Google Sheets 기준 · 예제는 합성 상품 코드 · 공식 도움말 확인: 2026년 10월 3일

먼저 같은 데이터에서 수식이 어떻게 동작하는지 확인

아래는 설명용으로 만든 상품 목록입니다. A열의 코드에서 D2에 입력한 코드를 찾고, 같은 행의 C열 가격을 가져온다고 가정합니다.

정확히 일치하는 VLOOKUP은 범위의 네 번째 인수를 FALSE로 둡니다. 결과는 8,000입니다.

=VLOOKUP(D2,$A$2:$C$4,3,FALSE)

XLOOKUP은 찾을 열과 반환할 열을 따로 지정합니다. 검색 옵션을 생략해도 정확히 일치가 기본값이지만, 다른 사람이 수식을 읽을 때도 의도가 보이도록 0을 명시할 수 있습니다.

=XLOOKUP(D2,$A$2:$A$4,$C$2:$C$4,"코드 확인",0)

코드가 실제 목록에 없으면 VLOOKUP은 #N/A, XLOOKUP은 네 번째 인수인 코드 확인을 반환합니다. 원인을 직접 진단하려면 우선 XLOOKUP의 네 번째 인수 없이 실행해 #N/A가 재현되는지 보세요.

1. 조회 키가 원본 목록에 실제로 있는지 확인

화면에서 비슷하게 보여도 코드가 다를 수 있고, 조회 대상이 최신 데이터 표에 아직 추가되지 않았을 수도 있습니다. 입력한 키와 공식 원본 범위가 같은 파일·시트·버전을 보고 있는지 먼저 확인합니다.

1. 검색 키를 복사해 원본 표의 키 열에서 찾습니다.
2. 조회 값이 빈칸인지 확인합니다. 입력 칸이 비어 있을 때는 오류 문구 대신 빈칸으로 두려면 최종 수식에 별도 빈값 처리를 둡니다.
3. 원본 목록의 기준 키가 실제 코드인지, 이름·표시값·별칭인지 확인합니다. 화면에 보이는 상품명과 수식에서 찾는 상품 코드가 서로 다르면 일치하지 않습니다.

오류만 감추고 싶어서 수식 전체를 IFERROR로 감싸면 실제 누락과 잘못된 범위·다른 오류가 같은 빈칸으로 보일 수 있습니다. 먼저 오류가 재현되는 기본 수식을 사용해 문제 위치를 좁히세요.

2. VLOOKUP은 FALSE, XLOOKUP은 정확 일치인지 확인

Google Sheets의 VLOOKUP에서 네 번째 인수를 빼면 TRUE, 즉 근사 일치가 기본입니다. 코드를 조회할 때는 값이 존재해도 정렬 조건이나 경계에 따라 엉뚱한 값 또는 #N/A가 날 수 있습니다. 정확한 코드 조회라면 FALSE를 명시하세요.

=VLOOKUP(D2,$A$2:$C$4,3,FALSE)

XLOOKUP의 기본 match_mode는 0(정확 일치)입니다. 그래도 수식 검토가 쉽도록 마지막 인수에 0을 써 두면 근사 일치 옵션을 실수로 넘겼는지 확인하기 좋습니다.

=XLOOKUP(D2,$A$2:$A$4,$C$2:$C$4,"코드 확인",0)

3. 검색 범위가 올바른 열에서 시작하고 새 행까지 포함하는지 확인

VLOOKUP은 지정한 범위의 첫 번째 열에서만 검색합니다. 코드가 B열인데 범위를 A:C로 잡으면, 함수는 A열을 훑으므로 B열에서 코드를 찾아주지 않습니다. B열의 코드를 찾는다면 범위도 B열에서 시작해야 합니다.

=VLOOKUP(D2,$B$2:$D$100,3,FALSE)

위 예에서 가격이 D열이면 선택 범위 B:D 안에서 D열은 세 번째 열이므로 색인은 3입니다. 범위를 A:C에서 B:D로 바꾼 뒤에도 예전 색인을 복사하면 다른 열의 정상값이 나올 수 있습니다. 이 경우 #N/A 대신 “틀린 값”이 나타날 수 있으니 반환 열도 확인하세요.

새 데이터가 101행 아래로 늘어났는데 범위가 $B$2:$D$100에서 끝나면 새 키는 범위에 없습니다. XLOOKUP은 범위를 별도로 쓰므로 조회 열과 반환 열이 같은 행부터 시작하고 길이가 맞는지 살핍니다. XLOOKUP의 조회 범위는 단일 행 또는 열이어야 합니다.

4. 화면에 안 보이는 공백을 양쪽에서 같은 방식으로 정리

SKU-102와 SKU-102 는 눈으로는 같아 보여도 뒤쪽에 일반 공백이 있으면 다른 문자열입니다. 복사한 값에 앞·뒤 공백이 있는지 간단히 길이를 비교하세요.

=LEN(D2)
=LEN(A3)

두 길이가 다르면 한쪽에 숨은 문자가 있는 단서입니다. 일반 공백에는 보조 열에 TRIM을 적용할 수 있습니다. 원본 A2를 그대로 보존하고, 보조 열에 다음 수식을 넣어 아래로 채웁니다.

=TRIM(A2)

웹 페이지나 PDF에서 붙여 넣은 코드에는 일반 공백과 겉모양이 비슷한 줄바꿈 없는 공백(U+00A0)이 들어갈 수 있습니다. Google Sheets의 데이터 정리 → 공백 삭제와 TRIM도 NBSP를 지우지 않습니다. 일반 공백으로 바꾼 뒤 양 끝 공백을 정리하는 보조 열을 사용합니다.

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

조회 목록 A열만 정리하고 D2 키는 그대로 두면 여전히 불일치합니다. 원본 목록 키와 검색 키 양쪽에 같은 정리 규칙을 적용한 뒤, 정리된 키 열을 조회 범위로 사용하세요. 보이지 않는 문자를 확인하기 어렵다면 먼저 원본을 복사한 뒤 한 열에만 시험해 결과를 대조합니다. CLEAN은 인쇄 불가능한 ASCII 문자 정리용이며 모든 Unicode 공백을 제거하는 만능 함수가 아닙니다.

5. 숫자처럼 보이는 텍스트와 실제 숫자를 구분

예를 들어 원본 코드는 텍스트 "00123"이고 검색 칸은 숫자 123이면 표시 형식이 비슷해도 같다고 단정할 수 없습니다. 비교할 원본 셀과 조회 셀에 ISTEXT 또는 ISNUMBER를 넣어 자료형을 확인하세요.

수량·금액·날짜라면 양쪽을 올바른 숫자/날짜 자료형으로 맞추는 편이 좋습니다. VALUE는 인식 가능한 숫자·날짜·시간 문자열을 숫자로 변환할 수 있지만, 날짜는 내부 일련번호가 될 수 있으므로 결과를 확인하세요.

제품 코드, 우편번호, 계정 ID처럼 선행 0이 중요한 식별자에는 VALUE를 무작정 쓰지 마세요. 00123을 숫자로 바꾸면 0이 없어져 원래 식별 의미가 달라질 수 있습니다. 그런 값은 양쪽 모두 텍스트로 유지하고, 원본을 보존한 보조 열에서 변환 결과를 비교하세요.

6. 중복 키라면 #N/A보다 잘못된 정상값을 의심

코드표에 같은 키가 여러 번 있으면 VLOOKUP은 첫 번째 일치값을 반환합니다. XLOOKUP도 기본 검색 순서는 첫 행에서 마지막 행입니다. 따라서 중복 키가 있어도 #N/A 대신 가격·담당자 등 그럴듯하지만 잘못된 값이 나올 수 있습니다.

=COUNTIF($A$2:$A$100,A2)는 같은 기준의 값을 세는 빠른 중복 후보 검사입니다. 단, Google Sheets의 COUNTIF는 대소문자를 구분하지 않으므로 대소문자를 구별해야 하는 ID라면 이 식 하나만으로 고유성을 확정하지 마세요. 중복이 발견되면 정리 작업 전에 어떤 행이 기준 레코드인지 원본 담당자와 확인합니다.

#N/A가 아닌 오류는 다른 항목을 확인

모든 오류를 한꺼번에 빈칸으로 바꾸기보다 오류 코드에 맞는 원인을 해결하는 편이 다음 문제도 드러냅니다. 일반 함수 사용법과 IFERROR의 범위는 Google Sheets 실무 함수 글에서 이어서 확인할 수 있습니다.

원인을 확인한 다음 IFNA로 사용자용 메시지 표시

누락 코드가 실제로 있을 수 있는 업무 표라면 조회 실패를 읽을 수 있는 문구로 바꿉니다. IFNA는 #N/A만 대체하므로 범위 오류 등 다른 문제는 그대로 드러납니다.

=IF(D2="","",IFNA(VLOOKUP(D2,$A$2:$C$4,3,FALSE),"코드 미등록"))

위 수식은 D2가 비어 있으면 빈칸, 코드가 표에 있으면 가격, 정확한 코드가 없으면 코드 미등록을 보여 줍니다. 이 메시지가 반복되면 오류를 숨길 때가 아니라 코드표에 키를 추가해야 하는지 확인할 때입니다.

=IF(D2="","",XLOOKUP(D2,$A$2:$A$4,$C$2:$C$4,"코드 미등록",0))

텍스트 정리 보조 열을 만들었다면 마지막 수식도 원본 범위 대신 정리된 키 열을 조회하도록 바꿉니다. 원본 수식과 정리 수식은 별도로 보존해 문제가 해결된 이유를 나중에 추적할 수 있게 하세요.

복구 전 마지막 30초 체크리스트

• 찾는 값과 원본 키 열이 같은 기준값을 가리키는가?
• VLOOKUP 네 번째 인수는 FALSE, XLOOKUP은 0인가?
• VLOOKUP 범위의 첫 열이 조회 키 열인가?
• 조회 범위가 새 행까지 포함하고, XLOOKUP 두 범위의 크기가 맞는가?
• 보조 열에서 앞뒤 일반 공백·NBSP를 양쪽 모두 정리했는가?
• 선행 0이 보존되어야 하는 식별자를 숫자로 변환하지 않았는가?
• 표시 결과가 맞더라도 중복 키와 첫 일치값을 확인했는가?
• 그 다음에만 IFNA를 적용했는가?

자주 묻는 질문

VLOOKUP에서 마지막 인수를 비워도 정확히 찾나요?

아닙니다. Google Sheets VLOOKUP은 이 인수를 생략하면 근사 일치인 TRUE가 기본값입니다. 정확한 ID 조회는 FALSE를 넣으세요.

XLOOKUP에도 FALSE를 넣어야 하나요?

아니요. XLOOKUP의 다섯 번째 인수는 match_mode이며 기본값 0이 정확 일치입니다. 수식 의도를 고정하려면 0을 명시해도 됩니다.

TRIM을 썼는데 왜 계속 #N/A인가요?

줄바꿈 없는 공백 같은 문자는 TRIM이 제거하지 않을 수 있습니다. 보조 열에서 SUBSTITUTE(A2,CHAR(160)," ")로 바꾸고 TRIM을 적용한 뒤 조회 키 양쪽에 같은 규칙을 쓰세요.

셀 표시 형식만 숫자로 바꾸면 해결되나요?

표시 형식만으로 저장된 자료형이 반드시 바뀌는 것은 아닙니다. ISNUMBER·ISTEXT로 값의 자료형을 검사하고 원본을 복사한 헬퍼 열에서 올바른 변환을 확인하세요.

IFERROR 대신 IFNA를 쓰는 이유는 무엇인가요?

누락 키만 메시지로 바꾸려면 IFNA가 범위·계산 오류를 덮지 않습니다. 다른 오류까지 숨기면 조회가 실패한 원인이 화면에서 사라질 수 있습니다.

중복 키가 있으면 왜 오류 대신 값이 나오나요?

조회 함수는 일치하는 키 중 첫 항목을 반환할 수 있습니다. 따라서 중복은 #N/A가 아니라 첫 번째 행의 잘못된 반환값으로 나타날 수 있습니다.

공식 자료와 다음 단계

함수 인수와 데이터 정리 동작은 Google 공식 고객센터를 기준으로 확인했습니다. 인터페이스 언어·계정 설정에 따라 메뉴 표시명은 다를 수 있습니다.

• Google Sheets VLOOKUP 도움말 — FALSE/TRUE, 범위 첫 열, 앞뒤 공백, 숫자·날짜 자료형
• Google Sheets XLOOKUP 도움말 — 기본 정확 일치, 누락값, 조회·반환 범위
• Google Sheets 데이터 정리: 공백 삭제 — NBSP는 공백 삭제로 처리되지 않음
• Google Sheets TRIM 도움말 · CHAR 도움말 · 함수 목록(SUBSTITUTE, ISNUMBER, ISTEXT, VALUE)
• Google Sheets CLEAN 도움말 — 비인쇄 ASCII 문자에 대한 제한
• Google Sheets IFNA 도움말 · COUNTIF 도움말 — 오류·중복 점검의 주의점

함수와 오류를 함께 익히기

• 구글 스프레드시트 실무 함수와 단축키 — XLOOKUP·FILTER·QUERY와 기본 계산 구조
• Google Sheets 업무 자동화 허브 — 실무 시트 가이드 모음
• IMPORTRANGE #REF! 오류 해결 — 다른 문서 연결과 접근 승인 문제

수식은 Google Sheets 공식 도움말을 기준으로 확인했습니다. 데이터 예시는 실서비스 데이터가 아닌 합성 코드입니다. 글 수정: 2026년 10월 3일.