XLOOKUP(): Excel의 가장 다재다능한 조회 함수

단계별 예제로 Excel의 XLOOKUP()을 사용하는 방법을 배웁니다. 구문, 정확/근사 일치, 다중 열 반환, 일반 오류 해결 방법을 다룹니다.

업데이트됨 2026년 7월 16일 · 15분 읽다

VLOOKUP()을 쓰다 왼쪽으로 검색해야 해서 막혔던 적이 있다면, XLOOKUP()이 그 문제를 해결합니다. Excel의 최신 조회 함수로, 하나의 간단한 수식으로 VLOOKUP(), HLOOKUP(), 심지어 대부분의 INDEX()와 MATCH()까지 대체할 수 있습니다.

XLOOKUP()은 어느 방향으로든 검색할 수 있고, 한 번에 여러 열을 가져오며, 값이 없을 때 사용자 지정 메시지를 보여줄 수 있습니다.

이 가이드에서는 먼저 구문을 살펴본 후, 처음부터 실제 수식을 만들어 보겠습니다. 전체 과정에서 하나의 간단한 데이터셋만 사용합니다. 또한 정확/근사 일치, 여러 열 반환, 오류 처리, 그리고 XLOOKUP()과 VLOOKUP(), INDEX()/MATCH() 비교까지 다룹니다.

Excel에서 XLOOKUP() 사용하는 방법

실제 XLOOKUP() 수식을 처음부터 만들어 보겠습니다.

데이터셋에는 다섯 개의 제품이 있고, 각 제품에는 ID, 이름, 카테고리, 가격이 있으며 A열에서 D열까지, 1행에는 헤더가 있습니다.

XLOOKUP()용 데이터셋. 작성자 제공 이미지.

예를 들어 "P104"의 가격을 찾고 싶다고 해봅시다. 단계별로 진행해 보겠습니다.

단일 값 조회

결과를 표시할 빈 셀을 클릭하세요. 여기서는 F2를 사용합니다. 다음 수식을 입력합니다:

=XLOOKUP("P104", A2:A6, D2:D6)

이 수식에서:

"P104"는 lookup_value로, 우리가 찾는 대상입니다.
A2:A6는 lookup_array로, Excel이 검색할 범위입니다.
D2:D6는 return_array로, Excel이 답을 가져올 범위입니다.

Excel은 A열(“Product ID”)에서 "P104"를 찾아 5행에서 발견하고, 해당하는 D열의 값인 $45를 반환합니다.

"P104"를 직접 입력하는 대신 셀을 참조하면, 수식 자체를 수정하지 않고 조회 값을 바꿀 수 있습니다. 여기서는 A5 셀에 "P104"가 있으므로 수식은 다음과 같습니다:

=XLOOKUP(A5, A2:A6, D2:D6)

Excel에서 XLOOKUP() 사용. 작성자 제공 이미지.

이제 A5에 임의의 제품 ID를 입력하면 가격이 자동으로 업데이트됩니다.

다른 열에서 값 반환

이 단계에서 XLOOKUP()과 VLOOKUP()의 진짜 차이가 드러납니다. 검색 열의 오른쪽 열에서만 값을 반환하도록 제한되지 않습니다.

가격이 아니라 "P102"의 카테고리를 원한다고 가정해 봅시다. 설정은 같고, 대신 return_array를 C열(“Category”)로 지정합니다:

=XLOOKUP(A3, A2:A6, C2:C6)

여기서는 A3가 Home을 반환합니다. A5로 바꾸면 Electronics로 업데이트됩니다. 마찬가지로 return_array를 변경해 제품명, 가격 또는 다른 어떤 열도 동일한 lookup_value와 lookup_array로 가져올 수 있습니다.

다른 열에서 값을 반환하는 XLOOKUP(). 작성자 제공 이미지.

VLOOKUP()이라면 검색 열에서 열을 세어가며 원하는 결과마다 열 인덱스 번호를 조정해야 합니다. XLOOKUP()은 원하는 열을 직접 지정하면 됩니다.

여러 행에 수식 복사

지금까지는 한 번에 하나의 값만 조회했습니다. 하지만 실제 스프레드시트에서는 보통 여러 값을 한꺼번에 찾아야 합니다. 예를 들어 가격이 필요한 주문 ID 목록 전체가 있을 수 있습니다.

이렇게 설정합니다: A열의 A2부터 제품 ID 목록이 이미 있습니다. 각 ID 옆, F열에 일치하는 가격을 표시하려고 합니다.

이를 위해 F2를 클릭하고 다음처럼 A2를 조회 값으로 참조하는 수식을 입력합니다:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

이제 F2를 다시 클릭한 다음, 셀의 오른쪽 아래 모서리에 있는 채우기 핸들을 나머지 행까지 아래로 끌어내리세요.

Excel이 수식을 아래로 복사하고, 각 행은 자기 Product ID에 맞는 올바른 가격을 반환합니다. A2의 ID는 F2에, A3의 ID는 F3에, 이런 식입니다.

수식을 다른 셀로 아래로 복사. 작성자 제공 이미지.

A2:A6과 D2:D6 주변의 달러 기호에 주목하세요. 이것을 절대 참조라고 하며, 범위를 고정해 수식을 복사해도 범위가 이동하지 않게 합니다.

여기서는 각 행이 조회 값만 달라질 뿐 동일한 제품 목록을 검색해야 하므로 중요합니다.

XLOOKUP()에서 정확 일치와 근사 일치 사용 방법

기본적으로 XLOOKUP()은 정확 일치를 찾습니다. 찾지 못하면, 앞서 다룬 if_not_found 인수를 설정하지 않는 한 오류를 반환합니다.

하지만 XLOOKUP()은 match_mode 인수를 이용해 가장 가까운 값을 찾을 수도 있습니다.

정확 일치(기본값)

이를 위해 추가로 입력할 것은 없습니다. 지금까지 만든 모든 수식은 이미 정확 일치를 사용했습니다. XLOOKUP()의 기본 동작이기 때문입니다.

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel은 A2의 정확한 값을 찾아, 조회 범위 어딘가에서 그 값이 정확히 일치할 때만 결과를 반환합니다.

이를 명시적으로 적고 싶다면 네 번째 인수로 0을 추가하세요:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP()은 정확히 일치하는 값을 반환합니다. 작성자 제공 이미지.

0 앞의 공백이 보이나요? 여기서는 if_not_found를 비워 두고 건너뛰기 위해 공란을 넣고, 바로 match_mode로 이동합니다. 대부분의 경우 이 인수는 생략해도 되며, Excel이 스스로 기본값인 정확 일치를 사용하도록 두면 됩니다.

근사 일치

이제, 데이터에 모든 가능한 조회 값이 있는 것이 아닌 경우를 가정해 봅시다. 예를 들어 영업사원의 총매출 구간에 따라 커미션율이 달라지는 구조가 있을 수 있습니다. 모든 금액에 대한 행이 있는 것이 아니라 각 구간의 시작점만 있을 것입니다.

이때 근사 일치가 도움이 됩니다.

match_mode를 -1로 설정하면, XLOOKUP()은 조회 값보다 작거나 같은 값 중 가장 큰 값을 찾습니다.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

예를 들어 조회 범위에 $0, $5,000, $10,000의 매출 임계값이 있고 각 값에 다른 커미션율이 연결되어 있다고 합시다.

매출이 $7,500인 사원은 어떤 행과도 정확히 일치하지 않습니다. match_mode를 -1로 설정하면 XLOOKUP()은 $7,500을 넘지 않는 임계값 중 가장 큰 값인 $5,000을 찾아 그 행의 커미션율을 반환합니다.

match_mode를 1로 설정하면 논리가 반대로 바뀝니다. 조회 값보다 크거나 같은 값 중 가장 작은 값을 찾습니다. 이는 배송 중량 구간처럼 내림이 아니라 올림으로 다음 구간을 적용하는 경우에 유용합니다.

XLOOKUP()은 근사 일치를 반환합니다. 작성자 제공 이미지.

참고: 근사 일치는 조회 범위가 정렬되어 있어야 합니다. -1은 오름차순, 1은 내림차순입니다. 올바르게 정렬되지 않으면, XLOOKUP()은 오류를 내지 않고도 잘못된 결과를 반환할 수 있어 더 위험합니다. 따라서 이를 사용하기 전에 정렬 순서를 반드시 재확인하세요.

XLOOKUP()으로 왼쪽의 값 조회하기

VLOOKUP()은 범위의 맨 왼쪽 열만 검색하고 그 오른쪽 열에서 값을 반환할 수 있으며, 반대 방향은 불가능합니다. 세 번째 인수는 범위가 아니라 왼쪽 끝에서부터 센 열 인덱스일 뿐입니다.

"왼쪽으로 한 열 가라"라고 지시할 방법이 없기 때문에, 필요한 값이 검색 열의 왼쪽에 있으면 데이터를 재배치하거나 보조 열을 추가해야만 합니다.

XLOOKUP()은 검색 범위와 반환 범위를 따로 지정하므로 이런 제한이 없습니다.

우리의 제품 목록을 보겠습니다. Product ID는 A열, Product Name은 B열에 있습니다.

제품 이름을 가지고 해당 ID를 찾고 싶다면, 즉 VLOOKUP()이 하는 작업의 반대입니다.

C9에 Bluetooth Speaker(목록에 존재하는 이름)를 입력합니다. E2에는 다음을 입력합니다:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP()은 B열에서 "Bluetooth Speaker"를 찾아 5행에서 발견하고, B열의 왼쪽에 있는 A열에서 "P104"를 반환합니다.

XLOOKUP()은 VLOOKUP()과 달리 왼쪽의 값도 검색합니다. 작성자 제공 이미지.

XLOOKUP()으로 여러 열 반환하기

XLOOKUP()은 동적 배열 기능을 사용해 단일 수식으로 여러 열을 한 번에 반환할 수 있습니다. 이를 위해 return_array를 단일 열이 아닌 범위로 지정하면, XLOOKUP()이 필요한 만큼 셀에 전체 일치 행을 "스필"합니다.

우리의 제품 목록으로 시도해 보겠습니다.

C9에 제품 ID(예: "P104")를 입력합니다. E2에는 다음을 입력합니다:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

여기서 return_array는 B2:D6으로, 제품명, 카테고리, 가격에 해당하는 세 열 범위입니다. Enter를 누르면 XLOOKUP()이 A열에서 "P104"를 찾고, Bluetooth Speaker, Electronics, $45를 한 번에 반환하여 E9, F9, G9로 스필합니다.

XLOOKUP()은 한 개의 수식으로 여러 열을 반환합니다. 작성자 제공 이미지.

이것이 스필 범위의 의미입니다. 실제 수식은 E9에 있고, F9와 G9는 자체 수식 없이 자동으로 채워집니다.

F9를 클릭하면 수식 입력줄에 원래 수식이 회색으로 표시되며, 직접 입력된 것이 아님을 볼 수 있습니다. E9의 스필이 활성화된 상태에서 F9에 무언가 입력하려고 하면 Excel은 스필 오류를 표시합니다.

해결 방법은 아래의 일반 오류 섹션에서 다룹니다.

XLOOKUP()에서 누락된 값 처리하기

기본적으로 XLOOKUP()은 일치 항목을 찾지 못하면 #N/A 오류를 발생시킵니다. if_not_found 인수를 사용하면 기본 오류 대신 원하는 내용을 표시할 수 있습니다.

예를 들어 C9에 데이터에 없는 Product ID 인 "P999"를 입력했다고 해봅시다.

사용자 지정 메시지 반환

G2에 다음을 입력합니다:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

A열에 "P999"가 없으므로, XLOOKUP()은 오류를 건너뛰고 "Not Found"를 반환합니다. 메시지는 "Check Product ID"나 "No Match" 등 원하는 문구로 바꿀 수 있습니다.

기본 오류 대신 사용자 지정 메시지 표시. 작성자 제공 이미지.

빈 칸 반환

텍스트 없이 빈 셀이 필요할 때가 있습니다. 이 경우 다음처럼 따옴표 두 개만 사용합니다:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

보고서에서 이 수식을 사용할 때, 값이 있는 열 옆에 "Not Found" 같은 문구가 섞여 보이는 것을 피하고 싶을 때 유용합니다.

기본 오류 대신 빈 셀 표시. 작성자 제공 이미지.

진짜 오류로 두기

if_not_found를 완전히 생략하면 XLOOKUP()은 #N/A로 돌아갑니다. 이것이 항상 나쁜 것은 아닙니다. 본인 용도의 수식을 만들고 있으며 오탈자나 누락을 즉시 잡아내고 싶다면, 문제를 숨기는 조용한 빈 칸보다 #N/A가 표시되는 편이 더 도움이 됩니다.

참고: if_not_found는 일치 항목 누락만 처리합니다. 예를 들어 lookup_array 와 return_array의 크기가 다르면 XLOOKUP()은 여전히 #VALUE! 오류를 발생시키며, if_not_found로는 이를 막을 수 없습니다. 이 특정 사례는 아래의 일반 오류 섹션에서 다룹니다.

고급 XLOOKUP() 기능

이제 XLOOKUP()의 고급 기능을 살펴보겠습니다.

아래에서 위로 검색

search_mode를 -1로 설정하면 XLOOKUP()은 범위를 위에서 아래가 아니라 아래에서 위로 스캔합니다. 중복 값이 있고, 첫 번째가 아닌 가장 최근 항목을 원할 때 사용하세요.

데이터셋에 중복된 Customer ID가 세 개 있다고 가정합니다. E2 셀에는 기본(위에서 아래) 방식의 수식을 입력합니다:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

이는 테이블의 시작부터 계산되어 첫 번째 일치값의 결과를 표시합니다.

하지만 아래에서 검색하려면 다음 수식을 사용합니다:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

이 수식에서 -1은 Excel에 데이터셋의 맨 아래부터 검색을 시작하라고 지시합니다. 그러면 아래에서 첫 번째 일치값인 340을 반환합니다.

XLOOKUP()은 아래에서 위로 데이터를 스캔합니다. 작성자 제공 이미지.

이진 검색 모드

search_mode를 2로 설정하면 XLOOKUP()은 한 행씩 확인하는 방식에서 이진 검색으로 전환합니다.

작동 방식은 다음과 같습니다:

먼저 범위의 가운데 항목을 확인합니다.
일치하지 않으면 값이 있을 수 없는 절반을 버리고, 남은 부분의 가운데를 봅니다.
이를 반복하며 매번 데이터를 절반으로 줄여가다 일치 항목을 찾습니다.
search_mode를 -2로 설정하면 같은 방식으로 작동하되, 처음이 아니라 마지막 항목부터 시작합니다.

여기서 E2에 다음을 입력합니다:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

이번에는 전혀 다른 행인 200을 반환합니다.

이진 검색 모드가 잘못된 결과를 반환한 예. 작성자 제공 이미지.

이진 검색은 lookup_array가 정렬되어 있을 때만 동작하기 때문입니다. 지금 Customer ID 열은 정렬되지 않아 C100, C101, C100, C102, C100 순으로 섞여 있고, 이진 검색이 잘못된 절반을 버려 틀린 일치를 제공합니다.

이제 데이터가 실제로 정렬되었을 때를 보겠습니다.

동일한 로그를 Customer ID 오름차순으로 정렬(C100 세 개가 앞에 오도록)한 뒤 같은 수식을 다시 실행합니다.

정렬된 데이터에서는 이진 검색 모드가 제대로 동작합니다. 작성자 제공 이미지.

이번에는 E2가 120을 반환하며, 일반 검색과 동일한 결과가 나옵니다. 차이는 여기에 있습니다. 이진 검색은 안전하게 제외할 수 있는 데이터만 건너뛰며, 이는 데이터가 처음부터 정렬되어 있을 때만 성립합니다.

중첩 XLOOKUP() 수식

조회 결과가 고정 범위가 아니라 두 번째 조회의 입력이 되어야 할 때, XLOOKUP()을 서로 중첩해서 사용하세요.

예를 들어 가격을 찾되, 어느 열에서 가져올지는 고객의 가격 책정 티어 같은 별도의 조회 결과에 따라 달라진다고 가정해 봅시다.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

이 수식에서 내부 XLOOKUP()이 먼저 D9에 따라 가져올 열 범위를 결정하고, 그 결과를 외부 XLOOKUP()의 return_array로 사용합니다.

중첩 XLOOKUP(). 작성자 제공 이미지.

XLOOKUP() vs. VLOOKUP()

XLOOKUP()의 작동 방식을 자세히 살펴보았습니다. 이제 VLOOKUP()과 비교해 보겠습니다.

	XLOOKUP()	VLOOKUP()
조회 방향	왼쪽, 오른쪽, 위, 아래 모두 검색	조회 열의 오른쪽만 검색
정확 일치 동작	기본적으로 정확 일치	기본적으로 근사 일치이며, 네 번째 인수를 FALSE로 설정해야 정확 일치
오류 처리	내장형 if_not_found 인수	별도의 IFERROR() 래퍼 필요
유연성	하나의 수식으로 여러 열 반환 가능	수식 하나당 한 열만 반환
유지 보수 용이성	셀 범위를 사용하므로 열을 삽입해도 정확성 유지	열 인덱스 번호를 사용하므로 열 삽입 시 깨질 수 있음

간단히 짚고 넘어갈 점이 두 가지 있습니다:

VLOOKUP()은 기본이 근사 일치입니다. 네 번째 인수를 빼먹으면 오류 없이 조용히 잘못된 값을 반환할 수 있습니다.
VLOOKUP()이 있는 시트에 열을 삽입하면, 수식이 조용히 잘못된 값을 끌어오기 시작합니다. XLOOKUP()은 열을 세지 않고 범위를 직접 참조하므로 이를 피합니다.

이렇다고 해서 VLOOKUP()이 쓸모없어진 것은 아닙니다. 단순하고 정적인 시트에서는 여전히 잘 동작합니다. 다만 앞으로 새로 만드는 작업에서는 XLOOKUP()을 기본 선택으로 하는 편이 더 안전합니다.

XLOOKUP() vs. INDEX() 및 MATCH()

XLOOKUP()이 나오기 전에는 INDEX()와 MATCH() 조합이 VLOOKUP()의 한계를 우회하는 표준 방법이었습니다. 두 개의 함수를 함께 사용합니다.

MATCH()는 조회 값의 위치를 찾습니다.
INDEX()는 그 위치를 사용해 지정한 곳에서 결과를 가져옵니다. 왼쪽이든 오른쪽이든 상관없습니다.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

이를 본문 내내 사용한 XLOOKUP() 버전과 비교해 보세요:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP()은 Microsoft 365와 Excel 2021 이후 버전에서만 작동합니다. 통합 문서를 Excel 2019나 2016에서도 열어야 하거나, 구버전 사용자와 공유해야 한다면 INDEX()와 MATCH()는 어디서나 동작합니다.

더 작은 사례도 있습니다.

INDEX()와 MATCH()는 "위치 찾기"와 "값 반환"을 분리하므로, 복잡한 중첩 수식에서 도움이 되는 경우가 있습니다. 흔하진 않으므로 대부분의 독자에게는 필요하지 않습니다.

이 두 가지 경우를 제외하면, 새 통합 문서에서는 XLOOKUP()이 더 나은 선택입니다.

일반적인 XLOOKUP() 오류와 해결 방법

가장 자주 겪는 문제와 각 해결책을 빠르게 참조할 수 있도록 정리했습니다.

#N/A 오류

이는 일치 항목을 찾지 못했을 때 XLOOKUP()의 기본 반응입니다. 앞서 lookup_value와 lookup_array가 맞지 않아 A열에서 일치시킬 대상이 없어 XLOOKUP()이 실패했던 사례가 있었습니다.

해결 방법: lookup_value 가 lookup_array에 실제로 존재하며, 철자와 서식이 동일한지 다시 확인하세요. 일치하지 않음이 예상 가능한 정상 상황이라면, if_not_found 인수로 #N/A를 "Not Found"나 빈 칸 등 더 유용한 값으로 바꾸세요. 위에서 다룬 방법입니다.

잘못된 조회 범위

lookup_array와 return_array가 올바르게 정렬되지 않았을 때(행이나 열이 틀리거나, 크기가 맞지 않는 경우) 발생합니다.

Excel 또는 Sheets의 버전에 따라 #VALUE! 오류로 나타나기도 하고, 때로는 별도의 설명 메시지와 함께 #N/A로 보이기도 합니다. #N/A라고 해서 누락된 일치라고 단정하지 마세요.

해결 방법: lookup_array 와 return_array가 동일한 행 또는 열 수를 덮도록 하세요. 예를 들어 lookup_array가 A2:A5라면, return_array도 2~5행을 포괄해야 하며, 4~5행처럼 불일치하는 범위여서는 안 됩니다.

데이터 형식 불일치

VLOOKUP()이 숫자를 기대했는데 텍스트를 받아 오류를 내는 경우가 있듯, XLOOKUP()에서도 비슷한 문제가 발생할 수 있습니다. 화면상으로는 동일해 보여도, lookup_value 가 숫자인데 lookup_array에는 같은 값이 텍스트로 저장되어 있거나, 그 반대인 경우 XLOOKUP()은 일치를 찾지 못합니다.

해결 방법: lookup_value 와 lookup_array가 동일한 데이터 형식을 사용하는지 확인하세요. 텍스트로 저장된 숫자는 보통 셀에서 왼쪽 정렬로 표시됩니다. lookup_value를 VALUE()로 감싸 텍스트를 숫자로 변환하거나, 반대 방향이라면 TEXT()를 사용하세요.