엑셀 XLOOKUP 함수 사용법: 정확히 일치와 오류 처리 예제
XLOOKUP은 한 범위에서 값을 찾고 같은 행 또는 열의 다른 범위에서 결과를 돌려주는 함수입니다. VLOOKUP과 달리 반환 열이 검색 열의 왼쪽에 있어도 사용할 수 있고, 열 번호를 직접 세지 않아도 됩니다.
기본 구문
=XLOOKUP(찾을값, 찾을범위, 반환범위, [없을때], [일치방식], [검색방식])
직원번호가 A열, 이름이 B열이고 E2에 찾을 직원번호가 있다면 다음처럼 작성합니다.
=XLOOKUP(E2,$A$2:$A$100,$B$2:$B$100,"찾는 직원 없음")
실무에서 먼저 확인할 세 가지
- 찾을 범위와 반환 범위의 행 개수가 같아야 합니다.
- 복사할 수식은 범위에
$를 붙여 고정합니다. - 직원번호처럼 고유해야 하는 값에 중복이 없는지 확인합니다.
여러 열을 한 번에 반환하기
이름, 부서, 직급이 B:D열에 연속되어 있다면 반환 범위를 넓게 지정할 수 있습니다.
=XLOOKUP(F2,$A$2:$A$100,$B$2:$D$100,"없음")
동적 배열을 지원하는 Excel에서는 결과가 옆 셀로 펼쳐집니다. 결과 영역에 기존 값이 있으면 #SPILL! 오류가 발생할 수 있습니다.
마지막으로 입력된 값 찾기
같은 거래처 코드가 여러 번 있고 가장 최근에 입력된 행을 찾으려면 검색 방식을 -1로 지정합니다.
=XLOOKUP(H2,$A$2:$A$500,$D$2:$D$500,"없음",0,-1)
0은 정확히 일치, -1은 마지막 항목부터 역방향 검색을 의미합니다.
자주 발생하는 오류
| 증상 | 확인할 점 |
|---|---|
| #N/A | 값이 없거나 공백·표기 방식이 다름 |
| #VALUE! | 찾을 범위와 반환 범위 크기가 다름 |
| #SPILL! | 결과가 펼쳐질 셀에 다른 값이 있음 |
| 예상과 다른 값 | 중복 값과 검색 방향 확인 |
눈에 보이지 않는 공백 때문에 일치하지 않을 때는 원본을 바로 덮어쓰지 말고 보조 열에서 TRIM이나 CLEAN 결과를 확인하세요.
버전 호환성
Microsoft 공식 안내에 따르면 XLOOKUP은 Excel 2016과 Excel 2019에서 사용할 수 없습니다. 여러 사람이 파일을 함께 쓴다면 상대방의 Excel 버전을 확인하고, 구버전이 포함되면 INDEX/MATCH 또는 정확히 일치하는 VLOOKUP 대안을 준비하세요.
직접 따라 하는 예제
F2에 직원번호 E003을 입력하고 A열에서 번호를 찾아 B열의 이름을 반환하는 실제 XLSX입니다. 수식 셀을 선택해 각 범위가 어떻게 연결되는지 확인하세요.
공식 참고 자료
- Microsoft XLOOKUP 함수: https://support.microsoft.com/ko-KR/Excel/get-started/xlookup-function