엑셀 XLOOKUP은 ‘찾을 값, 찾을 범위, 가져올 범위’를 차례로 지정하면 같은 위치의 값을 돌려준다. 가져올 열이 왼쪽에 있어도 된다. 옵션을 생략하면 위에서부터 정확히 일치하는 첫 항목을 찾는다.
사용 환경은 Microsoft 365용 Excel 또는 Excel 2021·2024를 기준으로 한다. Microsoft 공식 안내는 Excel 2016·2019에서는 사용할 수 없다고 명시한다. 도움말 위쪽 적용 대상 목록에 구버전이 보이더라도 이 제한을 먼저 읽는다. 파일을 전달할 상대방의 버전도 함께 확인한다.
아래 장부는 직접 만든 가상 자료다. 수식 결과는 공식 규칙에 따른 예상값을 별도로 검산했으며, 실제 Excel 앱에 입력하거나 저장·인쇄한 시험 결과와 화면은 아니다.
엑셀 XLOOKUP의 세 범위를 한 장부에서 연결하기
새 시트의 A1:D5에 아래 자료를 입력한다. 맨 왼쪽 ‘시트 행’은 입력 위치 안내이므로 데이터 열로 추가하지 않는다. F1에는 조회 코드, F2에는 DV-32를 적고 G1은 수량, H1은 담당팀으로 둔다. 좁은 화면에서는 표를 좌우로 움직여 D열까지 확인한다.
| 시트 행 | A열 담당팀 | B열 기기코드 | C열 점검일 | D열 수량 |
|---|---|---|---|---|
| 1 | 담당팀 | 기기코드 | 점검일 | 수량 |
| 2 | 자료팀 | DV-21 | 2026-04-11 | 12 |
| 3 | 지원팀 | DV-32 | 2026-04-14 | 8 |
| 4 | 운영팀 | DV-21 | 2026-04-18 | 15 |
| 5 | 자료팀 | DV-21 | 2026-04-15 | 0 |
-
G2에 수량 조회 수식을 입력한다. 찾을 코드는 F2, 검색할 곳은 B열, 가져올 곳은 D열이다.
=XLOOKUP(F2,$B$2:$B$5,$D$2:$D$5,"코드 확인") -
F2가
DV-32이면 G2의 예상값은 8이다. B3에서 코드를 찾아 D3의 수량을 가져온다. -
H2에는 왼쪽 담당팀을 가져오는 수식을 입력한다.
=XLOOKUP(F2,$B$2:$B$5,$A$2:$A$5,"코드 확인") -
H2의 예상값은 지원팀이다. 찾는 B열보다 A열이 왼쪽이어도 범위를 따로 지정하므로 조회할 수 있다.
$B$2:$B$5의 달러 표시는 수식을 다른 행에 복사할 때 조회표가 움직이지 않게 고정한다. 고정한 조회표와 달리 F2에는 달러 표시를 넣지 않았으므로 아래로 복사하면 F3, F4로 바뀐다. 연습할 때는 먼저 G2·H2 결과부터 확인한다.
수량이 나왔는데 왜 틀릴까? 길이보다 행의 짝을 본다
두 범위의 개수가 같아도 시작 행이 어긋나면 다른 기록의 값을 가져올 수 있다. 오류 문구가 없다는 이유로 조회가 맞았다고 판단하면 안 된다. 엑셀 XLOOKUP은 두 범위에서 서로 같은 순번의 위치를 연결한다.
예를 들어 G2 수식의 반환 범위만 실수로 $D$3:$D$6으로 지정했다고 가정한다. 검색 범위 B2:B5는 네 칸, 반환 범위 D3:D6도 네 칸이다. DV-32는 검색 범위의 두 번째 항목이므로 반환 범위의 두 번째인 D4, 즉 15를 가져오는 것이 예상된다.
| DV-32를 조회하는 범위 | 연결되는 셀 | 예상값 | 판단 |
|---|---|---|---|
| B2:B5 → D2:D5 | B3 → D3 | 8 | 장부의 같은 행 |
| B2:B5 → D3:D6 | B3 → D4 | 15 | 한 행 밀린 다른 기록 |
수식이 반환한 값 하나를 원본 장부의 코드·행·수량과 대조한다. 범위가 잘못됐다면 G2의 세 번째 인수를 $D$2:$D$5로 되돌린다. 자료를 6행까지 추가할 때도 검색 범위와 반환 범위의 끝을 함께 늘린다. 값이 이상하다고 먼저 전체 장부를 정렬하거나 원본 코드를 바꾸지 않는다.
엑셀 XLOOKUP의 마지막 기록은 최신 기록일까?
마지막 행을 찾는 옵션은 날짜의 최댓값을 계산하지 않는다. 공식 구문의 search_mode는 -1을 마지막 항목부터 역방향으로 검색하는 값으로 설명한다. 다섯 번째 인수의 -1과 자리가 다르다.
F2를 DV-21로 바꾼다. 기본 수량 수식의 예상값은 첫 기록 D2의 12다. 맨 아래의 일치 기록을 찾으려면 G2를 다음과 같이 바꾼다.
=XLOOKUP(F2,$B$2:$B$5,$D$2:$D$5,"코드 확인",0,-1)뒤의 0은 정확한 일치, 마지막 -1은 아래에서부터 찾기다. 예상값은 마지막 일치 기록 D5의 0이다. 이 0은 ‘못 찾음’이 아니라 장부에 실제 입력한 수량이다.
점검일을 직접 대조하면 DV-21의 가장 늦은 날짜는 4행의 2026-04-18, 해당 수량은 15다. 이 장부는 날짜순이 아니므로 역방향 결과 0을 최신 수량이라고 부르면 틀린다. 최신 기록이 목적이라면 먼저 날짜가 실제 날짜값인지, 같은 코드의 기록이 시간순으로 쌓였는지 확인한다.
단순히 마지막 행을 선택하려는지, 날짜가 가장 늦은 기록을 선택하려는지부터 정한다. 전체 장부를 정렬해야 한다면 원본 사본에서 모든 관련 열을 함께 선택해 행의 짝을 유지한다. 날짜가 같은 기록 중 무엇을 고를지도 기준이 필요하다. 중복된 모든 기록을 한꺼번에 반환하는 기능과도 구분한다.
같은 코드가 몇 건인지 먼저 보려면 COUNTIF로 조건에 맞는 셀을 세는 방법을 함께 쓴다. F2가 DV-21이면 =COUNTIF($B$2:$B$5,F2)의 예상값은 3이다. 기록이 하나인지 여러 개인지 확인한 뒤 처음·마지막을 고른다.
미등록·오류·엉뚱한 값은 이렇게 나눠 확인한다
네 번째 인수의 "코드 확인"은 일치하는 코드를 찾지 못했을 때의 표시다. 모든 오류를 없애는 인수가 아니다. F2를 DV-99로 바꿔 해당 문구가 나오는지 확인하고, 다시 DV-32로 돌렸을 때 8이 나오는지 비교한다.
- 코드 확인 또는 #N/A: 코드가 실제로 없는지부터 본다. 눈에 같은 코드가 있다면 앞뒤 공백과 숫자·문자 자료형을 대조한다. 공백을 지우거나 코드를 숫자로 바꾸기 전에 원본의 의미를 확인한다.
- #NAME? 또는 함수가 인식되지 않음: 지원 버전과 함수 철자를 확인한다. Excel 2016·2019가 필요한 공유 파일은 VLOOKUP의 정확 일치 방식 등 해당 환경의 대안을 검토한다.
- #VALUE!: 검색 범위와 반환 범위의 크기·방향부터 확인한다. 크기를 맞춘 뒤에도 시작 행이 같은지 따로 본다.
- 숫자는 나오지만 기대값과 다름: 원본의 같은 행인지, 같은 코드가 여러 번 있는지, 처음과 마지막 중 어느 기록을 고른 것인지 순서대로 확인한다.
여러 열을 반환하도록 범위를 넓히면 결과가 이웃 셀로 펼쳐질 수 있다. #SPILL!이 나오면 수식 셀에서 표시되는 결과 범위와 막는 셀을 확인한다. Microsoft 도움말은 기존 값·병합 셀·Excel 표 내부 등 여러 원인을 구분한다. 자료를 무작정 지우기보다 결과 수식을 빈 구역으로 옮길 수 있는지 먼저 검토한다.
엑셀 XLOOKUP을 연습한 뒤에는 DV-32 → 8·지원팀, DV-99 → 코드 확인, DV-21 → 처음 12·마지막 0을 대조한다. 이 결과를 확인한 다음 자기 장부로 옮기면 범위의 짝과 중복 기록의 선택 기준을 함께 점검할 수 있다.