XLOOKUP 실전 예제: 상품 코드로 이름 찾고 없는 값 처리하기

상품 코드를 입력하면 상품명을 자동으로 가져오는 작은 표를 만들어 보겠습니다. XLOOKUP의 핵심은 찾을 값, 검색할 범위, 반환할 범위를 분리하는 것입니다. 처음부터 복잡한 중첩을 쓰기보다 정상 값·없는 값·중복 값을 각각 시험하면 결과를 신뢰하기 쉬워집니다.

세 행의 표로 정확히 일치하는 값 찾기

A1에는 Code, B1에는 Item을 적고 A2:B4에 아래 자료를 입력합니다. E2에는 찾을 코드 A102를 입력하세요. 상품명은 그림과 수식을 대조하기 쉽도록 영어 예시를 유지합니다.

=XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"Not found",0)

결과는 Notebook입니다. 마지막 0은 정확히 일치하는 모드입니다. 기본값도 정확한 일치지만 여기서는 의도를 보이도록 명시했습니다. 달러 기호는 수식을 아래로 복사해도 참조 표가 이동하지 않게 합니다. E2는 다음 행에서 E3로 바뀔 수 있습니다.

없는 값과 중복은 서로 다른 문제

E2를 A999로 바꾸면 Not found가 나옵니다. 이 문구는 정상적인 미일치의 처리이며 수식의 모든 오류를 숨기는 장치가 아닙니다. 없음을 0으로 표시하면 실제 0과 구분하기 어려우므로 반환 자료의 의미에 맞게 정하세요.

코드 A102가 두 번 있으면 기본 검색은 첫 일치 항목을 반환합니다. 두 항목을 합하거나 최신값을 자동 판단하지 않습니다. 상품 코드는 유일해야 한다면 원본에서 중복을 먼저 검사하세요. 마지막 일치 검색은 별도 모드로 가능하지만, 행 순서가 최신 순서라는 보장이 있어야 의미가 있습니다.

찾지 못했을 때 범위보다 자료부터 점검

• 검색 범위와 반환 범위의 행 수와 시작·끝 행이 맞는지 봅니다.
• 숫자 코드와 텍스트 코드가 섞였는지 확인합니다.
• 앞뒤 공백이나 원본에서 붙은 보이지 않는 문자를 검사합니다.
• 코드를 고치기 전에 원본 값과 규칙을 확인합니다.

‘00123’과 숫자 123을 무조건 같은 값으로 변환하면 다른 상품을 섞을 수 있습니다. 검색 실패는 정리할 근거이지만 데이터 의미를 바꿀 허가는 아닙니다. 또한 XLOOKUP을 대소문자까지 구분하는 식별자 검증기로 사용하지 마세요. 그 조건이 필요하면 별도 검증 방법을 설계해야 합니다.

구버전과 합계 작업은 다른 선택

XLOOKUP은 Excel 2016과 2019에서 사용할 수 없습니다. Microsoft 365·Excel 2021·2024 등 지원 버전을 확인하세요. 이 예제는 구버전에서 다음처럼 INDEX와 MATCH의 정확한 일치로 바꿀 수 있습니다.

=INDEX($B$2:$B$4,MATCH(E2,$A$2:$A$4,0))

대체식은 없는 값에 #N/A를 반환하므로 필요한 미일치 처리를 별도로 정합니다. 같은 코드의 모든 수량을 합하려는 작업은 한 항목 검색과 다릅니다. 합계가 목적이면 SUMIF나 SUMIFS를 사용하세요. 정상 A102, 없는 A999, 중복 A102를 시험 파일에서 확인한 뒤 실제 표에 적용하면 기본 동작을 이해한 상태에서 사용할 수 있습니다.

공식 자료: Microsoft XLOOKUP, MATCH, INDEX. 함수 구분자는 지역 설정에 따라 쉼표 대신 세미콜론일 수 있습니다.

함께 읽으면 좋은 글

• 엑셀 앞자리 0과 16자리 번호 지키기: 숫자가 아닌 식별자로 저장하기
• SUMIFS로 월별·카테고리별 합계 구하기: 날짜 끝점을 놓치지 않는 수식