엑셀에서 XLOOKUP 함수를 사용했는데 #N/A, #VALUE!, #SPILL! 같은 오류가 나타나거나 검색 결과가 예상과 다르게 표시되는 경우가 있습니다. 수식을 여러 번 수정해도 원인을 찾지 못하면 작업 시간이 길어집니다.
이럴 때 챗GPT를 활용하면 수식의 구조를 분석하고, 오류가 발생할 가능성이 있는 부분을 확인하며, 수정할 수식을 제안받을 수 있습니다.
이번 글에서는 XLOOKUP 함수의 기본 사용법부터 오류별 해결 방법, 챗GPT에 질문하는 방법, 실제 업무에 적용할 수 있는 예제까지 단계별로 알아보겠습니다.
XLOOKUP 함수란 무엇인가?
XLOOKUP은 엑셀에서 특정 값을 검색하고, 해당 값과 연결된 다른 열이나 행의 데이터를 가져오는 함수입니다.
예를 들어 상품코드를 입력하면 상품명이나 가격을 자동으로 표시할 수 있습니다. 직원번호로 직원 이름을 찾거나 주문번호로 배송 상태를 확인하는 작업에도 활용됩니다.
기본 수식은 다음과 같습니다.
=XLOOKUP(찾을값, 검색범위, 반환범위, [찾지못한경우], [일치방식], [검색방향])
각 인수의 의미는 다음과 같습니다.
| 인수 | 설명 |
|---|---|
| 찾을값 | 검색하려는 상품코드나 직원번호 |
| 검색범위 | 찾을값이 들어 있는 셀 범위 |
| 반환범위 | 검색 결과로 가져올 데이터 범위 |
| 찾지못한경우 | 검색 결과가 없을 때 표시할 내용 |
| 일치방식 | 정확히 일치하거나 근사한 값 검색 |
| 검색방향 | 위에서 아래 또는 아래에서 위로 검색 |
XLOOKUP은 기본적으로 정확히 일치하는 값을 검색합니다. 또한 검색범위의 왼쪽에 있는 열에서도 결과를 가져올 수 있어 기존 VLOOKUP보다 유연하게 사용할 수 있습니다.
다만 XLOOKUP은 Excel 2016과 Excel 2019에서 기본 지원되지 않습니다. Microsoft 365 및 Excel 2021 이후 지원 버전에서 사용할 수 있습니다.
실습용 데이터로 XLOOKUP 수식 만들기
다음과 같은 상품 목록이 있다고 가정하겠습니다.
| A열 상품코드 | B열 상품명 | C열 가격 |
|---|---|---|
| P101 | 무선마우스 | 15,000 |
| P102 | 키보드 | 32,000 |
| P103 | USB허브 | 18,000 |
| P104 | 노트북거치대 | 25,000 |
| P105 | 웹캠 | 49,000 |
이 표를 엑셀 A1 범위에 입력합니다.
E2 셀에 P103을 입력하고 F2 셀에 다음 수식을 작성합니다.
=XLOOKUP(E2,A2:A6,B2:B6)
실행 결과는 USB허브입니다.
상품 가격을 가져오려면 반환범위를 C열로 변경합니다.
=XLOOKUP(E2,A2:A6,C2:C6)
실행 결과는 18000입니다. 셀에 천 단위 구분 형식을 적용하면 18,000으로 표시됩니다.
검색범위와 반환범위를 정확히 지정하면 상품코드를 변경할 때마다 결과가 자동으로 바뀝니다.
XLOOKUP에서 자주 발생하는 오류 5가지
| 오류 표시 | 주요 원인 | 확인할 사항 |
|---|---|---|
| #N/A | 일치하는 값을 찾지 못함 | 검색값, 공백, 데이터 형식 |
| #VALUE! | 인수 또는 배열 크기 문제 | 검색범위와 반환범위 |
| #SPILL! | 배열 결과를 표시할 공간 부족 | 결과 셀 주변 데이터 |
| #NAME? | 함수 이름을 인식하지 못함 | 엑셀 버전, 함수 철자 |
| #REF! | 잘못된 셀 참조 | 삭제된 셀, 외부 파일 참조 |
오류가 표시되었다고 해서 반드시 함수 자체가 잘못된 것은 아닙니다. 검색 데이터의 형식이나 셀 배치가 원인일 수도 있습니다.
#N/A 오류 해결 방법
#N/A는 검색값과 일치하는 데이터를 찾지 못했을 때 발생하는 대표적인 오류입니다.
예를 들어 E2 셀에 P109를 입력했지만 상품 목록에는 P109가 없다면 다음 수식은 #N/A를 반환합니다.
=XLOOKUP(E2,A2:A6,B2:B6)
검색 결과가 없을 때 오류 대신 안내 문구를 표시하려면 네 번째 인수를 사용합니다.
=XLOOKUP(E2,A2:A6,B2:B6,"상품 없음")
이제 일치하는 상품코드가 없으면 상품 없음이 표시됩니다.
검색값이 실제로 존재하는데도 #N/A가 발생한다면 다음 사항을 확인합니다.
- 검색값 앞뒤에 불필요한 공백이 있는지 확인합니다.
- 숫자가 문자로 저장되어 있는지 확인합니다.
- 상품코드에 오타가 있는지 확인합니다.
- 검색범위가 전체 데이터를 포함하는지 확인합니다.
공백 때문에 검색이 실패한다면 다음 수식을 시도할 수 있습니다.
=XLOOKUP(TRIM(E2),A2:A6,B2:B6,"상품 없음")
TRIM 함수는 E2의 일반적인 앞뒤 공백과 중복 공백을 정리합니다. 다만 검색범위에 있는 상품코드 자체에 공백이 포함되어 있다면 원본 데이터도 정리해야 합니다.
숫자와 문자 형식이 혼합된 경우에는 검색값과 검색범위의 형식을 일치시키는 것이 중요합니다. 특히 앞에 0이 붙는 상품코드나 직원번호는 문자 형식으로 관리하는 편이 안전합니다.
#VALUE! 오류 해결 방법
#VALUE! 오류는 XLOOKUP에 전달한 인수나 배열의 형태가 올바르지 않을 때 발생할 수 있습니다.
대표적으로 검색범위와 반환범위의 크기가 서로 맞지 않는 경우가 있습니다.
잘못된 수식 예시입니다.
=XLOOKUP(E2,A2:A6,B2:B5)
검색범위는 5개 행이지만 반환범위는 4개 행입니다.
올바른 수식은 다음과 같습니다.
=XLOOKUP(E2,A2:A6,B2:B6)
두 범위의 행 수를 일치시키면 정상적으로 검색할 수 있습니다.
챗GPT에는 다음과 같이 질문할 수 있습니다.
“엑셀 XLOOKUP 수식에서 #VALUE! 오류가 발생합니다. 수식은 =XLOOKUP(E2,A2,B2)입니다. 오류 원인과 수정 수식을 알려주세요.”
챗GPT가 수정 수식을 제안하면 실제 데이터 범위와 일치하는지 확인한 후 적용합니다.
#SPILL! 오류 해결 방법
#SPILL! 오류는 수식이 여러 셀에 결과를 표시하려고 하지만 필요한 공간을 확보하지 못했을 때 발생합니다.
예를 들어 다음 수식을 사용하면 상품명과 가격을 동시에 반환할 수 있습니다.
=XLOOKUP(E2,A2:A6,B2:C6)
E2에 P103이 입력되어 있다면 수식을 작성한 셀부터 오른쪽 두 칸에 다음 결과가 표시됩니다.
| 상품명 | 가격 |
|---|---|
| USB허브 | 18,000 |
그런데 두 번째 결과가 표시될 셀에 이미 다른 데이터가 있다면 #SPILL! 오류가 발생할 수 있습니다.
해결 방법은 다음과 같습니다.
- #SPILL! 오류가 표시된 셀을 선택합니다.
- 엑셀이 표시하는 점선 범위를 확인합니다.
- 결과가 표시될 공간에 다른 값이 있는지 확인합니다.
- 불필요한 데이터를 이동하거나 삭제합니다.
- 병합된 셀이 있다면 병합을 해제합니다.
동적 배열 수식은 엑셀 표 내부에서 확장되지 않을 수 있으므로 필요한 경우 표 바깥쪽에 작성합니다.
기존 데이터를 삭제하기 전에는 반드시 필요한 자료인지 확인해야 합니다.
#NAME? 오류 해결 방법
#NAME? 오류는 엑셀이 수식에 입력된 함수 이름이나 정의된 이름을 인식하지 못할 때 발생합니다.
예를 들어 함수 이름을 잘못 입력하면 오류가 발생할 수 있습니다.
잘못된 수식:
=XLOKUP(E2,A2:A6,B2:B6)
올바른 수식:
=XLOOKUP(E2,A2:A6,B2:B6)
함수 이름이 정확한데도 오류가 발생한다면 사용 중인 엑셀 버전을 확인합니다.
XLOOKUP을 지원하지 않는 엑셀에서는 INDEX와 MATCH 함수를 조합해 비슷한 검색 작업을 수행할 수 있습니다.
=INDEX(B2:B6,MATCH(E2,A2:A6,0))
이 수식은 A열에서 상품코드를 정확히 검색한 후 해당하는 B열의 상품명을 반환합니다.
단, 검색값이 없으면 #N/A가 발생할 수 있습니다.
#REF! 오류 해결 방법
#REF! 오류는 수식에서 참조하던 셀이나 범위가 유효하지 않을 때 발생합니다.
예를 들어 XLOOKUP 수식이 참조하던 열을 삭제하거나, 다른 파일에 있는 데이터를 참조하는 과정에서 연결이 잘못되면 오류가 나타날 수 있습니다.
다음과 같은 수식은 참조가 손상된 상태의 예시입니다.
=XLOOKUP(E2,A2:A6,#REF!)
이 경우 반환범위를 실제 존재하는 셀 범위로 다시 지정해야 합니다.
=XLOOKUP(E2,A2:A6,B2:B6)
오류를 수정할 때는 원본 데이터가 삭제되었는지, 시트 이름이 변경되었는지, 외부 파일 연결에 문제가 있는지 함께 확인하는 것이 좋습니다.
챗GPT로 XLOOKUP 오류 분석하는 순서
챗GPT를 활용할 때는 단순히 “엑셀 오류를 해결해 줘”라고 질문하는 것보다 실제 수식과 데이터 구조를 함께 전달하는 것이 효과적입니다.
첫 번째 단계는 오류가 발생한 수식을 복사하는 것입니다.
예를 들어 다음 수식에서 오류가 발생했다고 가정하겠습니다.
=XLOOKUP(E2,A2:A10,C2:C8)
두 번째 단계는 챗GPT에 오류 내용과 셀 범위를 설명하는 것입니다.
다음 질문을 복사해 사용할 수 있습니다.
“엑셀에서 XLOOKUP 함수를 사용하고 있습니다. 검색값은 E2에 있고, 검색범위는 A2, 반환범위는 C2입니다. #VALUE! 오류가 발생하는 이유를 분석하고 정상 작동하는 수식으로 수정해 주세요. 수정 이유도 초보자가 이해할 수 있도록 설명해 주세요.”
세 번째 단계는 챗GPT가 제안한 수식을 확인하는 것입니다.
이 사례에서는 검색범위와 반환범위의 행 수가 다르므로, 실제 데이터가 C10까지 존재한다는 전제에서 다음처럼 수정할 수 있습니다.
=XLOOKUP(E2,A2:A10,C2:C10)
네 번째 단계는 엑셀에 수정 수식을 입력하고 검색값을 바꾸어 테스트하는 것입니다.
다섯 번째 단계는 정상적으로 검색되는 값뿐 아니라 검색되지 않는 값도 입력하여 오류 처리 결과를 확인하는 것입니다.
챗GPT는 제공된 수식과 설명을 바탕으로 오류를 분석합니다. 실제 엑셀 파일을 확인하지 않은 경우에는 데이터의 숨은 공백, 형식 차이, 외부 참조 상태까지 확정할 수 없으므로 최종 검증은 엑셀에서 해야 합니다.
여러 개의 XLOOKUP 오류를 한 번에 점검하는 방법
실무에서는 수십 개 또는 수백 개의 셀에 XLOOKUP 수식이 사용되기도 합니다.
이럴 때는 오류가 발생한 셀의 수식 몇 개를 모아 챗GPT에 전달하거나, 파일 업로드 기능을 사용할 수 있는 환경이라면 엑셀 파일을 첨부하여 분석을 요청할 수 있습니다.
질문 예시는 다음과 같습니다.
“첨부한 엑셀 파일에서 XLOOKUP 함수가 사용된 셀을 확인하고, 오류가 발생한 셀 주소와 오류 유형, 예상 원인, 수정할 수식을 정리해 주세요. 원본 데이터는 변경하지 말고 수정이 필요한 부분을 먼저 알려주세요.”
파일 분석 기능을 이용할 수 있다면 반복되는 오류의 공통 원인을 찾는 데 도움이 됩니다.
다만 고객 이름, 연락처, 주민등록번호, 계좌번호 등 개인정보나 회사의 기밀자료는 업로드하기 전에 제거하거나 익명화해야 합니다.
또한 AI가 수정한 수식은 원본 파일의 복사본에서 먼저 검증하는 것이 안전합니다.
XLOOKUP을 더 편리하게 사용하는 실무 수식
XLOOKUP 오류를 해결한 다음에는 다음 수식을 활용해 작업을 효율적으로 관리할 수 있습니다.
| 작업 목적 | 수식 |
|---|---|
| 상품명 검색 | =XLOOKUP(E2,A2:A6,B2:B6) |
| 가격 검색 | =XLOOKUP(E2,A2:A6,C2:C6) |
| 검색 실패 안내 | =XLOOKUP(E2,A2:A6,B2:B6,"상품 없음") |
| 마지막 일치값 검색 | =XLOOKUP(E2,A2:A6,B2:B6,"상품 없음",0,-1) |
| 상품명과 가격 동시 검색 | =XLOOKUP(E2,A2:A6,B2:C6) |
마지막 일치값 검색 수식은 같은 상품코드가 여러 번 등장할 때 아래쪽에 있는 마지막 일치 항목을 반환합니다.
단, 마지막으로 입력한 데이터가 반드시 가장 최신 데이터라는 의미는 아닙니다. 최신 기록을 찾으려면 날짜나 입력 순서도 확인해야 합니다.
상품명과 가격을 동시에 반환하는 수식은 결과가 표시될 오른쪽 셀까지 비어 있어야 정상 작동합니다.
XLOOKUP 오류를 예방하는 데이터 관리 방법
XLOOKUP 오류는 수식 자체보다 원본 데이터의 관리 상태 때문에 발생하는 경우도 있습니다.
상품코드나 직원번호처럼 검색 기준으로 사용하는 값은 입력 형식을 통일하는 것이 좋습니다.
특히 숫자와 문자가 혼합된 데이터, 불필요한 공백, 중복된 검색값은 예상하지 못한 결과를 만들 수 있습니다.
데이터가 계속 추가되는 업무라면 검색범위를 고정된 셀 주소로 관리하는 대신 엑셀 표 기능을 활용하는 방법도 있습니다.
예를 들어 상품 목록을 엑셀 표로 변환하고 표 이름을 상품표로 지정했다면 다음과 같은 구조적 참조 수식을 사용할 수 있습니다.
=XLOOKUP(E2,상품표[상품코드],상품표[상품명],"상품 없음")
표에 새로운 상품을 추가하면 참조 범위가 자동으로 확장되어 관리가 편리해집니다.
다만 표 이름과 열 제목은 실제 엑셀 파일에 지정된 이름과 정확히 일치해야 합니다.
자주 묻는 질문
Q. XLOOKUP에서 #N/A가 나타나면 무조건 데이터가 없는 것인가요?
아닙니다. 검색값이 존재하더라도 숫자와 문자 형식이 다르거나 불필요한 공백이 포함되어 있으면 검색에 실패할 수 있습니다.
Q. 챗GPT가 알려준 수식을 그대로 사용해도 되나요?
수식 구조를 참고할 수 있지만 실제 파일의 셀 위치와 데이터 형식이 맞는지 확인해야 합니다. 특히 원본 데이터가 복잡하거나 여러 시트를 참조하는 경우에는 테스트가 필요합니다.
Q. XLOOKUP은 VLOOKUP보다 좋은가요?
XLOOKUP은 검색 방향이 자유롭고 정확히 일치하는 검색이 기본이며, 검색 실패 시 표시할 값을 지정할 수 있다는 장점이 있습니다. 다만 오래된 엑셀 버전과 파일을 공유해야 한다면 호환성을 고려해야 합니다.
Q. 검색 결과가 틀리게 나오는 이유는 무엇인가요?
중복된 검색값이 있거나 검색범위와 반환범위가 잘못 지정되었을 수 있습니다. 기본 XLOOKUP은 첫 번째 일치 항목을 반환하므로 중복 데이터가 있는지 확인해야 합니다.
Q. 챗GPT에 엑셀 파일을 올리면 모든 오류를 자동으로 수정하나요?
파일 분석과 수식 수정 제안을 요청할 수 있지만, 실제 분석 범위와 수정 가능 여부는 파일 구조와 사용 가능한 기능에 따라 달라집니다. 자동 수정 결과도 반드시 검증해야 합니다.
XLOOKUP 오류 해결 시 기억할 사항
XLOOKUP 오류를 해결하려면 오류 메시지뿐 아니라 검색값, 검색범위, 반환범위, 데이터 형식을 함께 확인해야 합니다.
#N/A는 검색값과 데이터 형식을, #VALUE!는 인수와 범위 크기를, #SPILL!은 결과가 표시될 공간을 먼저 점검하는 것이 효율적입니다.
챗GPT에는 실제 수식과 오류 메시지, 데이터가 배치된 위치를 구체적으로 알려주면 보다 정확한 수정 방법을 제안받을 수 있습니다.
중요한 업무 파일은 원본을 보관하고 복사본에서 수정 수식을 검증한 뒤 적용하는 것이 좋습니다.
공식 참고자료
XLOOKUP 함수의 인수와 검색 방식, 오류별 해결 방법은 Microsoft의 공식 지원 문서에서 확인할 수 있습니다.
확인하기 : Microsoft XLOOKUP 함수 공식 설명
답글 남기기