논리 함수와 엑셀 365의 동적 배열 함수를 결합, 가장 복잡한 데이터 조회 및 매칭 시나리오를 해결하고 엑셀 데이터 관리의 궁극적인 자동화를 실현할 차례입니다.
고급 응용 기술들은 데이터를 단순히 찾는 것을 넘어, 조건에 맞춰 스스로 판단하고 변형하여 원하는 결과물을 토해내는 엑셀의 진정한 마법을 보여줍니다.
왜 고급 조회 & 참조 + 논리/배열 함수 시너지를 알아야 할까요?
실무에서는 "서울 지점의 Gold 등급 고객 중, 최근 3개월 이내에 구매 이력이 있는 고객의 리스트를 뽑아달라"거나, "특정 기간 동안 가장 많이 팔린 제품 상위 5개를 지역별로 보여달라"와 같은 다중 조건을 포함하는 복잡한 데이터 조회 및 보고서 요청이 빈번합니다. 이처럼:
- 다중 조건 검색: 여러 조건을 동시에 만족하는 데이터만 조회해야 할 때
- 동적 데이터 추출: 조건이나 사용자 입력에 따라 실시간으로 변화하는 데이터 목록을 만들어야 할 때
- 보고서 자동화: 수동 필터링이나 복사/붙여넣기 없이도 보고서가 자동으로 업데이트되도록 할 때
이러한 상황에서는 단일 조회 함수만으로는 한계가 있습니다. 논리 함수와 동적 배열 함수를 조회/참조 함수와 결합함으로써, 엑셀이 마치 지능적인 데이터베이스 쿼리 도구처럼 작동하도록 만들어, 업무 효율성을 혁명적으로 개선할 수 있습니다.
주요 고급 응용 기술들
다음 핵심 응용 기술들을 실무 시나리오와 함께 자세히 살펴보겠습니다.
- 논리 함수 (IF, AND, OR) + INDEX/MATCH/XLOOKUP: 다중 조건에 따른 고급 조회
- FILTER + (조회/참조 함수) (엑셀 365): 특정 조건을 만족하는 데이터의 동적 추출 및 후속 조회
- SORTBY + XLOOKUP (엑셀 365): 여러 기준으로 정렬된 데이터에서 조회
- UNIQUE + SORT + XLOOKUP (엑셀 365): 고유하고 정렬된 목록 기반 조회
- CHOOSEROWS / CHOOSECOLS + XLOOKUP (엑셀 365): 특정 행/열만 선택하여 조회
실무 예제로 배우는 엑셀 함수 마스터하기
각 응용 기술을 실제 비즈니스 시나리오에 적용하여 가장 복잡한 데이터 조회 및 보고서 자동화를 구현하는 방법을 보여드리겠습니다.
1. 논리 함수 + INDEX/MATCH/XLOOKUP: 다중 조건 고급 조회
VLOOKUP은 다중 조건을 직접 처리할 수 없지만, INDEX/MATCH 또는 XLOOKUP은 논리 함수와 결합하여 여러 조건을 동시에 만족하는 데이터를 조회할 수 있습니다.
- 기본 아이디어 (INDEX/MATCH 조합): =INDEX(반환_범위, MATCH(1, (조건1)*(조건2)*(조건3), 0)) (배열 수식)
- 논리 조건들(예: (A:A="조건값1")*(B:B="조건값2"))을 *로 연결하면 AND 조건이 됩니다. 이는 TRUE를 1, FALSE를 0으로 변환하여 모두 1일 때만 1을 반환하는 원리입니다.
- 기본 아이디어 (XLOOKUP 조합): 보조 열을 만들거나, TRUE를 이용한 배열 수식 활용
실무 예제: 우리 회사의 지난달 고객 주문 데이터가 A2:D10에 있습니다.
| 지역 | 고객 등급 | 제품군 | 판매액(천원) |
| 서울 | Gold | 전자제품 | 1,500 |
| 부산 | Silver | 의류 | 800 |
| 서울 | Bronze | 의류 | 700 |
| 대구 | Gold | 식품 | 1,200 |
| 서울 | Gold | 의류 | 1,000 |
| 부산 | Bronze | 전자제품 | 900 |
| 서울 | Silver | 전자제품 | 1,100 |
| 대구 | Silver | 식품 | 600 |
| 부산 | Gold | 의류 | 1,300 |
예제 목표: '서울' 지역의 'Gold' 등급 고객이 구매한 '전자제품'의 판매액을 조회하세요.
해결 방법 1 (INDEX + MATCH 배열 수식): E2 셀에 다음 수식을 입력하고 Ctrl + Shift + Enter로 완료합니다. =INDEX(D2:D10, MATCH(1, (A2:A10="서울")*(B2:B10="Gold")*(C2:C10="전자제품"), 0))
결과: 1,500
해결 방법 2 (XLOOKUP + 보조열 - 엑셀 365):
- 보조열 생성: A열 앞에 새 열을 삽입하고 (가정상 A열이 이제 B열이 됨), A2 셀에 =B2&C2&D2를 입력하여 각 조건들을 결합한 키를 만듭니다. (원래 데이터 테이블도 똑같이 첫 열에 키 생성)
- F2 셀에 다음 수식을 입력합니다. =XLOOKUP("서울Gold전자제품", A2:A10, E2:E10, "조건 불일치")
결과: 1,500
최상급 실무 활용: 이 기술은 복잡한 재고 관리, 다차원적인 고객 분류, 특정 기준에 따른 리소스 할당 등 VLOOKUP으로는 불가능한 정교한 데이터 검색에 필수적입니다. 특히 INDEX/MATCH는 오래된 엑셀 버전에서도 사용 가능하며, XLOOKUP은 최신 버전에서 더욱 간결한 구문을 제공합니다. 이는 보고서의 자동화 수준을 극대화하여 수동 필터링의 필요성을 없애줍니다.
2. FILTER + (조회/참조 함수) (엑셀 365): 조건부 데이터 동적 추출 및 후속 조회
FILTER 함수는 특정 조건을 만족하는 데이터 배열을 통째로 반환합니다. 여기에 날짜/시간 함수 등과 결합하여 동적으로 필터링된 데이터를 만들고, 이 필터링된 결과에서 다시 XLOOKUP 등으로 값을 조회할 수 있습니다.
- 기본 아이디어: =FILTER(데이터_배열, 조건_배열)
실무 예제: 위 고객 주문 데이터(A1:D10)를 사용합니다.
예제 목표: '서울' 지역의 'Gold' 등급 고객의 모든 주문 내역을 동적으로 추출하고, 이 추출된 목록에서 가장 높은 판매액을 찾아내세요.
해결 방법:
- 동적 추출 (F2 셀): =FILTER(A2:D10, (A2:A10="서울")*(B2:B10="Gold"), "데이터 없음")
- 이 수식은 F2 셀부터 아래로, '서울' 지역 'Gold' 등급 고객의 모든 주문 정보를 자동으로 채워줍니다.
- 최고 판매액 조회 (MAX 함수 + 추출된 범위): K2 셀에 =MAX(INDEX(F2#,0,4)) (F2#은 FILTER 함수로 반환된 전체 배열을 참조)
- INDEX(F2#,0,4)는 F2 셀부터 시작하는 FILTER 결과의 4번째 열(판매액 열) 전체를 배열로 가져옵니다.
- 또는 MAX(FILTER(D2:D10, (A2:A10="서울")*(B2:B10="Gold")))처럼 FILTER를 한 번 더 사용하여 MAX를 직접 적용할 수도 있습니다.
결과: '서울' 지역 'Gold' 등급 고객의 모든 주문 내역이 동적으로 표시되며, 가장 높은 판매액 (1,500)이 별도로 표시됩니다.
최상급 실무 활용: FILTER 함수는 동적인 보고서 생성, 맞춤형 대시보드, 특정 조건에 맞는 데이터 서브셋(subset) 추출에 혁신적인 변화를 가져옵니다. 예를 들어, 사용자 선택에 따라 특정 기간의 특정 제품군에 대한 상세 내역을 자동으로 보여주는 동적 대시보드를 구축할 수 있습니다.
3. SORTBY + XLOOKUP (엑셀 365): 여러 기준으로 정렬된 데이터에서 조회
SORTBY 함수는 하나 이상의 기준 열을 사용하여 데이터 배열을 정렬합니다. 여기에 XLOOKUP을 결합하면, 정렬된 상태에서 특정 값을 조회하는 복합적인 시나리오를 구현할 수 있습니다.
- 기본 아이디어: =XLOOKUP(찾을_값, SORTBY(찾을_범위, 정렬_기준1, [정렬_순서1], ...), SORTBY(반환할_범위, 정렬_기준1, [정렬_순서1], ...))
실무 예제: 직원별 판매 실적 데이터에서, 판매액이 가장 높은 직원의 정보를 조회해야 합니다. (동점 시 이름 기준)
| 직원 ID | 이름 | 판매액 |
| E001 | 김철수 | 150 |
| E002 | 이영희 | 120 |
| E003 | 박지성 | 150 |
| E004 | 최민수 | 100 |
예제 목표: 판매액이 가장 높은 직원(가장 높은 값)의 '직원 ID'를 조회하세요. (판매액 내림차순, 이름 오름차순으로 정렬 후)
해결 방법: F2 셀에 다음 수식을 입력합니다. =XLOOKUP(MAX(C2:C5), SORTBY(C2:C5, C2:C5, -1, B2:B5, 1), SORTBY(A2:A5, C2:C5, -1, B2:B5, 1))
- MAX(C2:C5): 찾을 값 (가장 높은 판매액)
- SORTBY(C2:C5, C2:C5, -1, B2:B5, 1): '판매액'을 내림차순(-1), '이름'을 오름차순(1)으로 정렬된 '판매액' 범위.
- SORTBY(A2:A5, C2:C5, -1, B2:B5, 1): '판매액'과 '이름' 기준으로 정렬된 '직원 ID' 범위.
결과: E003 (판매액 150 중 박지성이 김철수보다 이름이 뒤에 오므로)
최상급 실무 활용: 최고/최저 성과자 식별, 특정 조건에 따른 우선순위 조회, 정렬된 데이터에서 동적으로 값 검색 등에 매우 강력합니다. 이는 성과 평가, 자원 배분, 문제 해결 등에서 가장 중요한 데이터를 빠르게 찾아내는 데 기여합니다.
4. UNIQUE + SORT + XLOOKUP (엑셀 365): 고유하고 정렬된 목록 기반 조회
고유한 항목 목록을 만들고, 이를 정렬한 후, 이 목록을 기준으로 조회하는 것은 많은 보고서에서 필요한 작업입니다.
동적 배열 함수를 통해 이를 단일 수식으로 처리할 수 있습니다.
- 기본 아이디어: =XLOOKUP(찾을_값, SORT(UNIQUE(찾을_범위)), 반환할_범위)
실무 예제: 고객 구매 이력 데이터에 중복된 제품명이 많습니다. 제품명으로 단가를 조회하려 하는데, 중복이 제거된 정렬된 제품 목록을 기준으로 조회하고 싶습니다.
| 구매 ID | 제품명 | 판매액 |
| P001 | 노트북 | 100 |
| P002 | 태블릿 | 120 |
| P003 | 노트북 | 150 |
| P004 | 스마트폰 | 80 |
| P005 | 태블릿 | 130 |
(참조할 단가표: F2:G4)
| 제품명 | 단가 |
| 노트북 | 10,000 |
| 스마트폰 | 8,000 |
| 태블릿 | 15,000 |
예제 목표: '구매 ID' P001의 제품명(노트북)을 기반으로, 단가표에서 해당 단가를 조회하세요. 단, 단가표는 UNIQUE와 SORT 함수로 생성된 가상 목록이라고 가정합니다.
해결 방법: D2 셀에 다음 수식을 입력합니다. =XLOOKUP(C2, SORT(UNIQUE(G2:G4)), H2:H4, "정보 없음")
- SORT(UNIQUE(G2:G4)): 단가표의 제품명(G2:G4)에서 고유한 값만 추출하고 오름차순 정렬하여 {"노트북"; "스마트폰"; "태블릿"} 배열을 만듭니다.
결과: 10,000
최상급 실무 활용: 복잡한 데이터에서 고유한 목록을 추출하고 이를 기반으로 조회하는 것은 보고서 작성의 일반적인 패턴입니다. 이 조합은 품목 마스터 목록 관리, 고객 리스트 중복 제거, 특정 범주에 대한 동적 필터링 및 조회 등 데이터 정제와 분석 과정에서 획기적인 효율성을 제공합니다.
5. CHOOSEROWS / CHOOSECOLS + XLOOKUP (엑셀 365): 특정 행/열만 선택하여 조회
CHOOSEROWS와 CHOOSECOLS 함수는 배열에서 특정 행이나 열만 선택하여 새로운 배열을 만듭니다. 이를 XLOOKUP과 결합하면, 원본 데이터에서 필요한 부분만 선택하여 효율적으로 조회할 수 있습니다.
- 기본 아이디어: =XLOOKUP(찾을_값, CHOOSEROWS/CHOOSECOLS(원본_배열, 행/열_인덱스), CHOOSEROWS/CHOOSECOLS(원본_배열, 반환_행/열_인덱스))
실무 예제: 직원 정보 테이블에서 '직원 ID'와 '부서' 열만 선택하여 '직원 ID'로 '부서'를 조회해야 합니다.
| 이름 | 직원ID | 부서 | 직급 |
| 김철수 | E001 | 영업부 | 사원 |
| 이영희 | E002 | 마케팅 | 대리 |
| 박지성 | E003 | 개발부 | 과장 |
예제 목표: '직원 ID' E002의 '부서'를 조회하세요. 단, 원본 테이블의 '이름'과 '직급' 열은 조회에 사용하지 않습니다.
해결 방법: F2 셀에 다음 수식을 입력합니다. =XLOOKUP("E002", CHOOSECOLS(A2:D4, 2), CHOOSECOLS(A2:D4, 3))
- CHOOSECOLS(A2:D4, 2): A2:D4 범위에서 2번째 열(직원 ID)만 선택하여 {E001; E002; E003} 배열 생성.
- CHOOSECOLS(A2:D4, 3): A2:D4 범위에서 3번째 열(부서)만 선택하여 {영업부; 마케팅; 개발부} 배열 생성.
결과: 마케팅
최상급 실무 활용: CHOOSEROWS/CHOOSECOLS는 원본 데이터의 특정 부분만 사용하여 조회/분석해야 할 때 매우 유용합니다. 불필요한 열/행을 제외하여 수식을 간결하게 만들고, 복잡한 데이터 모델에서 특정 하위 데이터셋을 기반으로 동적으로 조회해야 하는 고급 시나리오에 활용됩니다. 이는 엑셀 365의 최신 기능으로, 데이터 처리의 유연성을 한 차원 높여줍니다.
논리 함수와 엑셀 365의 동적 배열 함수를 결합하여 가장 복잡한 데이터 조회 및 매칭 시나리오를 해결하는 최상급 응용 기술들을 살펴보았습니다.
FILTER, SORTBY, UNIQUE, CHOOSEROWS/CHOOSECOLS와 XLOOKUP의 시너지를 통해 엑셀을 단순한 스프레드시트 프로그램을 넘어, 지능적인 데이터 분석 및 자동화 시스템으로 활용할 수 있는 역량을 갖추게 됩니다.