XLOOKUP으로 거래 코드를 찾을 때 정확히 일치와 근사값은 어떻게 다른가

발행

거래 코드에 맞는 주소를 XLOOKUP으로 가져올 때 결과 셀이 채워졌다는 사실만으로 올바르게 연결됐다고 판단하면 안 된다. 찾는 코드가 없는데 가까운 다른 코드를 반환하도록 설정했다면 정상처럼 보이는 주소가 잘못 붙을 수 있다. 식별자는 금액 구간표와 다르게 취급해야 한다.

Microsoft의 XLOOKUP 안내에서 match_mode 기본값 0은 정확히 일치하는 값을 찾는다. 일치값이 없으면 지정한 미발견 문구나 #N/A가 반환된다. -1은 정확 일치가 없을 때 다음 작은 값을, 1은 다음 큰 값을 찾는 방식이다. 이 근사 기능은 구간 기준표에는 쓰임이 있지만, 서로 다른 거래를 구별하는 코드에는 임의의 대체 연결이 될 수 있다.

가상 기준표에 코드 100과 200만 있고, 거래표에는 150이 있다고 하자. 정확 일치에서는 미발견으로 남아야 한다. 다음 작은 값을 허용하면 100의 주소를, 다음 큰 값을 허용하면 200의 주소를 가져올 수 있다. 150이 그 주소의 다른 표기라는 근거는 없다. 미발견을 빈칸으로 숨기기보다 ‘코드 확인 필요’처럼 상태를 드러내는 편이 검토에 도움이 된다.

수식의 선택 인수를 생략했는지, 특히 match_mode와 search_mode를 혼동하지 않았는지 살핀다. 이진 검색 모드 2 또는 -2는 조회 배열의 정렬 방향 조건이 있으며, 공식 문서는 정렬되지 않으면 잘못된 결과가 나올 수 있다고 경고한다. 단순히 큰 표를 빨리 찾기 위해 이 모드를 붙이기 전에 정렬 조건을 확인해야 한다. 기본 검색이 모두 정렬을 요구한다는 뜻은 아니다.

정확 일치도 기준표의 키 중복을 해결해 주지는 않는다. 같은 코드가 여러 행이면 첫 일치값 등의 검색 방향 규칙이 결과에 영향을 준다. 코드가 숫자인지 문자열인지와 앞자리 0의 의미도 원자료 정의대로 맞춘다. 공유 전에는 존재하는 코드, 없는 코드, 중복 코드 세 종류를 시험하고 미발견 건수를 기록한다. XLOOKUP을 지원하지 않는 Excel 2016·2019 환경 여부도 확인해, 파일을 받은 사람이 같은 수식을 계산할 수 있는지 점검한다.

출처와 확인일