엑셀 XLOOKUP으로 두 표의 코드와 항목 맞추는 방법

이름을 보고 옮기는 작업이 틀어지는 이유

예를 들어 요청 목록에 적힌 자재 코드를 보고 기준표에서 자재명을 찾아 옮기는 상황을 가정한다. 이름을 눈으로 검색하면 띄어쓰기나 비슷한 표기 때문에 다른 항목을 선택할 수 있다. 코드가 같으면 같은 항목이라는 규칙을 먼저 정하는 이유다.

이 글에서는 요청 시트의 B열에 조회할 코드가 있다고 가정한다. 기준 시트는 A열이 코드, B열이 자재명이다. 첫 행에는 제목을 넣고 그다음 행부터 데이터를 입력한다. 코드에 쓰는 숫자와 수식의 범위 끝 행은 모두 설명을 위한 예시 숫자다.

기준표는 코드 하나에 자재명 하나가 연결되어야 한다. 동일한 코드에 서로 다른 이름이 들어 있으면 수식을 넣기 전에 기준표를 정리한다. 조회 함수가 값을 반환한다는 사실만으로 연결이 올바르다고 판단할 수는 없다.

앞자리에 영이 있는 코드는 양쪽 표에서 모두 텍스트로 관리한다. 이미 숫자로 바뀌어 영이 사라졌다면 셀 표시 형식만 바꿔도 원래 코드가 복구되지는 않는다. 원래 코드 규칙을 확인한 뒤 값을 바로잡아야 한다.

조회와 중복 점검 수식을 나누어 넣기

요청 시트 C2에 다음 수식을 넣는다. B2가 비어 있으면 결과도 비워 두고, 코드가 없으면 확인할 문구를 남긴다.

=IF(B2="","",XLOOKUP(B2,기준!$A$2:$A$1000,기준!$B$2:$B$1000,"미등록",0))

마지막 인수인 0은 정확히 일치하는 코드를 찾도록 지정한다. 조회 범위와 반환 범위는 시작 행과 끝 행을 맞춘다. 달러 기호는 수식을 아래로 복사해도 기준표 범위가 움직이지 않도록 고정한다.

D2에는 코드가 기준표에 몇 번 들어 있는지 표시한다.

=IF(B2="","",COUNTIF(기준!$A$2:$A$1000,B2))

결과가 0이면 미등록 코드다. 1이면 연결 후보가 하나다. 1보다 크면 중복을 확인한다. 이 수식의 코드는 별표나 물음표 같은 와일드카드 문자를 포함하지 않는다고 가정한다. 그런 문자가 실제 코드에 쓰이면 COUNTIF의 조건 처리부터 조정해야 한다.

Microsoft 지원의 XLOOKUP 안내에서 함수의 인수와 지원 환경을 확인할 수 있다. 사용 중인 엑셀이 함수를 인식하는지도 사본에서 먼저 확인한다.

수식을 입력한 뒤 필요한 마지막 행까지만 복사한다. 첫 행의 결과만 맞는다고 전체 연결이 맞는 것은 아니다. 위쪽과 아래쪽에서 서로 다른 코드를 골라 기준표의 실제 행과 대조한다.

결과는 찾은 값보다 예외 목록으로 확인하기

가정한 시험 데이터에는 정상 코드, 없는 코드, 중복 코드, 빈 코드를 각각 준비한다. 정상 코드는 자재명을 반환해야 한다. 없는 코드는 미등록으로 표시되어야 하고, 빈 코드는 결과 칸이 비어 있어야 한다.

중복 코드는 조회 결과가 나와도 검토 대상으로 남긴다. 기본 조회에서는 같은 코드가 여러 개 있으면 앞쪽 일치 항목이 선택된다. 순서를 바꾸었을 때 결과가 달라질 수 있는 기준표는 안정적인 기준표가 아니다.

자동화로 바뀌는 작업은 자재명을 하나씩 찾아 옮기는 과정이다. 사람이 맡을 일은 미등록과 중복을 해결하는 일로 좁아진다. 다만 여기서는 실행 시간을 측정하지 않았으므로 절감 시간을 숫자로 제시하지 않는다.

효과를 확인하려면 같은 요청 목록을 두고 수작업과 수식 적용에 걸린 시간을 따로 기록한다. 수식 입력 시간뿐 아니라 기준표 수정과 예외 확인 시간도 포함해야 비교가 공정하다.

연결이 깨지는 조건과 마무리 점검

코드 앞뒤의 공백, 숫자와 텍스트의 혼용, 기준표 범위를 벗어난 새 행이 대표적인 실패 원인이다. 결과가 미등록이면 함수를 바꾸기 전에 양쪽 셀의 실제 값과 데이터 형식을 확인한다.

반환 대상인 자재명 셀이 비어 있으면 조회 결과에 0이 나타날 수 있다. 이를 미등록 코드와 혼동하지 않는다. 코드 존재 여부는 D열의 개수로 확인하고, 이름 누락은 기준표에서 별도로 수정한다.

대소문자만으로 서로 다른 코드를 구분하는 목록에도 그대로 적용하면 안 된다. 일반적인 XLOOKUP 조회는 대소문자를 구별하는 검증 수단이 아니다. 코드 체계에 그런 구분이 있는지 먼저 확인한다.

데이터가 늘어나면 범위 끝을 함께 조정한다. 한쪽 범위만 늘리거나 반환 열을 잘못 선택하지 않았는지도 본다. 반복적으로 행이 추가된다면 기준표를 엑셀 표로 관리하는 방식도 검토한다.

마지막으로 미등록 표시가 사라졌는지보다, 왜 사라졌는지를 확인한다. 임의의 이름을 채워 넣어 경고만 없애면 오류가 숨는다. 기준표의 책임자가 확인한 연결만 실제 업무에 반영한다.