엑셀 VLOOKUP 및 XLOOKUP 함수 오류 해결법, 결과가 안 맞을 때 확인할 핵심 원인
엑셀 VLOOKUP 및 XLOOKUP 함수 오류 해결법, 결과가 안 맞을 때 확인할 핵심 원인
엑셀 VLOOKUP 및 XLOOKUP 함수 오류는 수식 자체보다 데이터 형식, 공백, 범위 지정에서 발생하는 경우가 많습니다. 대표 오류별 원인과 올바른 수정 수식을 익히면 복잡한 자료도 빠르게 점검할 수 있습니다.
엑셀 VLOOKUP 함수 기본 구조와 정확한 사용법
⭐ 정확한 일치를 원하면 마지막 인수에 FALSE를 입력해야 합니다
VLOOKUP의 기본 구문은 ‘=VLOOKUP(조회값, 표범위, 열번호, 일치방식)’입니다. 예를 들어 A2의 사번을 D2:F100의 첫 열에서 찾아 세 번째 열의 부서를 가져오려면 ‘=VLOOKUP(A2,$D$2:$F$100,3,FALSE)’를 사용합니다. 조회 대상은 지정한 표범위의 맨 왼쪽 열에 있어야 합니다. 오른쪽에 있는 항목만 반환할 수 있다는 구조적 제약도 알아두어야 합니다. 마지막 인수를 생략하면 기본적으로 근사 일치가 적용되므로 정렬되지 않은 명단에서는 엉뚱한 결과가 나올 수 있습니다. 사번·상품코드·이름처럼 완전히 같은 항목을 찾을 때는 FALSE 또는 0을 넣는 것이 안전합니다. 표범위를 복사해 사용할 계획이라면 F4 키로 절대참조를 설정해야 범위가 밀리지 않습니다. 수식이 정상인데 값이 틀리다면 먼저 열번호와 마지막 인수를 확인하세요.
엑셀 XLOOKUP 함수 기본 구조와 장점
XLOOKUP의 기본 형태는 ‘=XLOOKUP(조회값, 조회배열, 반환배열, 찾지못한경우)’입니다. A2의 코드를 D2:D100에서 검색해 F2:F100의 담당자를 표시하려면 ‘=XLOOKUP(A2,$D$2:$D$100,$F$2:$F$100,"없음")’으로 작성할 수 있습니다. XLOOKUP은 별도 설정을 하지 않아도 기본적으로 정확히 일치하는 항목을 찾습니다. 조회 열의 왼쪽에 있는 자료도 가져올 수 있으며, 열번호를 직접 세지 않아도 된다는 장점이 있습니다. 조회배열과 반환배열은 행 개수 또는 열 개수가 서로 같아야 합니다. 두 영역의 크기가 다르면 정상적인 계산이 어려우므로 시작 셀과 끝 셀을 함께 점검해야 합니다. 표의 열이 추가되거나 이동할 가능성이 크다면 반환 영역을 직접 지정하는 방식이 관리에 유리합니다. 다만 사용 중인 버전이 해당 기능을 지원하지 않으면 #NAME?이 표시될 수 있습니다.
엑셀 VLOOKUP XLOOKUP #N/A 오류 해결법
#N/A는 대부분 검색한 항목을 지정 영역에서 찾지 못했다는 뜻입니다. 눈으로 보기에는 같은 값이어도 한쪽이 숫자이고 다른 쪽이 문자이거나, 앞뒤에 보이지 않는 공백이 있으면 서로 다른 데이터로 판단됩니다. 먼저 검색값과 원본 셀의 형식이 숫자 또는 텍스트로 통일됐는지 확인해야 합니다. 불필요한 일반 공백은 ‘=TRIM(A2)’, 인쇄되지 않는 문자는 ‘=CLEAN(A2)’로 정리할 수 있습니다. 웹이나 외부 시스템에서 복사한 자료에는 TRIM만으로 지워지지 않는 특수 공백이 포함될 수도 있습니다. 숫자처럼 저장된 문자는 VALUE 함수나 곱하기 1을 이용해 실제 숫자로 변환할 수 있습니다. 반대로 자릿수를 유지해야 하는 코드는 TEXT 함수를 활용합니다. 문제가 해결된 뒤에만 IFNA를 적용해야 원인을 숨기지 않고 결과 화면을 정리할 수 있습니다. 예시는 ‘=IFNA(VLOOKUP(A2,$D$2:$F$100,3,FALSE),"미등록")’입니다.
- 공백 검사: LEN 함수로 정리 전후의 글자 수 비교
- 형식 검사: ISTEXT와 ISNUMBER로 문자·숫자 여부 확인
- 일치 검사: EXACT로 두 텍스트가 완전히 같은지 확인
엑셀 VLOOKUP #REF! #VALUE! 오류 수정법
⭐ 열번호와 지정 범위의 실제 열 개수를 비교하세요
#REF!는 유효하지 않은 셀을 참조할 때 나타납니다. VLOOKUP에서 표범위가 A:D까지 네 열인데 반환 열번호를 5로 입력하면 가져올 칸이 없어 문제가 발생합니다. 원본 열을 삭제하면서 수식의 참조가 깨진 경우에도 같은 표시가 나옵니다. 반환 열번호는 선택한 표범위의 전체 열 개수를 넘을 수 없습니다. #VALUE!가 보인다면 열번호에 1보다 작은 수를 썼는지, 잘못된 인수 형식을 넣었는지 살펴봅니다. 열 위치를 직접 세지 말고 범위를 선택한 뒤 상태와 시작 열을 함께 확인하면 실수를 줄일 수 있습니다. 외부 파일을 참조하는 계산이라면 파일명이나 경로 변경 여부도 점검합니다. 삭제된 참조는 오류를 감추기 전에 올바른 원본 범위로 다시 연결해야 합니다.
엑셀 XLOOKUP #NAME? #VALUE! #SPILL! 해결법
#NAME?은 함수명 오타, 정의되지 않은 이름 또는 현재 프로그램에서 지원하지 않는 기능 때문에 발생할 수 있습니다. 함수 자동완성 목록에 XLOOKUP이 나타나지 않는다면 설치된 제품과 업데이트 상태를 확인해야 합니다. 지원되지 않는 환경에서는 INDEX와 MATCH 조합을 대안으로 사용할 수 있습니다. #VALUE!가 발생하면 조회배열과 반환배열의 크기가 동일한지 먼저 비교하세요. 여러 열을 반환하는 동적 배열 수식에서 #SPILL!이 표시되면 결과가 펼쳐질 영역에 값, 병합 셀 또는 표 구조가 있는지 살펴봅니다. 예상 출력 영역을 비우고 병합을 해제하면 분산 결과가 정상적으로 나타날 수 있습니다.
- #N/A: 일치 항목, 공백, 자료 형식 확인
- #REF!: 삭제된 셀과 잘못된 열번호 점검
- #VALUE!: 인수 종류와 배열 크기 비교
- #NAME?: 철자, 지원 버전, 정의된 이름 확인
- #SPILL!: 출력 예정 구역의 장애 요소 제거
엑셀 VLOOKUP XLOOKUP 오류별 점검표
| 표시 내용 | 주요 원인 | 우선 조치 |
| #N/A | 값 불일치·숨은 공백 | 형식 통일·문자 정리 |
| #REF!·#VALUE! | 참조 손상·배열 크기 차이 | 영역과 인수 재지정 |
| #NAME?·#SPILL! | 미지원 기능·출력 방해 | 버전 확인·주변 셀 정리 |
오류 문구를 없애기 전에 원본 자료와 참조 관계부터 바로잡아야 합니다. 점검 순서는 조회값 존재 여부, 데이터 형식, 숨은 문자, 검색 영역, 반환 위치, 일치 설정 순으로 잡으면 효율적입니다. 수식 입력줄에서 각 범위에 표시되는 색상을 대조하면 잘못 선택한 구간을 빠르게 찾을 수 있습니다. 진단이 끝난 다음 IFNA 또는 IFERROR를 사용해야 실제 문제를 놓치지 않습니다. IFNA는 찾지 못한 경우만 처리하므로 조회 수식의 관리 목적에 더 적합할 수 있습니다. IFERROR는 여러 종류의 문제를 한꺼번에 감추므로 원인 분석 단계에서는 신중하게 적용해야 합니다.
엑셀 VLOOKUP 및 XLOOKUP 함수 오류 해결 결론
조회 수식의 문제는 무작정 다시 입력하기보다 오류 코드가 뜻하는 범위를 좁혀 해결하는 편이 빠릅니다. 정확한 일치 설정, 동일한 데이터 형식, 올바른 참조 범위가 핵심입니다. 기존 문서의 호환성이 중요하면 VLOOKUP을, 좌우 검색과 유지 관리 편의성이 필요하면 XLOOKUP을 검토하세요. 단, 후자는 이용 환경의 지원 여부를 먼저 확인해야 합니다. 오류 처리 함수는 진단을 끝낸 뒤 사용자에게 보기 좋은 문구를 표시하는 용도로 적용하는 것이 바람직합니다. 기능의 구문과 적용 대상은 Microsoft 지원 문서의 최신 안내를 함께 확인하면 안전합니다.
엑셀 VLOOKUP XLOOKUP 오류 FAQ
Q. 값이 분명히 있는데 #N/A가 나오는 이유는 무엇인가요?
A. 화면에 같은 내용으로 보여도 한쪽은 숫자, 다른 쪽은 문자로 저장됐거나 앞뒤에 공백과 인쇄되지 않는 문자가 포함됐을 가능성이 큽니다. ISTEXT, ISNUMBER, LEN으로 상태를 비교하고 TRIM, CLEAN, VALUE 등을 이용해 원본을 정리한 뒤 다시 계산하세요. 코드에 앞자리 0이 필요하다면 숫자로 바꾸지 말고 양쪽을 같은 텍스트 형식으로 맞춰야 합니다.
Q. VLOOKUP 결과가 틀리지만 오류 문구는 표시되지 않는 이유는 무엇인가요?
A. 네 번째 인수를 생략했거나 TRUE로 지정해 근사 일치가 실행됐을 가능성이 있습니다. 이 방식은 첫 열이 적절히 정렬됐다는 전제로 가장 가까운 항목을 찾기 때문에 명단이나 상품코드에서는 예상과 다른 결과가 나올 수 있습니다. 완전히 동일한 대상을 검색하려면 마지막 자리에 FALSE 또는 0을 입력하고 표범위의 첫 열도 확인하세요.
Q. XLOOKUP을 입력했는데 #NAME?이 나타나면 어떻게 하나요?
A. 먼저 함수명의 철자와 괄호를 확인하고 자동완성 목록에 해당 기능이 표시되는지 살펴보세요. 철자가 맞는데도 인식하지 못한다면 사용 중인 제품이 이를 지원하지 않거나 업데이트가 필요한 상황일 수 있습니다. 지원 환경을 확인할 수 없다면 ‘=INDEX(반환범위,MATCH(조회값,검색범위,0))’ 형태로 바꾸어 정확한 일치 결과를 구할 수 있습니다.
목차