엑셀 XLOOKUP 함수는 사번이나 상품코드처럼 하나의 값을 기준으로 원하는 데이터를 찾을 때 편리합니다.
하지만 실제 업무에서는 하나의 조건만으로 원하는 데이터를 구분하기 어려운 경우가 있습니다.
예를 들어 같은 이름을 가진 직원이 여러 명이라면
이름 + 부서
두 가지 조건을 함께 확인해야 정확한 직원을 찾을 수 있습니다.
상품 데이터에서도
상품명 + 규격
또는
상품코드 + 거래처 + 날짜
처럼 여러 조건을 모두 만족하는 값을 조회해야 할 수 있습니다.
XLOOKUP 함수에서는 조건식을 곱하는 방법을 이용해 이러한 2개 이상의 다중조건 조회를 만들 수 있습니다.
이번 글에서는 엑셀 XLOOKUP 다중조건 조회 방법과 2개, 3개 이상의 조건으로 원하는 값을 찾는 방법을 예제를 통해 알아보겠습니다.
XLOOKUP 기본 사용법부터 알아보기
먼저 XLOOKUP 함수의 기본 구조는 다음과 같습니다.
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
일반적인 조회에서는 다음과 같이 간단하게 사용할 수 있습니다.
=XLOOKUP(F2,A2,B2,”검색 결과 없음”)
F2의 값을 A2에서 찾은 다음 같은 행에 있는 B열의 값을 가져오는 수식입니다.
예를 들어
A열 : 사번
B열 : 이름
으로 되어 있다면 사번을 입력해 해당 직원의 이름을 조회할 수 있습니다.
하지만 두 가지 이상의 조건을 동시에 만족하는 값을 찾아야 한다면 수식을 조금 다르게 구성해야 합니다.
XLOOKUP 다중조건 조회 원리
XLOOKUP으로 다중조건을 조회할 때 가장 많이 사용하는 기본 형태는 다음과 같습니다.
=XLOOKUP(1,(조건1)*(조건2),결과범위,”검색 결과 없음”)
여기에서 핵심은
찾을 값을 1로 지정하고 각각의 조건식을 곱하는 것입니다.
엑셀에서 조건식의 결과는 TRUE 또는 FALSE로 만들어집니다.
계산 과정에서는 이를 각각 1과 0처럼 활용할 수 있습니다.
두 조건을 곱하면
TRUE × TRUE → 1
TRUE × FALSE → 0
FALSE × TRUE → 0
FALSE × FALSE → 0
이 됩니다.
즉, 두 조건을 모두 만족하는 행에서만 1이 만들어집니다.
XLOOKUP이 이 1을 찾아 같은 위치의 결과값을 반환하도록 만드는 것이 다중조건 조회의 기본 원리입니다.
XLOOKUP 2개 조건으로 값 찾기
다음과 같은 직원 데이터가 있다고 가정해 보겠습니다.
| 이름 | 부서 | 직급 | 실적 |
|---|---|---|---|
| 김철수 | 영업팀 | 대리 | 120 |
| 이영희 | 총무팀 | 과장 | 80 |
| 박민수 | 영업팀 | 사원 | 95 |
| 김철수 | 개발팀 | 과장 | 130 |
| 최영수 | 영업팀 | 과장 | 150 |
여기에서는 김철수라는 이름이 두 번 등장합니다.
따라서 이름만 검색해서는 어떤 김철수의 정보를 가져와야 하는지 구분하기 어렵습니다.
이때
이름 + 부서
두 조건을 사용하면 됩니다.
F2 셀에는 이름을 입력하고 G2에는 부서를 입력한다고 가정하겠습니다.
F2 : 김철수
G2 : 개발팀
해당 직원의 직급을 찾으려면 다음 수식을 사용합니다.
=XLOOKUP(1,(A2=F2)*(B2=G2),C2,”검색 결과 없음”)
결과는
과장
이 됩니다.
2개 조건 수식 자세히 살펴보기
앞에서 사용한 수식은 다음과 같습니다.
=XLOOKUP(1,(A2=F2)*(B2=G2),C2,”검색 결과 없음”)
먼저
(A2=F2)
는 A열의 이름이 F2에 입력한 김철수와 같은지 확인합니다.
다음으로
(B2=G2)
는 B열의 부서가 G2의 개발팀과 같은지 확인합니다.
두 조건 사이에 *를 사용했기 때문에 두 조건을 모두 만족해야 결과가 1이 됩니다.
XLOOKUP은 이 1이 나타나는 위치를 찾고 같은 행의 C열에 있는 직급을 반환합니다.
따라서
김철수 + 개발팀
두 조건을 모두 만족하는 직원의 직급인 과장이 조회됩니다.
XLOOKUP 3개 조건으로 값 찾기
XLOOKUP 다중조건 조회는 조건을 세 개 이상으로 늘리는 것도 가능합니다.
예를 들어 다음 조건을 모두 만족하는 직원의 실적을 찾는다고 가정하겠습니다.
이름 : 김철수
부서 : 개발팀
직급 : 과장
조건 입력 셀을 다음과 같이 사용합니다.
F2 : 이름
G2 : 부서
H2 : 직급
수식은 다음과 같습니다.
=XLOOKUP(1,(A2=F2)(B2=G2)(C2=H2),D2,”검색 결과 없음”)
기본적인 원리는 2개 조건과 같습니다.
각 조건을 *로 연결하면 됩니다.
즉,
=XLOOKUP(1,(조건1)(조건2)(조건3),결과범위)
형태입니다.
모든 조건이 TRUE인 행에서만 1이 만들어지므로 세 가지 조건을 모두 만족하는 데이터의 값을 가져올 수 있습니다.
4개 이상의 조건도 가능할까?
조건을 더 추가하는 것도 가능합니다.
예를 들어 데이터가
A열 : 상품명
B열 : 규격
C열 : 거래처
D열 : 지역
E열 : 가격
으로 구성되어 있다고 가정하겠습니다.
상품명, 규격, 거래처, 지역을 모두 확인해서 가격을 가져오려면 다음과 같은 형태로 만들 수 있습니다.
=XLOOKUP(1,(A2=G2)(B2=H2)(C2=I2)*(D2=J2),E2,”검색 결과 없음”)
조건을 추가할 때마다
*(새로운 조건)
을 추가하면 됩니다.
다만 조건이 많아질수록 수식이 길어지므로 각 조건 범위와 결과 범위를 정확하게 지정하는 것이 중요합니다.
다중조건으로 여러 열 한 번에 조회하기
XLOOKUP은 조건에 맞는 하나의 셀뿐만 아니라 여러 열의 결과를 한꺼번에 반환하도록 구성할 수도 있습니다.
예를 들어 데이터가
A열 : 이름
B열 : 부서
C열 : 직급
D열 : 실적
E열 : 연락처
로 되어 있다고 가정하겠습니다.
F2에 이름, G2에 부서를 입력하고 조건에 맞는 직원의
직급 + 실적 + 연락처
를 한 번에 가져오려면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(1,(A2=F2)*(B2=G2),C2,”검색 결과 없음”)
결과 범위를 C2으로 여러 열 지정했기 때문에 조회된 행의 직급, 실적, 연락처가 옆으로 자동 표시됩니다.
동적 배열을 지원하는 엑셀에서는 이러한 방식으로 여러 결과 열을 한 번에 가져올 수 있습니다.
XLOOKUP 다중조건에서 결과가 없을 때
조건을 모두 만족하는 데이터가 없다면 오류가 발생할 수 있습니다.
XLOOKUP의 네 번째 인수를 이용하면 별도의 IFERROR 함수 없이도 원하는 문구를 표시할 수 있습니다.
예를 들어
=XLOOKUP(1,(A2=F2)*(B2=G2),C2,”해당 직원 없음”)
처럼 사용할 수 있습니다.
조건에 맞는 데이터가 없다면
해당 직원 없음
이라고 표시됩니다.
조회 화면을 만들어 다른 사람이 사용하도록 할 때는 오류값보다 이런 안내 문구를 표시하는 것이 알아보기 쉽습니다.
숫자 조건을 포함한 다중조건 조회
다중조건이 반드시 문자일 필요는 없습니다.
숫자 비교 조건도 함께 사용할 수 있습니다.
예를 들어
부서가 영업팀이면서 실적이 100 이상인 첫 번째 직원
의 이름을 찾는다고 가정하겠습니다.
F2에 영업팀, G2에 100을 입력했다면 다음과 같은 수식을 사용할 수 있습니다.
=XLOOKUP(1,(B2=F2)*(D2>=G2),A2,”검색 결과 없음”)
여기에서는
(B2=F2)
로 부서를 비교하고
(D2>=G2)
로 실적이 기준값 이상인지 확인합니다.
따라서 문자 조건과 숫자 조건을 함께 사용할 수도 있습니다.
다만 이 조건을 만족하는 사람이 여러 명이라면 XLOOKUP은 기본적인 검색 방향에서 첫 번째로 일치하는 결과를 반환합니다.
날짜 조건으로 XLOOKUP 다중조건 조회하기
날짜 데이터에서도 같은 방법을 사용할 수 있습니다.
예를 들어
A열 : 거래일
B열 : 거래처
C열 : 상품명
D열 : 금액
으로 되어 있다고 가정하겠습니다.
F2에 거래일, G2에 거래처를 입력해서 해당 거래의 금액을 찾으려면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(1,(A2=F2)*(B2=G2),D2,”검색 결과 없음”)
날짜가 정상적인 엑셀 날짜값으로 입력되어 있다면 다른 조건과 동일한 방식으로 비교할 수 있습니다.
조건을 직접 입력해서 조회하기
조건을 반드시 별도의 셀에 입력할 필요는 없습니다.
수식 안에 직접 조건을 지정하는 것도 가능합니다.
예를 들어
영업팀 + 과장
조건으로 이름을 찾으려면 다음과 같이 사용할 수 있습니다.
=XLOOKUP(1,(B2=”영업팀”)*(C2=”과장”),A2,”검색 결과 없음”)
하지만 실제 업무용 문서에서는 조건을 셀에 입력하거나 드롭다운으로 선택하도록 구성하는 것이 수식을 수정하지 않고 반복적으로 조회하기 편리합니다.
드롭다운과 XLOOKUP 다중조건 연결하기
다중조건 XLOOKUP을 더욱 편리하게 사용하려면 조건 셀에 드롭다운을 만들 수 있습니다.
예를 들어
F2 : 부서 선택
G2 : 직급 선택
으로 구성합니다.
F2에서는
영업팀
개발팀
총무팀
을 선택하고 G2에서는
사원
대리
과장
을 선택하도록 만듭니다.
그리고 다음 수식을 사용합니다.
=XLOOKUP(1,(B2=F2)*(C2=G2),A2,”검색 결과 없음”)
이제 드롭다운에서
영업팀 + 과장
을 선택하면 두 조건을 만족하는 첫 번째 직원의 이름이 자동으로 나타납니다.
조건을 변경하면 결과도 자동으로 변경되므로 간단한 조회 화면을 만들 수 있습니다.
조건 중 하나를 ‘전체’로 선택하려면?
드롭다운을 사용하다 보면 특정 조건을 무시하고 조회하고 싶은 경우가 있습니다.
예를 들어 F2가 부서, G2가 직급이라고 가정하겠습니다.
각 드롭다운에 전체 항목을 추가한 뒤 조건을 선택적으로 적용하도록 구성할 수 있습니다.
예를 들어 다음과 같은 형태를 사용할 수 있습니다.
=XLOOKUP(1,IF(F2=”전체”,1,B2=F2)*IF(G2=”전체”,1,C2=G2),A2,”검색 결과 없음”)
F2가 전체라면 부서 조건은 모든 행을 통과시키고, G2가 특정 직급이라면 직급 조건만 적용하는 방식입니다.
다만 이렇게 수식이 복잡해지고 여러 결과를 보여줘야 하는 상황이라면 XLOOKUP보다는 FILTER 함수를 사용하는 편이 더 자연스러운 경우가 많습니다.
같은 조건을 만족하는 값이 여러 개라면?
XLOOKUP 다중조건에서 반드시 알아둬야 하는 부분입니다.
예를 들어 다음 조건을 사용한다고 가정하겠습니다.
부서 = 영업팀
직급 = 과장
영업팀 과장이 한 명이라면 문제가 없습니다.
하지만 같은 조건을 만족하는 직원이 3명이라면 XLOOKUP은 기본적으로 첫 번째로 일치하는 결과 하나를 반환합니다.
따라서 XLOOKUP 다중조건은
여러 조건으로 하나의 대응값을 찾는 경우
에 적합합니다.
조건을 만족하는 모든 데이터를 가져와야 한다면 FILTER 함수를 사용하는 것이 좋습니다.
XLOOKUP과 FILTER 다중조건 차이
예를 들어
영업팀 + 과장
조건을 만족하는 직원을 찾는다고 가정해 보겠습니다.
XLOOKUP에서는 다음과 같이 사용할 수 있습니다.
=XLOOKUP(1,(B2=F2)*(C2=G2),A2,”검색 결과 없음”)
조건을 만족하는 첫 번째 이름을 가져옵니다.
반면 FILTER 함수는
=FILTER(A2,(B2=F2)*(C2=G2),”검색 결과 없음”)
처럼 사용할 수 있습니다.
FILTER는 해당 조건을 만족하는 모든 행을 추출합니다.
따라서 용도를 구분하면 쉽습니다.
XLOOKUP
여러 조건을 만족하는 결과 중 하나의 대응값 조회
FILTER
여러 조건을 만족하는 모든 데이터 추출
어떤 함수가 더 좋은 것이 아니라 필요한 결과에 따라 선택하면 됩니다.
XLOOKUP 다중조건과 문자열 결합 방법
다중조건 조회에는 조건식을 곱하는 방법 외에도 여러 값을 하나의 문자열로 연결해서 조회하는 방식이 있습니다.
예를 들어
=XLOOKUP(F2&G2,A2&B2,C2,”검색 결과 없음”)
처럼 사용할 수 있습니다.
F2와 G2를 하나의 값처럼 연결하고 A열과 B열도 같은 방식으로 연결해 비교하는 것입니다.
다만 데이터에 따라 값이 우연히 같은 문자열로 합쳐질 가능성을 줄이려면 구분자를 추가하는 방법도 있습니다.
예를 들어
=XLOOKUP(F2&”|”&G2,A2&”|”&B2,C2,”검색 결과 없음”)
처럼 사용할 수 있습니다.
하지만 일반적인 다중조건에서는
=XLOOKUP(1,(조건1)*(조건2),결과범위)
형태가 조건의 의미를 확인하기 쉬워 많이 활용됩니다.
XLOOKUP 다중조건이 안 될 때 확인할 사항
수식을 입력했는데 원하는 결과가 나오지 않는다면 몇 가지 부분을 확인해 볼 필요가 있습니다.
먼저 각 조건 범위의 행 개수가 동일해야 합니다.
예를 들어
A2
과
B2
을 조건으로 함께 사용하면 범위 크기가 다르기 때문에 문제가 발생할 수 있습니다.
조건 범위와 결과 범위는 가능하면 동일한 시작 행과 끝 행으로 맞춰주는 것이 좋습니다.
또한 눈으로는 같은 값처럼 보여도 데이터 앞뒤에 공백이 포함되어 있으면 정확하게 일치하지 않을 수 있습니다.
숫자가 문자 형식으로 저장되어 있거나 날짜 데이터 형식이 서로 다른 경우에도 원하는 결과가 나오지 않을 수 있으므로 함께 확인해 보는 것이 좋습니다.
XLOOKUP 다중조건 조회 방법 정리
XLOOKUP에서 2개 이상의 조건을 사용하려면 기본적으로 다음 구조를 기억하면 됩니다.
=XLOOKUP(1,(조건1)*(조건2),결과범위,”검색 결과 없음”)
예를 들어
이름 + 부서
조건으로 직급을 조회하려면
=XLOOKUP(1,(A2=F2)*(B2=G2),C2,”검색 결과 없음”)
을 사용할 수 있습니다.
조건이 세 개라면
=XLOOKUP(1,(A2=F2)(B2=G2)(C2=H2),D2,”검색 결과 없음”)
처럼 조건을 추가합니다.
핵심은 각 조건식을 *로 연결하는 것입니다.
모든 조건을 만족하는 행에서만 1이 만들어지고 XLOOKUP이 해당 위치의 결과를 반환합니다.
다만 XLOOKUP은 조건을 만족하는 값이 여러 개일 때 기본적으로 첫 번째 일치 결과를 가져오기 때문에 모든 결과를 목록으로 추출해야 한다면 FILTER 함수를 사용하는 것이 더 적합합니다.
따라서
2개 이상의 조건으로 하나의 값 찾기 → XLOOKUP
2개 이상의 조건으로 여러 데이터 추출 → FILTER
로 구분해 두면 실무에서 함수를 선택하기 쉽습니다.
직원 조회, 상품 가격 조회, 거래내역 검색처럼 하나의 조건만으로 데이터를 구분하기 어려운 엑셀 파일을 사용한다면 XLOOKUP의 기본 사용법과 함께 다중조건 조회 방법도 알아두시기 바랍니다.