INDEX, MATCH, SEQUENCE 결합 – 동적 범위 자동 참조 자동화하기
엑셀 자동화의 핵심은 "데이터가 변해도 수식이 스스로 적응하는 것"입니다. 지난 글에서 UNIQUE/FILTER와 SORTBY를 이용해 자동 정렬 고유값 파이프라인을 만들었다면, 이번에는 INDEX, MATCH, SEQUENCE를 결합해 동적 범위를 자동 참조하는 방법을 소개하겠습니다. 보고서 자동화, 대시보드 설계에서 자주 쓰이는 실무 팁입니다.
1) INDEX 함수의 기본 – 동적 참조의 핵심
INDEX는 "행 번호, 열 번호"를 지정해 해당 셀을 반환하는 함수입니다. 예를 들어 A2:A100 범위에서 5번째 값을 가져오려면:
=INDEX(A2:A100, 5)INDEX는 셀 참조를 동적으로 제어할 수 있는 도구로, MATCH나 SEQUENCE와 결합할 때 진가를 발휘합니다.
2) MATCH 함수로 위치 찾기
MATCH는 특정 값이 범위 내 몇 번째 위치에 있는지 반환합니다.
=MATCH("서울", A2:A100, 0)이렇게 하면 A2:A100 범위에서 "서울"이 몇 번째에 있는지 찾을 수 있습니다. INDEX와 결합하면 조건 기반 데이터 참조가 가능합니다.
3) SEQUENCE로 자동 범위 생성
SEQUENCE는 일정한 숫자 배열을 자동 생성합니다.
=SEQUENCE(5)→ {1;2;3;4;5} 반환. 이를 INDEX의 "행 번호" 인수로 넘기면, 한 번에 여러 셀을 동적으로 불러올 수 있습니다.
4) INDEX+MATCH+SEQUENCE 결합 – 동적 범위 파이프라인
예를 들어, 특정 제품명("노트북")이 시작되는 위치부터 최근 7일간의 판매량을 불러오려면:
=INDEX(B2:B100, MATCH("노트북", A2:A100, 0) + SEQUENCE(7,1,0,1))이 수식은 "노트북"이 등장한 행을 기준으로 7개의 연속 데이터를 자동으로 반환합니다. 즉, 데이터가 늘어나거나 위치가 바뀌어도 자동으로 업데이트되는 동적 참조 파이프라인이 완성됩니다.
5) 실무 활용 사례: 자동 차트 업데이트
실제 보고서에서는 "최근 N일 매출 차트"나 "특정 제품군의 동적 그래프"가 자주 필요합니다. INDEX+MATCH+SEQUENCE를 사용하면, 데이터가 추가될 때마다 차트 범위가 자동으로 확장됩니다. 파워쿼리/피벗테이블과 달리, 별도 새로고침 없이 실시간 반영되는 점이 강점입니다.
6) OFFSET 함수와의 비교
OFFSET 함수도 비슷한 기능을 제공하지만, 휘발성 함수라서 대규모 데이터에서는 속도 저하가 발생할 수 있습니다. 반면 INDEX+MATCH+SEQUENCE는 비휘발성이어서 성능 면에서 더 안정적입니다.
7) 자주 묻는 질문(FAQ)
Q1. SEQUENCE 대신 ROW 함수를 써도 되나요?
가능합니다. 다만 SEQUENCE는 원하는 개수, 시작값, 간격을 한 번에 지정할 수 있어 훨씬 유연합니다.
Q2. INDEX+MATCH 대신 XLOOKUP을 쓰면 안 되나요?
XLOOKUP은 단일 값 반환에는 간단하지만, "연속된 범위"를 불러오는 데는 INDEX+MATCH+SEQUENCE 조합이 더 적합합니다.
Q3. 차트와 자동 연결은 어떻게 하나요?
이 수식을 이름 정의(Name Manager)로 설정한 뒤 차트 범위를 해당 이름으로 지정하면 자동 업데이트가 가능합니다.