잘 되던 XLOOKUP 수식이 갑자기 #NAME? 에러로 바뀌는 이유는 무엇인가요?
엑셀 2016, 2019 이하 구버전이나 영구 버전(LTSC) 환경에서는 XLOOKUP 함수를 인식하지 못하기 때문입니다.
최신 365 버전 엑셀을 사용하는 내 컴퓨터에서는 완벽하게 돌아가던 파일이 타 부서나 협력업체 컴퓨터에만 가면 #NAME? 오류 메시지로 바뀔 때가 있습니다. 엑셀 프로그램이 XLOOKUP이라는 함수 이름 자체를 모르는 단어로 판단해 내뱉는 에러입니다.
실무에서 구버전 엑셀(2016)을 쓰는 타 부서에 XLOOKUP 수식이 담긴 파일을 공유했다가 전산 에러로 수식이 전부 깨졌다는 연락을 받고, 급히 INDEX와 MATCH 조합으로 전체 수정해 다시 전달했던 적이 있습니다.
회사 내 모든 직원이 동일한 최신 버전 엑셀을 쓰지 않는다면, 언제 어디서나 호환되는 INDEX+MATCH 조합을 사용하는 것이 훨씬 안전합니다.
INDEX와 MATCH를 조합할 때 사람들이 가장 많이 하는 순서 착각은 무엇인가요?
VLOOKUP처럼 '찾을 값'을 먼저 쓰는 것이 아니라, 내가 '최종적으로 가져올 결과 범위'를 INDEX 안에 가장 먼저 넣어야 합니다.
대부분의 조회 함수는 "누구를 찾을 것인가"로 시작합니다. VLOOKUP도, XLOOKUP도 첫 번째 칸에는 항상 내가 찾고 싶은 기준값(예: 사번, 이름)을 입력합니다. 그래서 INDEX+MATCH 수식을 처음 짤 때도 습관적으로 기준값부터 찍는 실수가 자주 나옵니다.
INDEX+MATCH는 동작 방식이 반대입니다.
- "내가 가져오고 싶은 진짜 데이터 범위가 어디인가?" (INDEX의 역할)
- "그 데이터 범위에서 몇 번째 줄 값을 가져올 것인가?" (MATCH의 역할)
이 두 가지를 순서대로 연결하는 개념입니다. 꺼내오고 싶은 결과 보관함(INDEX)을 먼저 열어두고, 그 보관함 안에서 몇 번째 칸을 꺼낼지 주소표(MATCH)를 붙여준다고 생각하면 이해가 쉽습니다.
XLOOKUP 수식을 INDEX+MATCH 수식으로 1초 만에 바꾸려면 어떻게 써야 하나요?
=INDEX(가져올범위, MATCH(찾을값, 찾을범위, 0)) 공식 하나만 적용하면 XLOOKUP과 똑같이 작동합니다.
XLOOKUP 수식에 적어두었던 인자 3개의 위치를 그대로 옮겨 적기만 하면 됩니다. 아래 비교표를 보면 위치 대응 관계를 한눈에 파악할 수 있습니다.
| XLOOKUP 요소 | INDEX+MATCH 대응 위치 | 역할 설명 |
|---|---|---|
| 가져올범위 (3번째 인자) | INDEX( 가져올범위 , ... ) | 최종적으로 화면에 출력할 데이터 열 |
| 찾을값 (1번째 인자) | MATCH( 찾을값 , ... ) | 검색 기준이 되는 데이터 (예: 사번 셀) |
| 찾을범위 (2번째 인자) | MATCH( ... , 찾을범위 , 0) | 기준 데이터가 들어있는 전체 열 |
| 정확하게 일치 | MATCH( ... , 0 ) | 정확히 일치하는 값만 찾기 위한 필수 숫자 |
예를 들어 B열에서 사번 A2를 찾아 C열의 이름을 가져오는 XLOOKUP 수식이 =XLOOKUP(A2, B:B, C:C) 라면, 이를 바꾸한 INDEX+MATCH 수식은 =INDEX(C:C, MATCH(A2, B:B, 0)) 이 됩니다. 맨 뒤에 있던 C:C가 맨 앞으로 이동한다는 점만 기억해 두세요.
💡 수식 순서가 헷갈려 에러가 난다면 클릭 한 번으로 작성해 보세요.
복잡한 인자 배치를 일일이 외우지 않아도 내 표에 딱 맞는 수식을 빠르게 조합할 수 있습니다. 엑셀 수식 생성기를 이용해 보세요.
수식을 잘 썼는데도 엉뚱한 값이 나온다면 MATCH 함수 맨 뒤에 무엇을 놓쳤나요?
MATCH 함수 마지막 인자에 정확히 일치를 뜻하는 '0'을 빠뜨리면 오름차순 정렬이 안 된 표에서 전혀 다른 행 번호를 불러옵니다.
수식에 오류 메시지(#N/A 나 #NAME?)는 안 뜨는데 완전히 다른 직원의 이름이나 엉뚱한 수치가 출력되는 경우가 있습니다. 이 현상의 90% 이상은 MATCH 함수의 세 번째 옵션인 , 0을 누락했기 때문에 일어납니다.
MATCH 함수에서 마지막 옵션을 생략하면 엑셀은 기본값을 1로 인식합니다. 1은 "데이터가 오름차순으로 예쁘게 정렬되어 있을 때 비슷하게 일치하는 값을 찾으라"는 의미입니다.
하지만 실무에서 다루는 대부분의 문서(급여대장, 인사기록부, 매출표)는 오름차순 정렬이 안 되어 있는 경우가 많습니다. 정렬이 안 된 표에서 숫자를 생략하면 엑셀은 잘못된 행 번호를 되돌려주며, 결과적으로 완전히 다른 줄의 값을 가져오게 됩니다.
따라서 INDEX+MATCH 조합을 쓸 때는 괄호를 닫기 전 항상 , 0을 써주었는지 확인해야 합니다.
본문에 제시된 수식 예시 및 급여 관련 계산 항목은 이해를 돕기 위한 참고용 추정치이며 법적 효력이 없습니다.