빠른 링크
XLOOKUP 구문과 단일 조건 예제
예제 1: Boolean(불리언) 논리로 다중 조건 XLOOKUP
예제 2: 연결(concatenation)으로 다중 조건 XLOOKUP
소개
XLOOKUP 함수에 대해 흔히 하는 오해 중 하나는 “조회 조건은 반드시 하나뿐이어야 한다”는 것입니다. 실제로는 여러 조건으로 값을 조회할 수 있으며, 그 방법은 크게 두 가지입니다. 하나는 Boolean 논리를 사용해 임시 조회 배열을 만드는 방법이고, 다른 하나는 각 조건을 결합(문자열 연결)하는 방법입니다. 데스크톱용 Excel(Windows/Mac)에서는 Excel 2021 이상 또는 Microsoft 365가 필요하며, 웹·태블릿·모바일에서도 사용할 수 있습니다.
Microsoft 365 Personal
Microsoft 365는 Word, Excel, PowerPoint 같은 Office 앱을 최대 5대의 기기에서 사용할 수 있고, OneDrive 1TB 등 여러 혜택을 포함합니다.
$100 at Microsoft
$100 at Amazon
XLOOKUP 구문과 단일 조건 예제
XLOOKUP 함수의 구문은 처음 보기에는 복잡해 보이지만 논리적입니다:
=XLOOKUP(a, b, c, d, e, f)- a (필수): 조회값(lookup value)
- b (필수): 조회 배열(lookup array)
- c (필수): 반환 배열(return array)
- d (선택): 조회값이 없을 경우 반환할 텍스트
- e (선택): 일치 모드(0 = 정확 일치(기본), -1 = 같거나 작은 값, 1 = 같거나 큰 값, 2 = 와일드카드)
- f (선택): 검색 모드(1 = 위→아래(기본), -1 = 아래→위, 2 = 이진 검색(오름차순), -2 = 이진 검색(내림차순))
예를 들어 데이터 유효성 검사 드롭다운에서 ID를 선택한 뒤 E2 셀에 아래 수식을 입력하면 점수 51을 반환합니다:
=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],"No match",0,1)
설명:
- E1: 조회할 값(예: 1323)이 있는 셀
- T_Scores[ID]: ID 열에서 조회
- T_Scores[Score]: 결과로 반환할 점수 열
- “No match”: 조회값이 없을 때 반환할 텍스트
- 0: 정확 일치
- 1: 위에서 아래로 검색
마지막 두 인수는 기본값이기 때문에 생략할 수 있습니다.
예제 1: Boolean 논리로 다중 조건 XLOOKUP
여러 조건이 있을 때 XLOOKUP을 사용하는 방식이 달라집니다.
상황: 여섯 종류의 음료를 여섯 개 국가에 판매하는 브랜드가 있고, 각 음료·국가 조합마다 담당 매니저가 배정되어 있다고 가정합니다.
목표: 음료와 국가를 입력하면 해당 조합의 매니저 이름과 매출을 반환하는 조회를 만들기.
문제: XLOOKUP은 보통 단일 조회값을 사용하도록 설계되어 있으므로, 여기서는 임시 배열을 만들어 여러 조건을 처리해야 합니다. Boolean 논리를 활용하면 각 조건의 일치 여부(TRUE/FALSE)를 1과 0으로 바꿔 곱셈을 통해 모든 조건이 충족되는 행만 1이 되도록 만들 수 있습니다.
아래 수식이 그 예시입니다(가독성을 위해 줄 바꿈):
=XLOOKUP(
1,
(T_Managers[Drink]=G1)*(T_Managers[Country]=G2),
T_Managers[[Manager]:[Sales]],
"No result"
)
과정 설명(중요한 단계):
- 조회 배열(b) 부분:
(T_Managers[Drink]=G1)*(T_Managers[Country]=G2)- 먼저 T_Managers[Drink]=G1 은 Drink 열의 각 행이 G1(예: Coffee)과 같은지 비교해 TRUE/FALSE의 배열을 생성합니다.
- 다음으로 T_Managers[Country]=G2 는 Country 열에서 G2(예: Spain)과 비교한 또 다른 TRUE/FALSE 배열을 생성합니다.
- 두 배열을 곱하면 TRUE는 1로, FALSE는 0으로 간주되어 두 조건을 모두 만족하는 행만 1이 됩니다.
예시 중간 결과(개념적으로):
{FALSE;FALSE;...;TRUE;TRUE;...}
{FALSE;...;TRUE;...;TRUE;...}곱셈 후:
{0;0;...;1;0;...}조회값(a)은 1입니다. 즉, 생성된 0/1 배열에서 처음 등장하는 1의 위치를 찾아 해당 행의 반환 배열(c)에서 값을 반환합니다.
반환 배열(c):
T_Managers[[Manager]:[Sales]]이 예에서는 해당 행의 Manager 열에서 Olivia를, Sales 열에서 346을 반환합니다.
추가 팁:
- 숫자 비교 같은 경우에는 등호(=) 외에도 >, < 등 비교 연산자를 사용할 수 있습니다.
- 여러 조건 중 “적어도 하나“에 일치하는 첫 결과를 원한다면 곱셈(*) 대신 덧셈(+)을 사용해 각 조건을 더한 다음 0보다 큰 값을 찾는 방식으로 응용할 수 있습니다.
예제 2: 결합(Concatenation)으로 다중 조건 XLOOKUP
다른 접근법은 각 조회값과 각 조회 배열을 문자열로 결합(앰퍼샌드 &)하는 것입니다. 이 방법은 이해하기 쉽고 구현도 간단하지만, 배열 요소를 문자열로 변환하는 방식 때문에 숫자 형식·공백·중복 조합 등에 유의해야 합니다.
같은 시나리오에서 음료와 국가를 입력해 매니저 이름과 매출을 반환하려면 아래 수식을 사용합니다:
=XLOOKUP(
G1&G2,
T_Managers[Drink]&T_Managers[Country],
T_Managers[[Manager]:[Sales]],
"No result"
)
과정:
- a: G1&G2 → 예: CoffeeSpain (공백 없이 결합)
- b: T_Managers[Drink]&T_Managers[Country] → 각 행에서 Drink와 Country 문자열을 결합한 배열(예: Apple juiceAustralia)
- c: 반환 배열은 매니저와 매출 열
- d: 일치하지 않으면 “No result” 반환
이 경우 각 조합(예: CoffeeSpain)은 테이블에서 유일하게 존재하므로 정확 일치만으로도 원하는 결과를 얻습니다.
주의사항:
- 결합 방식은 공백, 대소문자, 숫자 형식이 섞인 경우 의도치 않은 미스매치를 만들 수 있습니다. 필요하면 TRIM(), UPPER()/LOWER(), TEXT() 같은 함수를 사용해 표준화하세요.
- 동일한 조합이 테이블에 중복으로 존재하면 XLOOKUP은 첫 번째 일치 항목만 반환합니다.
요약: 결합 방식은 간단하지만 형식 일관성이 전제되어야 하고, Boolean 방식은 더 유연해 복잡한 논리(예: OR 조건, 범위 비교)를 다루기 좋습니다.
언제 어떤 방법을 선택해야 할까
- 단순한 일치(각 조합이 유일) → 결합(앰퍼샌드) 방식이 빠르고 직관적.
- 복잡한 비교(범위, >=, <=, OR, NOT 등) → Boolean 논리 방식이 더 유연.
- 성능: 매우 큰 테이블에서는 배열 연산이 느릴 수 있으니 테스트가 필요.
현실에서 실패하는 경우와 해결책
- 배열 크기 불일치: 조회 배열(b)와 반환 배열(c)의 행 수가 맞지 않으면 오류가 발생합니다. 항상 동일한 범위를 사용하세요.
- 빈값/공백 문제: 결합 방식은 공백/빈 셀이 있는 경우 잘못된 문자열을 만들 수 있습니다. TRIM(), IFERROR(), IFNA()로 보호하세요.
- 중복 항목: 결합 방식은 첫 번째 일치만 반환합니다. 모든 일치 항목을 원하면 FILTER 함수나 고급 필터링이 필요합니다.
- Excel 버전 문제: XLOOKUP은 구버전 Excel(예: 2019 이하)에는 없습니다. 대체로 INDEX/MATCH나 SUMPRODUCT를 고려하세요.
- 성능 저하: 매우 큰 데이터셋(수십만 행)에서는 배열 곱셈(*)이 느려질 수 있습니다. 필요한 경우 계산 옵션을 수동으로 변경하거나, 테이블을 요약해 사용하는 것이 좋습니다.
대체 접근법
- FILTER: 여러 조건에 일치하는 모든 행을 반환할 때 유용합니다.
- INDEX + MATCH(다중 기준): 전통적인 방법으로 호환성이 좋습니다. 다중 기준은 INDEX와 MATCH의 배열식을 통해 구현 가능합니다.
- SUMIFS / AVERAGEIFS: 집계(합계/평균)를 바로 구해야 할 때 편리합니다.
- VLOOKUP + helper column(보조열): 결합된 키를 보조 열로 만들어 VLOOKUP으로 조회하는 방법도 있습니다.
간단 비교:
- XLOOKUP(다중 조건, Boolean): 유연, 반환 열 여러 개 가능, 배열 연산 필요
- XLOOKUP(결합): 구현 단순, 문자열 형식에 민감
- FILTER: 모든 일치 항목 반환, 결과가 Spill(흘러나옴)
- INDEX/MATCH: 구버전 호환성 우수, 구현이 비교적 복잡
실무별 체크리스트
데이터 분석가:
- 조회 전 테이블이 정규화되어 있는지 확인
- 공백/형식 오류를 TRIM/텍스트 함수로 정리
- 성능 문제 탐지 시 샘플로 프로파일링
재무 담당자:
- 숫자 형식(소수점, 통화) 동기화
- 같은 조합의 중복 여부 확인(중복 시 집계 필요)
- 오류 반환값을 “No result” 대신 회계 기준 문구로 표준화
제품 매니저/운영:
- 드롭다운 값이 사용자 친화적인지 확인
- 변경 시 영향 범위를 문서화(테스트 케이스 포함)
개발/시스템 통합:
- 자동화 시 외부 데이터 소스의 인코딩·지역화(날짜/숫자) 확인
- 대량 데이터 처리 시 캐싱 또는 요약 테이블 도입 검토
치트 시트: 자주 쓰는 패턴
- Boolean(AND) 다중 조건:
=XLOOKUP(1, (범위1=값1)*(범위2=값2), 반환범위, "No result")- Boolean(OR) 다중 조건(첫 일치):
=XLOOKUP(1, (범위1=값1)+(범위2=값2), 반환범위, "No result")- 결합(Concatenate) 다중 조건:
=XLOOKUP(값1&값2, 범위1&범위2, 반환범위, "No result")- 모든 일치 항목 가져오기(FILTER 사용):
=FILTER(전체범위, (범위1=값1)*(범위2=값2), "No result")- INDEX/MATCH 다중 조건(예시):
=INDEX(반환범위, MATCH(1, (범위1=값1)*(범위2=값2), 0))(위 INDEX/MATCH 배열식은 Ctrl+Shift+Enter가 필요한 구버전이 있으니 환경에 따라 확인)
마인드셋/휴리스틱
- “조회는 항상 정상화된 키를 바라본다”: 중복·공백·형식 불일치는 미스매치의 주요 원인입니다.
- “첫 번째 일치는 의도된가?”: XLOOKUP과 FILTER의 차이는 단일 결과와 모든 결과를 반환하는지에 달려 있습니다.
- “성능을 미리 생각하라”: 작은 테스트셋에서 잘 작동해도 대용량에서 느릴 수 있습니다.
1줄 용어집
- XLOOKUP: Excel의 현대적 조회 함수, 단일/다중 열 반환 가능
- FILTER: 조건에 맞는 모든 행을 반환하는 함수
- INDEX/MATCH: 전통적 조회 조합, 호환성이 좋음
- Spill: 동적 배열 결과가 셀 아래로 흐르는 현상
예제 검증용 테스트 케이스(간단)
- 유효 케이스: 존재하는 Drink+Country 조합을 입력했을 때 올바른 Manager·Sales 반환
- 부재 케이스: 존재하지 않는 조합 시 “No result” 반환
- 중복 케이스: 동일 조합이 2개 이상일 때 첫 항목 반환(의도한 동작인지 확인)
- 형식 케이스: 공백/대소문자 다른 경우 일관된 결과(정규화 필요)
마무리 요약
XLOOKUP은 다중 조건 조회에 매우 강력한 도구이며, Boolean 논리와 문자열 결합 두 가지 방법으로 구현할 수 있습니다. Boolean 방식은 논리 표현이 유연하고 복잡한 비교에 적합하며, 결합 방식은 구현이 단순하지만 형식 일관성이 요구됩니다. 대규모 데이터나 복잡한 요구사항이 있는 경우 FILTER, INDEX/MATCH 같은 대체 방법도 고려하세요.
중요: XLOOKUP은 Excel 2021·Microsoft 365 이상에서 지원됩니다. 구버전 사용자는 INDEX/MATCH 또는 보조열(Helper Column)을 사용해 유사한 동작을 구현해야 합니다.
요약 및 다음 단계:
- 사용 환경(Excel 버전) 확인
- 조회 키의 형식 일관성 확보
- 테스트 데이터로 성능·정확성 점검
