엑셀 XLOOKUP 함수 사용법 - VLOOKUP의 한계를 넘는 검색 함수

시리즈 1편에서 VLOOKUP을 다루면서 몇 가지 제약을 언급했어요.
왼쪽 방향 검색 불가, 열번호 직접 지정, 열 삽입/삭제 시 오류 가능성 등.

XLOOKUP은 이런 제약을 모두 해결한 함수입니다.
Microsoft 365 또는 Excel 2021 이상에서 사용할 수 있어요.

이 글에서는 XLOOKUP의 기본 문법, VLOOKUP과의 차이, 실무 예제, 배열 반환 기능까지 다룹니다.

XLOOKUP 기본 문법

=XLOOKUP(찾을값, 찾을범위, 반환범위, [못찾을때값], [일치모드], [검색모드])

필수 인수는 앞의 3개뿐이에요. 나머지는 선택 인수입니다.

인수	설명	필수 여부
찾을값	검색할 값 또는 셀 주소	필수
찾을범위	찾을값을 검색할 열(또는 행)	필수
반환범위	결과를 가져올 열(또는 행)	필수
못찾을때값	일치하는 값이 없을 때 반환할 값	선택 (기본: #N/A)
일치모드	0=정확히 일치, -1=이전값, 1=다음값, 2=와일드카드	선택 (기본: 0)
검색모드	1=위→아래, -1=아래→위, 2=이진검색(오름차순), -2=이진검색(내림차순)	선택 (기본: 1)

실무에서 대부분의 경우 앞의 3개 인수만 입력하면 됩니다.
VLOOKUP과 달리 기본값이 정확히 일치(0)이므로 마지막에 0이나 FALSE를 쓸 필요가 없어요.

VLOOKUP과 XLOOKUP 비교

항목	VLOOKUP	XLOOKUP
검색 방향	오른쪽만	양방향 (왼쪽도 가능)
기본 일치	유사일치 (TRUE)	정확히 일치 (0)
열번호 지정	숫자로 직접 입력	반환범위를 직접 선택
열 삽입/삭제 영향	열번호 틀어질 수 있음	영향 없음
못 찾을 때 처리	IFERROR 필요	4번째 인수로 지정
여러 열 동시 반환	불가 (수식 반복 필요)	가능 (배열 반환)
사용 가능 버전	모든 버전	Microsoft 365, Excel 2021+

XLOOKUP은 거의 모든 면에서 VLOOKUP을 개선한 함수예요.
단, Excel 2019 이하 버전에서는 사용할 수 없으니 파일 공유 시 주의가 필요합니다.

예제 1 - 기본 사용법: 사번으로 이름 찾기

직원 목록에서 사번으로 이름을 가져오는 예제입니다.

데이터 (A2:D8):

	A	B	C	D
2	S001	김민수	영업팀	350
3	S002	이지은	인사팀	280
4	S003	박준호	개발팀	310
5	S004	최수진	영업팀	250
6	S005	정하늘	기획팀	420
7	S006	한도윤	개발팀	300

(D열: 실적, 단위: 만 원)

G2셀에 "S003"을 입력하고, H2셀에:

=XLOOKUP(G2,A2:A8,B2:B8)

결과: 박준호

VLOOKUP으로 쓰면 =VLOOKUP(G2,A2:D8,2,0)인데, XLOOKUP은 반환범위(B열)를 직접 선택하므로 열번호를 셀 필요가 없어요.

예제 2 - 왼쪽 방향 검색

VLOOKUP으로는 불가능했던, 이름으로 사번을 역방향으로 찾는 예제입니다.

=XLOOKUP("박준호",B2:B8,A2:A8)

결과: S003

찾을범위(B열)가 반환범위(A열)보다 오른쪽에 있어도 문제없이 동작해요.
VLOOKUP에서는 이 작업을 하려면 INDEX+MATCH 조합을 써야 했습니다.

예제 3 - 못 찾을 때 값 지정

4번째 인수를 사용하면 #N/A 오류 대신 원하는 값을 표시할 수 있어요.

=XLOOKUP(G2,A2:A8,B2:B8,"해당 사번 없음")

G2에 존재하지 않는 사번을 입력하면 #N/A 대신 "해당 사번 없음"이 표시됩니다.

VLOOKUP에서는 이 처리를 위해 IFERROR로 감싸야 했어요.

VLOOKUP 방식: =IFERROR(VLOOKUP(G2,A2:D8,2,0),"해당 사번 없음")
XLOOKUP 방식: =XLOOKUP(G2,A2:A8,B2:B8,"해당 사번 없음")

XLOOKUP이 더 간결합니다.

예제 4 - 여러 열 동시 반환 (배열 반환)

XLOOKUP은 반환범위를 여러 열로 지정하면 한 번에 여러 값을 가져올 수 있어요.

사번으로 이름, 부서, 실적을 한꺼번에 가져오려면:

=XLOOKUP(G2,A2:A8,B2:D8)

반환범위를 B2:D8(이름~실적)로 넓게 잡으면, H2셀에 이름, I2셀에 부서, J2셀에 실적이 자동으로 채워져요.

단, 결과가 출력될 셀(H2:J2)이 비어 있어야 합니다.
이미 값이 있으면 #SPILL! 오류가 나요. 해당 셀을 비우면 해결됩니다.

예제 5 - 와일드카드 검색

부분 일치 검색을 하려면 5번째 인수(일치모드)를 2로 설정합니다.

이름에 "준"이 포함된 직원의 부서를 찾으려면:

=XLOOKUP("*준*",B2:B8,C2:C8,"없음",2)

결과: 개발팀 (박준호가 일치)

와일드카드는 일치모드를 2로 지정해야만 작동해요. 기본값(0)에서는 무시됩니다.

자주 하는 실수 / 주의사항

1. 찾을범위와 반환범위의 크기 불일치

찾을범위와 반환범위의 행(또는 열) 수가 같아야 해요.
A2:A8(7행)과 B2:B10(9행)을 넣으면 #VALUE! 오류가 납니다.

2. #NAME? 오류

XLOOKUP을 입력했는데 #NAME? 오류가 나면 엑셀 버전 문제예요.
Excel 2019 이하에서는 XLOOKUP을 사용할 수 없습니다.

이전 버전 사용자와 파일을 공유해야 한다면 VLOOKUP이나 INDEX+MATCH를 사용하세요.

3. #SPILL! 오류 (배열 반환 시)

여러 열을 동시에 반환할 때, 결과가 출력될 셀에 다른 값이 있으면 발생해요.
해당 셀의 내용을 지우면 해결됩니다.

VLOOKUP을 쓸지 XLOOKUP을 쓸지

판단 기준은 간단해요.

• 본인만 사용하는 파일이고, Microsoft 365 / Excel 2021 이상이면 → XLOOKUP
• Excel 2019 이하 사용자와 파일을 공유해야 하면 → VLOOKUP 또는 INDEX+MATCH
• 왼쪽 방향 검색이 필요하면 → XLOOKUP (또는 INDEX+MATCH)

같은 환경이라면 XLOOKUP이 더 직관적이고 오류 가능성도 적습니다.

다음 글에서는 피벗테이블 기초를 다룹니다. 함수 없이도 데이터를 집계하고 분석할 수 있는 기능이에요.