[엑셀 함수] XLOOKUP + INDEX + MATCH 실무 완전 정복

“찾는 값이 하나가 아닐 때” 쓰는 진짜 조회 조합

XLOOKUP을 쓰다 보면 어느 순간 이런 상황을 만납니다.

• 값은 정확히 찾았는데
• 가져와야 할 위치가 고정이 아님
• 열이 늘어나거나 순서가 바뀜
• 같은 기준으로 여러 값을 가져와야 함

이때 많은 사람들이 이렇게 생각합니다.

“XLOOKUP으로는 한계가 있네…”

 

하지만 문제는 XLOOKUP이 아니라 ‘위치 처리’를 같이 안 써서입니다.

 

이번 6화에서는 XLOOKUP + INDEX + MATCH조합으로 실무에서 자주 나오는 유동적인 조회 문제를 해결합니다.

 

 

왜 INDEX가 필요한가?

XLOOKUP은 정말 좋은 함수입니다. 하지만 구조는 명확합니다.

• 기준값 1개
• 반환열 1개

즉,

✔ “무엇을 찾을지”에는 강하지만
❌ “어디에서 가져올지”는 고정돼 있습니다.

실무에서는 이런 상황이 더 많습니다.

• 월별로 다른 열에서 값 조회
• 선택한 항목에 따라 가져올 열이 달라짐
• 보고서 구조가 자주 바뀜

👉 이때 필요한 게 INDEX입니다.

 

 

 

실무 시나리오 예시

다음 상황을 가정해 보겠습니다.

 

✔ 매출 데이터 Sheet

 

✔ 보고서 Sheet

• 제품 선택
• 월 선택
• 해당 월의 매출 자동 표시

 

질문은 이것입니다.

“제품 C001의 1월 매출은 얼마인가?”

 

 

되짚어 보기 : XLOOKUP만으로 구현

✔ 제품 "C001"의 "1월" 매출액 불러오기

아래와 같이 매출 데이터 Sheet에서 B열의 data를 XLOOKUP만으로 불러올 수 있습니다. 

=XLOOKUP(A7,'Sales data'!A:A,'Sales data'!B:B)

다른 행들도 동일한 방식으로 매출 data를 불러올 수 있습니다.

 

✔ 제품 "C001"의 "월별" 매출 data 불러오기

=XLOOKUP(A2,'Sales data'!A:A,'Sales data'!B:E)

XLOOKUP을 통해 제품별 매출 data를 불러올 수 있습니다.

 

 

 

Step 0. XLOOKUP + INDEX + MATCH 기본 구조

여기서 생각을 바꿉니다.

• MATCH → 열 번호 결정
• INDEX → 해당 열 전체 선택
• XLOOKUP → 행의 값 반환

즉, 역할을 나눕니다.

보고서 Sheet에서 해당 월의 매출 자동 표시하고 싶을 때 INDEX, MATCH 함수를 이용해서 동적인 함수를 구현할 수 있습니다.

 

 

Step 1. INDEX + MATCH로 열 위치 지정

✔ 월별 열 위치 결정

MATCH 함수로 열 번호 부여

=MATCH(B6,'Sales data'!B1:E1,0)

월별 순서대로 값이 지정된 것을 볼 수 있습니다.

 

✔ INDEX로 해당 열 전체 선택

MATCH 함수로 월별 번호를 정하고 INDEX로 해당 번호에 해당하는 열의 전체 data를 가져오게 됩니다.

 

 

Step 2. XLOOKUP로 제품의 월별 매출액 가져오기

월별 제품의 매출액

=XLOOKUP(A12,'Sales data'!A:A, INDEX('Sales data'!B:E,, MATCH(B11,'Sales data'!B1:E1,0)))

이렇게 제품별 매칭되는 월별 매출액을 확인할 수 있습니다.

 

 

📌 구조를 풀면 이렇습니다.

• MATCH → 선택한 월이 몇 번째 열인지 설정
• INDEX → 그 위치의 열 전체 값을 반환
• XLOOKUP → 제품별 행 값 불어오기

 

 

이 조합이 실무에서 자주 쓰이는 이유

이 구조의 장점은 명확합니다.

✔ 열 순서가 바뀌어도 안전
✔ 월이 늘어나도 수식 수정 없음
✔ 보고서 입력값만으로 제어 가능
✔ 대시보드에 바로 사용 가능

 

즉,

❌ 열 번호를 외우는 엑셀
⭕ 구조를 이해하는 엑셀

이 차이입니다.

 

 

 

실무에서 바로 쓰이는 예시들

이 조합은 이런 곳에서 자주 쓰입니다.

• 월별 / 분기별 실적 조회
• 선택한 KPI 값 가져오기
• 제품·지점·담당자별 지표 조회
• 대시보드 핵심 수치 영역

특히,

✔ 보고서 구조가 자주 바뀌는 경우

✔ 사용자가 직접 조건을 선택하는 경우

INDEX 없이는 관리가 거의 불가능합니다.

 

 

 

오늘의 핵심 정리

✔ XLOOKUP은 “무엇을 찾는지”
✔ INDEX는 “어디서 가져오는지”
✔ MATCH로 "위치를 자동 계산"
이 조합으로 유동적인 보고서 완성할 수 있습니다.