코드를 입력하면 기준표에서 품목명이나 담당 부서를 가져오고 싶을 때 XLOOKUP을 사용할 수 있습니다. 코드 열과 결과 열을 지정하면 같은 행의 값을 반환하므로 이름을 매번 찾아 복사할 필요가 줄어듭니다. 다만 기준표에 없는 코드와 중복 코드를 어떻게 처리할지 먼저 정해야 합니다. 이 글은 Windows용 Microsoft 365와 Excel 2024·2021 데스크톱 기준입니다. XLOOKUP은 Excel 2016·2019에서 사용할 수 없으므로 파일을 받을 사람의 버전도 확인하세요.
찾을 코드와 기준표를 분리하세요
A2:A5에 품목 코드, B2:B5에 품목명을 적은 가상 기준표를 만들어 보겠습니다. 코드 P101은 연필, P102는 파일, P103은 지우개, P104는 메모지라고 정합니다. D2에는 사용자가 찾을 코드 P103을 입력하고 E2에 결과 수식을 넣습니다. 코드와 품목명 범위는 같은 행의 자료를 가리켜야 합니다. 코드 범위에서 제목을 제외했는데 결과 범위에는 제목을 포함하면 대응 관계가 어긋납니다. 먼저 각 범위의 시작 행과 끝 행을 나란히 확인하세요.
실제 사내 코드가 0012처럼 앞자리에 0을 포함하면 코드의 성격을 숫자가 아니라 식별자로 생각해야 합니다. Excel은 숫자로 입력한 앞자리 0을 제거할 수 있으므로 입력 전 해당 코드 열을 텍스트 형식으로 정하고 원래 표기대로 보관하는 것이 도움이 됩니다. 이미 0이 사라진 자료의 원래 코드를 임의로 추측해서 채우지 마세요. 기준표와 입력 셀에서 코드 표기가 일치하는지 원본 자료와 대조합니다. 화면의 숫자 서식으로만 0을 보이게 한 경우에도 실제 저장 값은 다를 수 있습니다.
정확한 일치와 미등록 문구를 지정하세요
- E2를 선택하고
=XLOOKUP(D2,$A$2:$A$5,$B$2:$B$5,"미등록",0)을 입력합니다. - 첫 번째 인수 D2는 찾을 코드입니다. 두 번째는 코드를 검색할 범위, 세 번째는 같은 행에서 반환할 품목명 범위입니다.
- 네 번째 인수 ‘미등록’은 찾는 코드가 없을 때 보여 줄 문구입니다. 다섯 번째 인수 0은 정확한 일치를 뜻합니다. 정확한 일치는 기본값이지만 예시에서는 확인하기 쉽도록 적었습니다.
- Enter를 누른 뒤 P103에 해당하는 ‘지우개’가 나오는지 확인합니다. 이어 D2를 P999로 바꾸어 ‘미등록’이 표시되는지도 검사합니다.
- 입력 코드가 여러 행이면 수식을 아래로 복사하세요. 기준 범위는 달러 기호로 고정했고 D2는 상대 참조이므로 다음 행에서는 D3의 코드를 찾습니다.
중복 코드가 있으면 먼저 기준표를 정리하세요
XLOOKUP의 기본 검색은 앞에서부터 시작해 첫 번째 일치를 반환합니다. 기준표에 P103이 두 번 들어 있고 품목명이 서로 다르다면 두 결과를 비교해 알려 주는 것이 아니라 먼저 찾은 값을 내놓습니다. 그래서 결과가 나온다고 해서 기준표가 정상이라고 판단할 수는 없습니다. 실제 업무에서는 같은 코드가 두 품목을 의미하는지, 잘못 복사된 중복 행인지 기준표 관리 담당자의 자료와 대조해야 합니다. 단순히 마지막 행의 값을 쓰겠다는 이유로 검색 방향만 바꾸면 자료 오류를 가릴 수 있습니다.
이 함수는 결과 열이 코드 열의 왼쪽에 있어도 사용할 수 있습니다. 열의 위치보다 찾는 범위와 반환 범위의 대응이 중요합니다. 반면 이진 검색 모드 2 또는 -2를 사용하면 정렬 조건이 따릅니다. 오름차순이나 내림차순 조건에 맞게 정렬되지 않은 자료에서는 올바르지 않은 결과가 나올 수 있습니다. 일반적인 소규모 코드표를 그대로 찾는 이 예시에서는 기본 검색 모드를 유지하세요. 근사 일치 모드 역시 코드 조회에 필요하다고 단정하지 말고 실제 기준표의 의미를 먼저 검토해야 합니다.
미등록 결과를 숨기지 말고 후속 확인에 쓰세요
네 번째 인수를 생략하면 찾지 못한 경우 NA 오류가 나타납니다. ‘미등록’처럼 업무에서 구분할 수 있는 문구를 넣으면 담당자가 해당 행을 확인하기 쉽습니다. 빈 문자열로 숨겨 버리면 기준표에 없는 코드와 빈 결과 값을 구별하기 어려울 수 있습니다. 다만 ‘미등록’이라는 정상 품목명이 기준표에 실제로 존재하는 상황이라면 다른 표시 문구를 선택하세요. 이 문구는 잘못 입력한 코드의 정답을 자동으로 추측하는 기능이 아닙니다.
공유하기 전에는 P101 같은 정상 코드, P999 같은 없는 코드, 기준표의 마지막 코드 세 가지를 차례로 넣어 결과를 확인하세요. 첫 행과 마지막 행은 범위를 한 칸 덜 잡았을 때 문제를 찾는 데 유용합니다. D2를 비운 상태의 결과도 실제 입력 정책에 따라 검토합니다. 빈 코드를 허용하지 않는 업무라면 입력 안내나 별도 점검을 두고, XLOOKUP의 결과 하나로 입력 완료를 판정하지 않습니다. 기준표에 새 행을 추가했다면 고정 범위의 끝 행도 해당 자료를 포함하도록 점검하세요.
완료 기준은 정상 코드가 올바른 품목명을 반환하고, 없는 코드는 미등록 표시가 나오며, 기준표의 코드가 중복되지 않았는지 확인한 상태입니다. 파일을 다른 사람에게 전달할 때에는 사용하는 Excel 버전을 확인하세요. Microsoft가 명시한 지원 범위 밖의 Excel 2016·2019에서 같은 수식이 작동한다고 안내하면 안 됩니다. 수식을 유지해 전달할지 결과 값을 별도로 전달할지도 받는 사람의 작업 목적에 맞게 정하면 됩니다.
공식 자료와 확인일
2026년 10월 4일 공식 본문 확인. 메뉴와 지원 제품은 설치 버전의 공식 안내를 확인해 주세요.