엑셀 XLOOKUP 함수 사용법

XLOOKUP은 한 열(또는 행)에서 값을 찾아 다른 열의 같은 위치에 있는 값을 돌려줘요. VLOOKUP이 하는 일을 모두 하면서 실수할 여지는 더 적어요. 따로 지정하지 않으면 정확히 일치하는 값을 찾고, 왼쪽 열의 값도 돌려줄 수 있고, 값이 없을 때 표시할 내용도 함수 안에서 정할 수 있어요.

함수 구조

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_value는 찾을 값이에요. 제품 이름 같은 거죠.
lookup_array는 값을 찾을 열(또는 행) 하나예요.
return_array는 값을 돌려줄 열(또는 행)이에요. lookup_array와 행 수가 같아야 해요.
if_not_found는 넣지 않아도 돼요. 일치하는 값이 없을 때 표시할 내용이에요. 생략하면 #N/A가 나와요.
match_mode는 넣지 않아도 돼요. 0은 정확히 일치(기본값), -1은 정확히 일치하거나 바로 다음으로 작은 값, 1은 정확히 일치하거나 바로 다음으로 큰 값, 2는 와일드카드 일치예요.
search_mode는 넣지 않아도 돼요. 1은 처음부터 끝까지(기본값), -1은 끝에서 처음으로 찾아요.

XLOOKUP 수식 읽는 법

=XLOOKUP(G2¹,B2:B7²,A2:A7³,"없음"⁴)

G2lookup_value: 찾을 제품 이름
B2:B7lookup_array: 값을 찾을 열
A2:A7return_array: 값을 돌려줄 열. 여기서는 찾을 열보다 왼쪽에 있어요
"없음"if_not_found: B2:B7에 이름이 없을 때 표시할 내용

제품 이름으로 코드 찾기

아래 시트에는 제품 여섯 개가 있고, 코드는 A열에 있어요. G2에 있는 제품(스테이플러)의 코드를 G3에 표시해 볼게요. 코드가 제품 이름보다 왼쪽에 있어서 VLOOKUP으로는 할 수 없는 일이에요.

결과를 표시할 셀을 클릭하세요

"코드" 옆에 있는 G3을 클릭하세요.

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	코드
4	P-103	형광펜	1.8	85	가격
5	P-104	스테이플러	12	15	풀 가격
6	P-105	가위	6.5	40	주문 수량	25
7	P-106	테이프	2.5	60	할인율

A열에 코드, B열에 제품 이름이 있는 목록이에요. G2에 있는 제품의 코드는 G3에 들어가요.

=XLOOKUP( 와 찾을 값을 입력하세요
=XLOOKUP(를 입력하고 G2를 클릭한 다음 쉼표를 입력하세요.
찾을 열을 선택하세요
제품 이름이 있는 B2:B7을 드래그하고 쉼표를 입력하세요.

돌려줄 열을 선택하고 Enter 키를 누르세요

코드가 있는 A2:A7을 드래그하고 Enter를 누르세요. 결과는 P-104예요.

=XLOOKUP(G2,B2:B7,A2:A7)

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	코드	P-104
4	P-103	형광펜	1.8	85	가격
5	P-104	스테이플러	12	15	풀 가격
6	P-105	가위	6.5	40	주문 수량	25
7	P-106	테이프	2.5	60	할인율

XLOOKUP이 B2:B7에서 스테이플러를 찾고(노란색), A2:A7의 같은 행에 있는 코드를 돌려줘요(초록색).

가격을 가져오려면 세 번째 인수를 가격 열로 바꾸세요. 다음 수식은 12를 돌려줘요.

=XLOOKUP(G2,B2:B7,C2:C7)

찾을 열과 돌려줄 열을 따로 지정하기 때문에, 표 안에 열을 삽입해도 수식이 깨지지 않아요. 열 번호를 숫자로 고정하는 VLOOKUP과 다른 점이에요.

돌려줄 범위는 여러 열이어도 돼요. =XLOOKUP(G2,B2:B7,C2:D7)은 가격과 재고, 12와 15를 나란히 돌려줘요. 결과가 오른쪽 셀로 분산되는 거죠. 그 셀이 비어 있지 않으면 셀을 비울 때까지 #SPILL!이 나와요.

값이 없을 때 메시지 표시하기

목록에 풀은 없기 때문에, 그냥 찾으면 #N/A가 나와요. 네 번째 인수를 넣으면 그 대신 원하는 내용을 표시해요.

=XLOOKUP("풀",B2:B7,C2:C7,"없음")

결과는 없음이에요. 여기에는 0, 빈 문자열 "", 셀 참조 등 어떤 값이든 쓸 수 있어요. 수식을 IFERROR로 감싸는 것과 달리 "찾지 못함"인 경우만 바꾸기 때문에, 범위 크기가 다른 것 같은 다른 실수는 여전히 오류로 드러나요.

match_mode로 가장 가까운 값 찾기

정확히 같은 값이 아니라 가장 가까운 값을 찾아야 할 때도 있어요. 10~12행에 할인율 표가 있어요. 1개 이상 주문하면 0%, 10개 이상이면 5%, 50개 이상이면 10%예요. G6의 주문 수량은 25인데, 표에는 25가 없어요.

match_mode를 -1로 쓰면 XLOOKUP은 정확히 일치하는 값을, 없으면 바로 다음으로 작은 값을 찾아요. 다음 수식은 10에 해당하는 할인율 5%를 돌려줘요.

=XLOOKUP(G6,A10:A12,B10:B12,,-1)

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	코드
4	P-103	형광펜	1.8	85	가격
5	P-104	스테이플러	12	15	풀 가격
6	P-105	가위	6.5	40	주문 수량	25
7	P-106	테이프	2.5	60	할인율	5%
8
9	최소 수량	할인율
10	1	0%
11	10	5%
12	50	10%

A10:A12에 25가 없어서 match_mode -1이 바로 아래 값인 10을 찾고, 그 할인율 5%를 돌려줘요.

쉼표 두 개는 if_not_found를 비워 두고 -1이 match_mode 자리에 들어가게 하려는 거예요. 유사 일치 VLOOKUP과 달리 표가 정렬되어 있지 않아도 제대로 동작해요.

match_mode	찾는 값	이 시트에서의 예
`0`(기본값)	정확히 일치하는 값만	25는 `#N/A`
`-1`	정확히 일치하는 값, 없으면 바로 다음으로 작은 값	25는 5%
`1`	정확히 일치하는 값, 없으면 바로 다음으로 큰 값	25는 10%(50의 행)
`2`	와일드카드 일치	`"형광*"`는 형광펜을 찾음

match_mode 2에서는 *가 글자 수에 상관없이 아무 글자를, ?가 정확히 한 글자를 뜻해요. 다음 수식은 형광펜의 가격 1.8을 돌려줘요.

=XLOOKUP("형광*",B2:B7,C2:C7,"없음",2)

2를 빼면 별표를 일반 글자로 취급해서 없음이 표시돼요.

search_mode로 아래쪽부터 찾기

XLOOKUP은 처음 찾은 값을 돌려줘요. 기본적으로 위에서부터 찾기 때문에, 같은 항목이 여러 번 나오는 목록에서는 가장 위쪽 행의 값을 가져와요. 아래쪽부터 찾으려면 search_mode를 -1로 쓰세요. 날짜순으로 정렬된 입고 기록에서 B2:B50에 제품, D2:D50에 날짜가 있다면, 다음 수식은 볼펜이 가장 최근에 입고된 날짜를 돌려줘요.

=XLOOKUP("볼펜",B2:B50,D2:D50,"없음",0,-1)

search_mode 2와 -2는 더 빠른 이진 검색을 하지만, 찾을 범위가 각각 오름차순이나 내림차순으로 정렬되어 있어야 해요. 정렬되지 않은 데이터에서는 오류 없이 틀린 값을 돌려줄 수 있으니, 정렬된 아주 큰 목록에서만 쓰세요.

XLOOKUP, VLOOKUP, INDEX와 MATCH 중 무엇을 쓸까요?

세 가지 모두 표에서 값을 찾아요. XLOOKUP이 가장 유연하고, 나머지 둘은 모든 버전의 엑셀에서 쓸 수 있어요.

	XLOOKUP	VLOOKUP	INDEX와 MATCH
쓸 수 있는 버전	Microsoft 365, Excel 2021 이후	모든 버전	모든 버전
왼쪽 열 돌려주기	가능	불가능	가능
기본 일치 방식	정확히 일치	유사 일치	유사 일치(정확히 일치하려면 MATCH에 `0`)
돌려줄 열 지정	범위	숫자	범위
표 안에 열을 삽입하면	그대로 동작해요	엉뚱한 열을 돌려줄 수 있어요	그대로 동작해요
값이 없을 때 표시할 값	기본 제공(네 번째 인수)	없음(`IFNA`로 감싸기)	없음(`IFNA`로 감싸기)
아래쪽부터 찾기	가능(search_mode `-1`)	불가능	불가능

파일을 여는 사람이 모두 Excel 2021이나 Microsoft 365를 쓴다면 XLOOKUP을 쓰세요. 이전 버전을 쓰는 사람에게 보낼 파일이라면 VLOOKUP이나 INDEX와 MATCH를 쓰세요.

실제 활용 사례

인사

사번 하나로 직원 정보 가져오기

직원 목록의 각 행에 사번, 이름, 부서, 내선 번호가 있어요. B8에 사번을 입력하면 B9의 XLOOKUP 하나가 그 직원의 정보를 모두 돌려줘요.

	A	B	C	D
1	사번	이름	부서	내선
2	E-101	김은지	재무	214
3	E-102	오민재	영업	305
4	E-103	진하늘	IT	118
5	E-104	백준서	영업	309
6	E-105	노지아	인사	120
7
8	찾을 사번	E-103
9	결과	진하늘	IT	118

직원 다섯 명이에요. B9의 수식 하나가 세 열을 돌려주고, 결과가 C9와 D9로 분산돼요.

=XLOOKUP(B8,A2:A6,B2:D6)

돌려줄 범위 B2:D6이 세 열이라서 XLOOKUP은 E-103에 대해 값 세 개, 진하늘, IT, 118을 돌려줘요. 수식은 B9에만 입력하고, C9와 D9는 엑셀이 채워요. 이 셀들은 비워 두세요. 값이 있으면 B9에 #SPILL!이 나와요. 일부 열만 가져오려면 돌려줄 범위를 그 열로 좁히세요. 부서와 내선 번호만 필요하면 C2:D6을 쓰면 돼요.

배송

무게 구간별 배송비

택배 요금이 무게 구간으로 정해져 있어요. 1kg까지, 2kg까지, 5kg까지, 10kg까지이고, 그보다 무거우면 다른 서비스를 써야 해요. 소포마다 그 무게 이상인 첫 번째 구간의 요금을 적용하는데, match_mode 1이 바로 그 값을 찾아요.

	A	B	D	E	F
1	최대 무게(kg)	요금	주문	무게(kg)	배송비
2	1	5.5	501	0.8	5.5
3	2	7	502	2	7
4	5	11	503	3.4	11
5	10	16	504	7.5	16
6			505	12	배송 불가

A2:B5의 요금표와 주문 다섯 건이에요. F열은 주문별 요금을 찾아요.

F2에 아래 수식을 입력하고 F6까지 채우세요.

=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,"배송 불가",1)

주문 503의 무게는 3.4kg이에요. 표에 3.4가 없으니 match_mode 1이 바로 다음으로 큰 값인 5를 찾아서, F4는 11을 돌려줘요. 주문 502는 정확히 2kg이라 2kg 구간과 일치해서 7이에요. 주문 505는 12kg인데 표에 그만큼 큰 값이 없어서, XLOOKUP은 if_not_found에 넣은 배송 불가를 돌려줘요. match_mode를 -1로 쓰면 3.4kg이 2kg 구간에 들어가는데, "몇 kg까지" 요금표에는 맞지 않는 규칙이에요.

학교

학생의 가장 최근 시험 점수

시험 결과를 날짜순으로 기록하기 때문에 한 학생이 여러 번 나와요. XLOOKUP은 보통 처음 일치하는 값, 즉 가장 오래된 점수를 돌려줘요. search_mode -1로 아래쪽부터 찾으면 가장 최근 점수가 나와요.

	A	B	C	E	F
1	날짜	학생	점수	조회	결과
2	2026-09-04	민아	72	학생	민아
3	2026-09-04	도윤	85	최근 점수	84
4	2026-09-11	민아	78	첫 점수	72
5	2026-09-11	서연	90
6	2026-09-18	도윤	88
7	2026-09-18	민아	84
8	2026-09-25	서연	93
9	2026-09-25	도윤	81

4주 동안의 시험 점수예요. F3은 아래쪽부터 찾아 민아의 최근 점수를, F4는 위쪽부터 찾아 첫 점수를 돌려줘요.

=XLOOKUP(F2,B2:B9,C2:C9,"시험 없음",0,-1)

민아는 2행, 4행, 7행에 있어요. 9행부터 위로 찾으면 XLOOKUP이 처음 만나는 민아는 7행이라서, F3은 9월 18일 점수인 84를 돌려줘요. 0은 정확히 일치로 찾게 하고, 여섯 번째 자리의 -1은 찾는 방향을 정해요. F4의 =XLOOKUP(F2,B2:B9,C2:C9)는 위쪽부터 찾아서 첫 점수 72를 돌려줘요. 아직 시험을 보지 않은 학생이라면 시험 없음이 표시돼요.

영업

지역과 분기를 골라 매출 찾기

표의 세로 방향에 지역이, 가로 방향에 분기가 있어요. G2에서 지역을, G3에서 분기를 고르면 XLOOKUP 안에 넣은 또 하나의 XLOOKUP이 그 행과 열이 만나는 값을 찾아요.

	A	B	C	D	E	F	G
1	지역	1분기	2분기	3분기	4분기	선택	조회
2	동부	4,200	3,900	4,600	5,100	지역	북부
3	서부	3,800	4,100	4,300	4,700	분기	3분기
4	북부	2,900	3,100	3,500	3,300	매출	3,500
5	남부	3,300	3,600	3,400	3,900

지역별 분기 매출이에요. G4는 북부 행과 3분기 열이 만나는 값을 돌려줘요.

=XLOOKUP(G2,A2:A5,XLOOKUP(G3,B1:E1,B2:E5))

안쪽부터 읽으세요. XLOOKUP(G3,B1:E1,B2:E5)는 머리글 행에서 3분기를 찾아 그 열 전체인 D2:D5를 돌려줘요. 바깥쪽 XLOOKUP은 A2:A5에서 북부를 찾아 그 열에서 같은 위치의 값 3,500을 돌려줘요. G2나 G3을 바꾸면 결과도 따라 바뀌어요. XLOOKUP이 없는 버전에서는 =INDEX(B2:E5,MATCH(G2,A2:A5,0),MATCH(G3,B1:E1,0))가 같은 결과를 돌려줘요.

틀린 결과가 나오는 실수

범위 크기가 서로 다른 경우. =XLOOKUP(G2,B2:B7,C2:C6)은 찾을 범위가 여섯 행, 돌려줄 범위가 다섯 행이라서 #VALUE!를 돌려줘요. 두 범위에 같은 행을 쓰세요.
if_not_found 없이 #N/A가 나오는 경우. 찾을 범위에 값이 없거나, 공백 하나 또는 형식이 달라요(숫자 101은 텍스트 "101"과 일치하지 않아요). 데이터를 고치거나, 값이 없는 경우가 예상된다면 네 번째 인수를 넣으세요.
복사할 때 범위가 밀리는 경우. 수식을 아래로 채우면 범위도 함께 움직여요. F4로 고정하세요: =XLOOKUP(H2,$B$2:$B$7,$C$2:$C$7).
이전 버전에서 #NAME?가 나오는 경우. XLOOKUP이 없는 Excel 2019 이하 버전에서 파일을 연 거예요.
match_mode와 search_mode를 헷갈린 경우. 둘은 다섯 번째와 여섯 번째 인수이고 순서도 이대로예요. =XLOOKUP("볼펜",B2:B50,D2:D50,"없음",-1)은 아래쪽부터 찾는 게 아니라 바로 다음으로 작은 값을 찾아요. 쉼표 개수를 세어 보세요.

연습

제품과 할인율 찾기

아래 시트를 사용하세요. 강조된 셀을 클릭하고, 시트 위의 입력줄에 수식을 입력한 다음 Enter를 누르세요. 그다음 엑셀에서 직접 해 보세요.

연락처 여섯 개를 A열에 이름, B열에 전화번호, C열에 도시로 적어 보세요.
셀 하나에 도시를 입력하고, XLOOKUP으로 그 도시에 사는 사람의 이름을 찾아보세요. 이름은 왼쪽에 있어요.
네 번째 인수로 "해당 없음"을 넣고, 목록에 없는 도시를 입력해 보세요.

직접 해 보기4개 중 0개 완료

G3에 G2에 있는 제품의 코드를 찾아보세요. 코드는 제품 이름보다 왼쪽에 있어요.
G4에 G2에 있는 제품의 가격을 찾아보세요.
G5에 풀의 가격을 찾고, 목록에 없으면 없음이 표시되게 해 보세요.
G7에 A10:B12의 표에서 G6의 주문 수량에 해당하는 할인율을 찾아보세요.

점선으로 표시된 셀을 클릭하고, 입력줄에 수식을 쓴 다음 Enter를 누르세요.

	A	B	C	D	F	G
1	코드	제품	가격	재고	조회	결과
2	P-101	공책	4.5	120	제품	스테이플러
3	P-102	볼펜	1.2	300	코드
4	P-103	형광펜	1.8	85	가격
5	P-104	스테이플러	12	15	풀 가격
6	P-105	가위	6.5	40	주문 수량	25
7	P-106	테이프	2.5	60	할인율
8
9	최소 수량	할인율
10	1	0%
11	10	5%
12	50	10%

도움이 되었나요? 틀린 부분을 발견하셨나요? 피드백 보내기 · admin@arumsoft.com