자료 확인일: 2026-09-21
XLOOKUP은 찾을 값과 검색할 범위, 가져올 범위를 지정하는 조회 함수입니다.
기본값은 정확 일치이므로 상품코드처럼 동일한 값을 찾아야 하는 작업에 사용할 수 있습니다.
Microsoft 365 및 지원되는 최신 영구 버전에서 제공되지만, Excel 2016·2019에서는 사용할 수 없습니다.[1]
1. 예제 데이터를 붙여넣고 첫 조회 만들기
아래는 설명을 위해 만든 가상 상품 데이터입니다.
실제 판매 데이터가 아닙니다.
코드 블록을 복사해 빈 시트의 A1에 붙여넣으세요.
탭으로 구분되어 있습니다.
상품코드 상품명 단가 P101 정리함 12000 P102 케이블 8500 P103 노트 4500 P104 파우치 16000 P102 케이블 신규 9000
E2에 P103을 입력하고 F2에 다음 수식을 넣습니다.
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,"미등록")
이 예제의 기대 결과는 노트입니다.
E2를 P999로 바꾸면 미등록이 나와야 합니다.
아래는 수식을 읽는 순서입니다.
| 부분 | 이 예제에서 뜻하는 것 |
|---|---|
| E2 | 조회할 상품코드 |
| $A$2:$A$6 | 코드가 있는 열 |
| $B$2:$B$6 | 같은 행에서 가져올 상품명 |
| “미등록” | 일치하는 코드가 없을 때 표시할 문구 |
네 번째 인수는 찾는 값이 없을 때의 표시입니다.
수식의 모든 오류를 없애는 장치로 이해하면 안 됩니다.[1]
2. 단가 조회와 중복 코드 처리
상품명 대신 단가를 가져오려면 반환 범위만 C열로 바꿉니다.
=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"미등록")
E2가 P102라면 기대 결과는 8,500입니다.
샘플에 P102가 두 번 있지만 기본 검색은 위에서부터 처음 일치한 항목을 반환하기 때문입니다.[1]
현재 데이터에서 코드가 몇 번 나타나는지 먼저 확인할 수도 있습니다.
COUNTIF는 지정한 조건과 일치하는 셀의 개수를 셉니다.[2]
=COUNTIF($A$2:$A$6,E2)
P102의 기대 결과는 2입니다.
이때는 함수를 더 복잡하게 만들기 전에 업무 기준을 정해야 합니다.
| 코드 개수 | 확인할 사항 | 이 예제의 처리 방향 |
|---|---|---|
| 0개 | 신규 코드인지, 입력이 잘못됐는지 | 미등록 목록으로 분리 |
| 1개 | 해당 행이 유효한 기준정보인지 | 일반 조회 |
| 2개 이상 | 중복 오류인지, 이력이 쌓인 것인지 | 기준일·우선순위부터 결정 |
마지막 행을 가져오겠다는 기준이 분명하다면 역방향 검색을 사용할 수 있습니다.
=XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"미등록",0,-1)
P102의 기대 결과는 9,000입니다.
여기서 0은 정확 일치, -1은 뒤에서부터 검색입니다.[1]
마지막 행이 최신 자료라는 뜻은 아닙니다.
날짜를 정렬하지 않은 이력표라면 마지막 행의 값이 최신 단가인지 별도로 판단해야 합니다.
3. 값이 안 나올 때의 점검 순서
| 화면에서 보이는 문제 | 먼저 할 일 | 피할 일 |
|---|---|---|
| 함수 자체를 인식하지 못함 | 지원되는 Excel 버전인지 확인 | 원본 값을 무작정 수정 |
| 분명 있는 코드인데 미등록 | 입력값을 원본 셀에서 복사해 재시험 | 미등록을 모두 숫자 0으로 변경 |
| 엉뚱한 단가가 나옴 | 코드 중복과 반환 열 확인 | 중복 기준 없이 마지막 값 채택 |
| 일부 코드만 조회되지 않음 | 앞뒤 공백·문자 형식을 별도 열에서 점검 | 모든 코드를 숫자로 일괄 변환 |
| 수식 복사 뒤 결과가 바뀜 | 검색/반환 범위가 같이 이동했는지 확인 | 결과 셀만 덮어쓰기 |
일반적인 앞뒤 공백을 확인할 때는 원본을 보존하고 보조 열에서 정리합니다.
=TRIM(A2)
TRIM은 일반 공백을 정리하지만, 웹에서 복사된 비분리 공백 등 모든 종류의 문자를 제거하지는 않습니다.[3]
또한 0012처럼 앞자리 0에 의미가 있는 코드를 숫자 12로 바꾸면 다른 코드 체계가 됩니다.
먼저 해당 열을 식별자로 관리할지, 계산할 숫자로 관리할지 정하세요.
4. 업무 파일에 적용하기 전 작은 검수표
아래 기대값은 제공한 가상 데이터와 공개된 함수 동작을 대조한 값입니다.
실제 Excel 환경에서의 실행 결과와 구분해 사용하세요.
| 입력/수식 | 기대 결과 | 확인 목적 |
|---|---|---|
| P103 → 상품명 조회 | 노트 | 정상 매칭 |
| P999 → 상품명 조회 | 미등록 | 없는 코드 |
| P102 → 기본 단가 조회 | 8500 | 첫 일치 값 |
| P102 → 역방향 단가 조회 | 9000 | 마지막 일치 값 |
| P102 → COUNTIF | 2 | 중복 탐지 |
정상 코드·미등록 코드·중복 코드를 각각 한 번씩 통과시킨 뒤 실제 자료에 적용하면, 수식이 작동하는 것과 업무 기준이 맞는 것을 나눠 확인할 수 있습니다.
답글 남기기