xlookup 함수 | 찾는 값, 못 찾을 때 문구, 마지막 값까지 한 번에

xlookup 함수는 =XLOOKUP(찾을 값, 찾을 범위, 반환 범위) 세 칸만 채우면 같은 행의 값을 가져오는 엑셀 조회 함수예요.
못 찾을 때 띄울 문구, 마지막 값 찾기, 구간 조회까지 인수만 바꿔 처리하고, 엑셀 2016과 2019에서는 쓸 수 없습니다.

xlookup 함수란, 찾은 줄에서 다른 칸 값을 꺼내는 함수예요

xlookup 함수는 범위에서 값을 찾고, 처음 일치한 항목과 같은 위치에 있는 값을 돌려줘요.
상품 코드를 넣으면 가격이 나오고, 직원 번호를 넣으면 이름이 나오는 식입니다.

도서관에서 책 번호표를 보고 서가에서 책을 꺼내는 것과 같아요. 번호표가 '찾을 값', 번호가 적힌 서가 줄이 '찾을 범위', 꺼내려는 책이 놓인 줄이 '반환 범위'입니다.

예전에는 vlookup 함수가 이 역할을 맡았어요.
vlookup은 표 전체를 고르고 열 번호를 세어야 하지만, xlookup은 찾을 열과 가져올 열을 따로 고르니 열 번호를 셀 일이 없습니다.

xlookup 사용법, 칸 세 개로 시작하고 못 찾을 때 문구도 넣어요

xlookup 사용법은 인수 순서만 외우면 끝나요.
첫째 칸에 찾을 값, 둘째 칸에 그 값을 찾을 범위, 셋째 칸에 돌려받을 값이 들어 있는 범위를 적습니다.

찾을 값 고르기상품 코드가 적힌 A2 같은 셀을 첫 번째 칸에 넣어요.

찾을 범위 고르기코드가 모여 있는 B2:B5처럼 한 줄 또는 한 열 범위를 두 번째 칸에 넣어요.

돌려받을 범위 고르기가격이 모여 있는 C2:C5를 세 번째 칸에 넣어요. 찾은 위치와 같은 위치의 값이 나옵니다.

못 찾을 때 문구 넣기네 번째 칸에 "상품 없음"처럼 문구를 넣으면 일치하는 값이 없을 때 #N/A 대신 그 문구가 나와요.

네 번째 칸은 오류를 숨기는 칸이 아니라 '못 찾았다'는 안내 문구를 적는 칸이에요. 문구를 비워 두면 #N/A가 그대로 나옵니다.

예시 1 · 상품 가격표에서 가격을 찾는 서연 씨 (가정)코드가 B2:B5, 가격이 C2:C5에 있고 A2에 찾을 코드를 적었다고 가정해 볼게요.
=XLOOKUP(A2,B2:B5,C2:C5,"상품 없음")이라고 쓰면 코드가 있을 때는 같은 줄의 가격이, 없는 코드를 넣으면 "상품 없음"이 나옵니다.

xlookup 함수 사용법, 같은 값이 여러 개면 마지막 값도 찾을 수 있어요

기본 검색은 첫 항목부터 내려가면서 처음 만나는 값을 돌려줘요.
같은 고객 코드가 여러 줄 있을 때 가장 아래 줄의 값이 필요하면 여섯 번째 칸 search_mode에 -1을 넣습니다.

search_mode 1첫 항목부터생략하면 이 방식이에요. 처음 일치한 값을 돌려줘요

search_mode -1마지막 항목부터거꾸로 검색해서 가장 아래에 있는 일치 값을 돌려줘요

예시 2 · 같은 고객이 여러 번 결제한 장부를 보는 지훈 씨 (가정)고객 코드가 B2:B100, 결제일이 C2:C100에 있다고 가정해 볼게요.
=XLOOKUP(A2,B2:B100,C2:C100,,0,-1)이라고 쓰면 그 고객의 마지막 결제일이 나와요. 다섯 번째 칸 match_mode는 0을 넣어 정확히 일치로 두고, 네 번째 칸은 비워 둡니다.

일치하는 줄이 여러 개인데 전부 필요하다면 xlookup이 아니라 FILTER 함수 같은 다른 방법을 써야 해요.
xlookup은 첫 일치 항목 하나만 돌려주기 때문입니다.

구간표는 match_mode로, 다음 작은 값과 다음 큰 값을 골라요

다섯 번째 칸 match_mode는 정확히 같은 값이 없을 때 어떻게 할지 정해요.
생략하거나 0이면 정확히 일치하는 값만 찾고, 없으면 #N/A가 나옵니다.

match_mode 0정확히 일치기본값이에요. 같은 값이 없으면 #N/A

match_mode -1다음으로 작은 값정확히 같은 값이 없으면 그다음으로 작은 항목을 선택해요

match_mode 1다음으로 큰 값정확히 같은 값이 없으면 그다음으로 큰 항목을 선택해요

match_mode 2와일드카드 일치*, ?, ~에 특별한 의미를 줘서 일부 글자만으로 찾아요

예시 3 · 점수로 등급을 매기는 하윤 선생님 (가정)기준점 0, 60, 70, 80, 90에 F, D, C, B, A를 짝지은 표라고 가정할게요.
=XLOOKUP(87,{0,60,70,80,90},{"F","D","C","B","A"},,-1)을 넣으면 87은 정확히 일치하는 기준점이 없어 다음으로 작은 80이 선택되고, 결과는 B예요.

기준값 표를 만들 때는 구간 설계가 맞는지 먼저 확인하세요.
다음 작은 값은 기준보다 작은 쪽을, 다음 큰 값은 큰 쪽을 고르니 어느 쪽이 맞는 구간인지 따져 봐야 합니다.

vlookup과 차이, 여러 열을 한 번에 가져오고 왼쪽 열도 돼요

xlookup은 반환 범위를 여러 열로 잡으면 한 번의 수식으로 같은 행의 여러 값을 가져와요.
Microsoft 예제처럼 =XLOOKUP(B2,B5:B14,C5:D14)이라고 쓰면 직원 번호 하나로 이름과 부서가 옆 칸까지 펼쳐져 나옵니다.

찾을 범위와 반환 범위를 따로 고르기 때문에 가져올 열이 찾는 열의 왼쪽에 있어도 상관없어요.
같은 조회를 vlookup 함수로 하면 =VLOOKUP(F2,B2:D11,3,FALSE)처럼 표 범위와 열 번호 3을 직접 세어 넣어야 합니다.

vlookup은 '이 표의 세 번째 칸'이라고 번호로 부르고, xlookup은 '저 줄의 값'이라고 줄을 직접 가리켜요.

가로 방향 표를 조회하던 HLOOKUP도 xlookup으로 대신할 수 있어요.
행과 열 두 조건을 모두 맞춰 교차 값을 찾는 양방향 조회는 xlookup 안에 xlookup을 넣는 중첩 수식으로 풀며, 합계가 필요하면 엑셀 함수 더하기와 함께 쓸 수 있습니다.

xlookup 안 될 때는 버전부터, 큰 표는 이진 검색도 있어요

수식을 쳤는데 함수 이름을 못 알아본다면 엑셀 버전을 먼저 보세요.
xlookup은 Microsoft 365용 엑셀, 엑셀 2024, 엑셀 2021과 각 Mac 버전, iPad, iPhone, Android용 엑셀에서 쓸 수 있고 엑셀 2016과 2019에서는 쓸 수 없어요.

새 버전에서 만든 xlookup 파일을 엑셀 2016이나 2019에서 열 수 있더라도 그 버전에서 새로 xlookup을 입력해 쓸 수는 없어요. 파일을 받는 쪽 버전도 미리 확인하세요.

정렬된 큰 표에서 빠르게 찾고 싶다면 여섯 번째 칸에 2나 -2를 넣어요.
2는 오름차순 정렬을 전제로 하고, -2는 내림차순 정렬을 전제로 이진 검색을 합니다. 정렬이 맞지 않으면 잘못된 결과가 나올 수 있어서 정렬 상태를 꼭 확인해야 해요.

수식이 어떻게 계산되는지 단계별로 보고 싶다면 수식 셀을 선택한 뒤 수식 탭의 수식 감사에서 수식 평가를 눌러 보세요.
다른 함수도 함께 익히려면 엑셀 함수 모음과 엑셀 평균 함수부터 이어서 보면 좋습니다.

자주 묻는 질문

xlookup 함수의 가장 기본적인 사용법은 무엇인가요?

=XLOOKUP(찾을 값, 찾을 범위, 반환 범위)처럼 앞의 세 칸만 채우면 돼요. 찾을 범위에서 첫 번째로 일치하는 칸을 찾고, 반환 범위에서 같은 위치에 있는 값을 가져옵니다.

xlookup에서 찾는 값이 없을 때 #N/A 대신 문구를 보여 줄 수 있나요?

네, 네 번째 칸 if_not_found에 "상품 없음"처럼 문구를 넣으면 돼요. 이 칸을 비우고 일치하는 값이 없으면 #N/A가 나옵니다.

vlookup 대신 xlookup을 쓰면 왼쪽 열도 가져올 수 있나요?

네, xlookup은 찾는 범위와 가져올 범위를 따로 지정하기 때문에 반환 열이 조회 열의 왼쪽이든 오른쪽이든 같은 행의 값을 가져와요.

xlookup으로 마지막에 입력된 값을 찾으려면 어떻게 하나요?

여섯 번째 칸 search_mode에 -1을 넣으면 마지막 항목부터 거꾸로 찾아요. =XLOOKUP(A2,B2:B100,C2:C100,,0,-1)처럼 쓰고, 같은 값이 여러 번 있으면 가장 아래 행의 값이 나옵니다.

엑셀 2019에서 xlookup이 인식되지 않아요.

엑셀 2016과 2019에서는 xlookup을 쓸 수 없어요. Microsoft 365용 엑셀, 엑셀 2021, 엑셀 2024에서 쓸 수 있으니 설치된 버전부터 확인하세요.

엑셀 함수 바로 이어서 보기

xlookup을 익혔다면 같은 조회 계열과 집계 함수를 이어서 보세요.
수식 하나로 표를 정리하는 속도가 확실히 빨라집니다.