엑셀에서 사번을 입력하면 직원 이름을 가져오거나, 상품코드를 이용해 상품명과 가격을 찾는 작업에는 조회 함수가 자주 사용됩니다.
과거에는 이런 작업에 VLOOKUP 함수를 많이 사용했지만, 최신 엑셀 환경에서는 XLOOKUP 함수를 이용하면 보다 편리하게 데이터를 조회할 수 있습니다.
XLOOKUP은 찾을 범위와 결과를 가져올 범위를 각각 지정하기 때문에 수식을 이해하기 쉽고, VLOOKUP에서 불편했던 왼쪽 방향 조회도 가능합니다.
또한 검색 결과가 없을 때 표시할 문구를 함수 안에서 지정할 수 있으며 여러 조건을 결합한 조회에도 활용할 수 있습니다.
이번 글에서는 엑셀 XLOOKUP 함수 사용법부터 VLOOKUP과의 차이, 다중조건으로 데이터를 조회하는 방법까지 알아보겠습니다.
엑셀 XLOOKUP 함수란?
XLOOKUP은 특정 값을 범위에서 찾은 다음 해당 위치에 대응하는 다른 범위의 값을 반환하는 함수입니다.
기본 구조는 다음과 같습니다.
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
각 인수의 의미는 다음과 같습니다.
lookup_value
찾으려는 값입니다.
lookup_array
찾을 값이 들어 있는 범위입니다.
return_array
검색 결과로 가져올 값이 들어 있는 범위입니다.
if_not_found
일치하는 값이 없을 때 표시할 내용을 지정합니다.
match_mode
정확히 일치하는 값 또는 근사값 등 일치 방법을 지정합니다.
search_mode
검색을 시작하는 방향이나 검색 방식을 지정합니다.
처음 XLOOKUP을 사용할 때는 모든 인수를 알 필요는 없습니다.
가장 기본적으로는 다음 세 가지 인수만 이해해도 충분합니다.
=XLOOKUP(찾을값,찾을범위,가져올범위)
XLOOKUP 함수 기본 사용법
다음과 같은 직원 명단이 있다고 가정해 보겠습니다.
| 사번 | 이름 | 부서 | 직급 |
|---|---|---|---|
| A001 | 김철수 | 영업팀 | 대리 |
| A002 | 이영희 | 총무팀 | 과장 |
| A003 | 박민수 | 영업팀 | 사원 |
| A004 | 김민지 | 개발팀 | 대리 |
| A005 | 최영수 | 영업팀 | 과장 |
데이터가 A2에 입력되어 있고 F2 셀에 조회할 사번을 입력한다고 가정하겠습니다.
F2에
A003
을 입력한 뒤 해당 직원의 이름을 가져오려면 다음 수식을 사용합니다.
=XLOOKUP(F2,A2,B2)
수식을 나눠보면 다음과 같습니다.
F2
찾으려는 사번
A2
사번을 검색할 범위
B2
검색에 성공했을 때 이름을 가져올 범위
따라서 A003에 해당하는
박민수
가 결과로 표시됩니다.
XLOOKUP으로 부서와 직급 조회하기
같은 방식으로 부서를 가져오려면 반환 범위만 변경하면 됩니다.
=XLOOKUP(F2,A2,C2)
직급을 가져오려면
=XLOOKUP(F2,A2,D2)
을 사용할 수 있습니다.
즉, XLOOKUP의 기본 원리는 간단합니다.
어떤 값을 찾을 것인지
↓
어디에서 찾을 것인지
↓
어디의 값을 가져올 것인지
세 가지를 지정한다고 생각하면 됩니다.
XLOOKUP에서 검색 결과가 없을 때 처리하기
조회할 값이 원본 데이터에 없다면 기본적으로 오류가 나타날 수 있습니다.
XLOOKUP에서는 네 번째 인수를 이용해 검색 결과가 없을 때 표시할 문구를 직접 지정할 수 있습니다.
예를 들어 다음과 같이 작성합니다.
=XLOOKUP(F2,A2,B2,”검색 결과 없음”)
F2에 존재하지 않는 사번을 입력하면 오류 대신
검색 결과 없음
이라고 표시됩니다.
기존 조회 수식에서 오류를 처리하기 위해 IFERROR 함수를 추가했던 것보다 수식을 간결하게 만들 수 있는 경우가 많습니다.
XLOOKUP으로 여러 열 한 번에 가져오기
XLOOKUP의 반환 범위를 여러 열로 지정하면 여러 결과를 한 번에 가져올 수도 있습니다.
예를 들어 사번을 검색해서
이름
부서
직급
을 한꺼번에 표시하고 싶다면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(F2,A2,B2,”검색 결과 없음”)
F2에 A003을 입력했다면 결과가
박민수 | 영업팀 | 사원
처럼 여러 셀에 자동으로 표시됩니다.
동적 배열을 지원하는 XLOOKUP의 편리한 활용 방법 중 하나입니다.
VLOOKUP 함수 기본 구조
XLOOKUP과의 차이를 이해하기 위해 VLOOKUP 함수도 간단하게 살펴보겠습니다.
VLOOKUP의 기본 구조는 다음과 같습니다.
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
예를 들어 A2에서 F2의 사번을 찾아 이름을 가져온다면 다음과 같이 사용할 수 있습니다.
=VLOOKUP(F2,A2,2,FALSE)
여기에서 2는 지정한 표 범위에서 두 번째 열의 값을 가져오라는 의미입니다.
XLOOKUP으로 같은 작업을 하면 다음과 같습니다.
=XLOOKUP(F2,A2,B2)
두 함수 모두 결과는 같지만 데이터를 지정하는 방법이 다릅니다.
XLOOKUP과 VLOOKUP 차이
두 함수의 대표적인 차이를 정리하면 다음과 같습니다.
| 구분 | XLOOKUP | VLOOKUP |
|---|---|---|
| 조회 범위 | 검색·반환 범위를 각각 지정 | 전체 표 범위 지정 |
| 반환 열 지정 | 범위로 지정 | 열 번호로 지정 |
| 왼쪽 조회 | 가능 | 기본 방식으로는 어려움 |
| 정확히 일치 | 기본 동작 | FALSE 지정 필요 |
| 값이 없을 때 처리 | 함수 내부에서 지정 가능 | 보통 IFERROR 등 추가 |
| 여러 열 반환 | 가능 | 일반적으로 열별 수식 필요 |
| 열 삽입 영향 | 비교적 적음 | 열 번호가 바뀌면 주의 필요 |
특히 XLOOKUP에서는 반환할 열을 2, 3, 4와 같은 번호로 지정하지 않고 실제 범위를 직접 선택합니다.
따라서 표 중간에 열이 추가되는 상황에서도 수식을 이해하고 관리하기 편리한 경우가 많습니다.
XLOOKUP은 왼쪽으로도 조회 가능
VLOOKUP을 사용하면서 자주 겪는 불편함 중 하나가 찾을 값이 표의 왼쪽에 있어야 한다는 점입니다.
XLOOKUP은 검색 범위와 반환 범위를 각각 지정하므로 이러한 제한이 없습니다.
예를 들어
A열 : 사번
B열 : 이름
으로 되어 있을 때 이름을 이용해 사번을 찾고 싶다고 가정하겠습니다.
F2에 박민수를 입력했다면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(F2,B2,A2)
B열에서 박민수를 찾은 뒤 A열의 사번을 가져옵니다.
결과는
A003
이 됩니다.
즉, 오른쪽 → 왼쪽 방향으로도 자유롭게 조회할 수 있습니다.
XLOOKUP 정확히 일치하는 값 찾기
XLOOKUP은 기본적으로 정확히 일치하는 값을 찾도록 사용할 수 있어 일반적인 코드·사번·상품명 검색이 간단합니다.
예를 들어
=XLOOKUP(F2,A2,B2)
처럼 작성하면 F2와 일치하는 값을 A열에서 찾아 B열의 결과를 가져옵니다.
VLOOKUP에서 정확히 일치하도록 만들기 위해 자주 사용하는
FALSE
를 기본적인 XLOOKUP 수식에서는 따로 입력하지 않아도 됩니다.
XLOOKUP match_mode 사용법
조금 더 세밀하게 조회하려면 XLOOKUP의 match_mode를 사용할 수 있습니다.
대표적인 값은 다음과 같습니다.
0
정확히 일치
-1
정확히 일치하는 값이 없으면 다음으로 작은 값
1
정확히 일치하는 값이 없으면 다음으로 큰 값
2
와일드카드 문자 검색
일반적인 사번이나 상품코드 조회에서는 기본값인 정확히 일치를 사용하는 경우가 많습니다.
XLOOKUP 와일드카드 검색하기
XLOOKUP에서는 와일드카드를 이용한 조회도 가능합니다.
예를 들어 A열에 상품명이 있고 F2에 상품명의 일부를 입력해 검색한다고 가정하겠습니다.
다음과 같은 형태로 사용할 수 있습니다.
=XLOOKUP(““&F2&”“,A2,B2,”검색 결과 없음”,2)
여기에서 *는 여러 문자를 대신하는 와일드카드입니다.
F2에 노트북이라고 입력하면 앞뒤에 다른 문자가 포함되어 있더라도 조건에 맞는 첫 번째 값을 조회하는 방식으로 활용할 수 있습니다.
단, 일치하는 데이터가 여러 개라면 XLOOKUP은 여러 행의 목록을 모두 추출하는 용도가 아니라 조건에 맞는 하나의 조회 결과를 찾는 용도라는 점을 알아두는 것이 좋습니다.
여러 결과를 모두 가져오려면 FILTER 함수가 더 적합합니다.
XLOOKUP 다중조건 조회 방법
XLOOKUP에서도 두 가지 이상의 조건을 결합해 원하는 값을 조회할 수 있습니다.
예를 들어 다음과 같은 데이터가 있다고 가정하겠습니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김철수 | 영업팀 | 대리 | 120 |
| 이영희 | 총무팀 | 과장 | 80 |
| 박민수 | 영업팀 | 사원 | 95 |
| 김민지 | 개발팀 | 대리 | 110 |
| 최영수 | 영업팀 | 과장 | 150 |
F2에는 부서, G2에는 직급을 입력한다고 가정하겠습니다.
F2 = 영업팀
G2 = 과장
두 조건을 모두 만족하는 직원의 이름을 조회하려면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(1,(B2=F2)*(C2=G2),A2,”검색 결과 없음”)
처음 보면 조금 복잡해 보이지만 원리를 이해하면 어렵지 않습니다.
XLOOKUP 다중조건 수식 원리
먼저 다음 부분을 살펴보겠습니다.
(B2=F2)
각 행의 부서가 F2와 같은지 확인합니다.
조건을 만족하면 TRUE, 만족하지 않으면 FALSE가 만들어집니다.
다음은
(C2=G2)
각 행의 직급이 G2와 같은지 확인합니다.
두 조건 사이에 *를 사용하면 두 조건을 모두 만족하는 행은 계산 과정에서 1에 해당하는 값이 됩니다.
따라서
=XLOOKUP(1,(조건1)*(조건2),결과범위)
형태로 작성하면 모든 조건을 만족하는 첫 번째 결과를 조회할 수 있습니다.
3개 조건으로 XLOOKUP 조회하기
같은 원리를 이용하면 조건을 더 추가할 수도 있습니다.
예를 들어
부서
직급
이름
세 조건을 모두 만족하는 행의 실적을 가져온다고 가정하겠습니다.
조건 셀이
F2 : 부서
G2 : 직급
H2 : 이름
이라면 다음과 같이 작성할 수 있습니다.
=XLOOKUP(1,(B2=F2)(C2=G2)(A2=H2),D2,”검색 결과 없음”)
조건을 추가할 때마다
*(새로운 조건)
형태로 연결하면 됩니다.
다중조건에서 같은 결과가 여러 개라면?
XLOOKUP 다중조건을 사용할 때 주의할 부분이 있습니다.
예를 들어
부서 = 영업팀
직급 = 대리
조건을 만족하는 직원이 여러 명이라면 XLOOKUP은 기본적인 사용 방식에서 일치하는 첫 번째 결과를 반환합니다.
따라서 조건에 해당하는 모든 직원을 목록으로 표시하고 싶다면 XLOOKUP보다 FILTER 함수가 적합합니다.
예를 들어 다음과 같이 사용할 수 있습니다.
=FILTER(A2,(B2=F2)*(C2=G2),”검색 결과 없음”)
이 수식은 두 조건을 만족하는 모든 행을 가져옵니다.
XLOOKUP과 FILTER 차이는?
XLOOKUP과 FILTER는 모두 데이터를 조회할 때 활용할 수 있지만 목적이 조금 다릅니다.
XLOOKUP
특정 값에 대응하는 결과를 찾아올 때 적합
예)
사번 입력 → 직원 이름 조회
상품코드 입력 → 가격 조회
FILTER
조건에 맞는 여러 데이터를 목록으로 추출할 때 적합
예)
영업팀 직원 전체 조회
실적 100 이상 직원 전체 조회
따라서
하나의 대응값을 찾는다 → XLOOKUP
조건에 맞는 여러 행을 가져온다 → FILTER
라고 구분하면 이해하기 쉽습니다.
XLOOKUP으로 마지막 값 찾기
XLOOKUP의 search_mode를 이용하면 검색 방향도 지정할 수 있습니다.
기본적인 검색에서는 앞쪽에서부터 일치하는 값을 찾지만 뒤쪽에서부터 검색하도록 설정할 수도 있습니다.
예를 들어 A열에 고객명이 반복되어 있고 B열에 거래금액이 입력되어 있다고 가정하겠습니다.
특정 고객의 가장 마지막 거래금액을 가져오려면 다음과 같은 형태로 사용할 수 있습니다.
=XLOOKUP(F2,A2,B2,”검색 결과 없음”,0,-1)
마지막 -1은 뒤에서부터 검색하도록 지정하는 search_mode입니다.
같은 값이 여러 번 등장하는 거래내역에서 가장 최근에 입력된 항목 등을 조회할 때 응용할 수 있습니다.
다만 실제 ‘최신 거래’를 의미하려면 원본 데이터가 날짜나 입력 순서 기준으로 적절하게 구성되어 있는지도 함께 확인해야 합니다.
XLOOKUP에서 #N/A 오류가 발생한다면?
XLOOKUP에서 찾으려는 값이 검색 범위에 존재하지 않으면 #N/A 오류가 나타날 수 있습니다.
이 경우 네 번째 인수를 이용하면 됩니다.
=XLOOKUP(F2,A2,B2,”검색 결과 없음”)
이렇게 설정하면 일치하는 값이 없을 때 #N/A 대신 검색 결과 없음이 표시됩니다.
또한 눈으로 보기에는 같은 값인데 조회되지 않는다면 앞뒤 공백이나 데이터 형식 차이 등도 확인해 보는 것이 좋습니다.
XLOOKUP 함수가 안 될 때 확인할 사항
XLOOKUP은 비교적 최신 엑셀 환경에서 사용할 수 있는 함수입니다.
수식을 입력했는데 XLOOKUP이라는 함수 자체가 인식되지 않거나 #NAME? 오류가 발생한다면 현재 사용하는 엑셀 버전에서 XLOOKUP을 지원하는지 확인할 필요가 있습니다.
지원되지 않는 환경에서는 VLOOKUP이나 INDEX와 MATCH 함수 조합 등 다른 조회 방법을 사용해야 할 수 있습니다.
XLOOKUP과 동적 배열 함수를 함께 익혀두면 좋은 이유
최근 엑셀의 데이터 조회와 정리에서는 XLOOKUP뿐만 아니라
FILTER
SORT
SORTBY
UNIQUE
같은 함수도 함께 활용할 수 있습니다.
각 함수의 역할을 간단하게 구분하면 다음과 같습니다.
XLOOKUP
원하는 값에 대응하는 데이터 조회
FILTER
조건에 맞는 데이터 목록 추출
UNIQUE
중복값 제거
SORT / SORTBY
데이터 자동 정렬
예를 들어 부서별 직원 전체를 조회하려면 FILTER를 사용하고, 사번 하나를 입력해 직원 정보를 가져오려면 XLOOKUP을 사용하는 식으로 목적에 맞게 선택하면 됩니다.
엑셀 XLOOKUP 함수 사용법 정리
XLOOKUP의 가장 기본적인 사용 방법은 다음과 같습니다.
=XLOOKUP(찾을값,찾을범위,가져올범위)
예를 들어 사번을 이용해 직원 이름을 조회하려면
=XLOOKUP(F2,A2,B2)
처럼 사용할 수 있습니다.
검색 결과가 없을 때 별도의 문구를 표시하려면
=XLOOKUP(F2,A2,B2,”검색 결과 없음”)
을 사용합니다.
여러 열을 한꺼번에 가져오려면
=XLOOKUP(F2,A2,B2,”검색 결과 없음”)
처럼 반환 범위를 여러 열로 지정할 수도 있습니다.
두 가지 조건을 모두 만족하는 결과를 조회하려면
=XLOOKUP(1,(B2=F2)*(C2=G2),A2,”검색 결과 없음”)
형태로 활용할 수 있습니다.
VLOOKUP과 비교했을 때 XLOOKUP은 검색 범위와 반환 범위를 각각 지정할 수 있고, 왼쪽 조회가 가능하며, 검색 실패 시 표시할 내용을 함수 안에서 설정할 수 있다는 점 등이 특징입니다.
따라서 XLOOKUP을 지원하는 엑셀을 사용하고 있다면 기본적인 데이터 조회부터 다중조건 검색까지 다양하게 활용할 수 있습니다.
특히 FILTER·UNIQUE·SORT와 같은 동적 배열 함수까지 함께 알아두면 단순 조회뿐만 아니라 검색, 추출, 중복 제거, 자동 정렬까지 연결된 엑셀 데이터 관리 기능을 만들 수 있습니다.
“엑셀 XLOOKUP 함수 사용법 (VLOOKUP 차이)”에 대한 1개의 생각