엑셀 INDEX·MATCH 완전정복 — VLOOKUP의 한계를 넘는 실무 수식 20가지

VLOOKUP의 한계, INDEX·MATCH로 넘어야 하는 이유

VLOOKUP을 쓰다 보면 반드시 막히는 순간이 옵니다. 왼쪽 열을 찾아야 할 때, 열 번호가 바뀌면 수식이 깨질 때, 대용량 데이터에서 속도가 느려질 때. 이 세 가지 문제를 한 번에 해결하는 것이 INDEX·MATCH 조합입니다. 복잡해 보이지만 구조를 이해하면 VLOOKUP보다 오히려 쉽게 느껴집니다.

INDEX·MATCH는 방향 제한 없이 어느 열이든 참조값을 찾아냅니다

INDEX 함수 — 행·열 번호로 값 꺼내기

INDEX 함수는 지정한 범위에서 행 번호, 열 번호에 해당하는 값을 반환합니다.

=INDEX(범위, 행_번호, [열_번호])

수식 예시	의미
=INDEX(A1:C10, 3, 2)	A1:C10 범위의 3행 2열 값 반환
=INDEX(B:B, 5)	B열의 5번째 행 값 반환
=INDEX(A1:E1, 1, 4)	가로 범위의 4번째 열 값 반환

MATCH 함수 — 찾는 값이 몇 번째 행인지 알아내기

MATCH 함수는 범위 안에서 특정 값의 위치(순번)를 반환합니다. INDEX와 결합하면 강력해집니다.

=MATCH(찾는값, 찾는범위, [일치유형])

일치유형	의미	사용 시점
0	정확히 일치	일반 실무 조회 (대부분 이걸 씁니다)
1	이하 최근사치	오름차순 정렬된 범위 (등급표 등)
-1	이상 최근사치	내림차순 정렬된 범위

INDEX·MATCH 조합 — 핵심 구조

INDEX의 행 번호 자리에 MATCH를 넣는 것이 핵심입니다. "MATCH가 위치를 알아내고, INDEX가 값을 꺼낸다"고 기억하세요.

=INDEX(결과범위, MATCH(찾는값, 찾는범위, 0))

실전 예시: A열에 직원 이름, C열에 부서가 있을 때, F1에 입력한 이름으로 부서 찾기

=INDEX(C:C, MATCH(F1, A:A, 0))

INDEX·MATCH는 열 위치에 관계없이 어떤 방향으로도 조회가 가능합니다

실무 활용 패턴 6가지

패턴	수식	설명
① 역방향 조회	=INDEX(A:A, MATCH(F1,C:C,0))	오른쪽→왼쪽 조회 (VLOOKUP 불가)
② 다중 조건 (Ctrl+Shift+Enter)	=INDEX(C:C, MATCH(F1&G1, A:A&B:B, 0))	두 조건을 동시에 만족하는 행 찾기
③ 열 헤더로 동적 참조	=INDEX(A1:E100, MATCH(I1,A:A,0), MATCH(J1,A1:E1,0))	행·열 모두 동적으로 찾기
④ IFERROR 오류 처리	=IFERROR(INDEX(C:C,MATCH(F1,A:A,0)), "없음")	#N/A 대신 "없음" 표시
⑤ 최대값 위치 찾기	=INDEX(A:A, MATCH(MAX(B:B), B:B, 0))	B열 최대값에 해당하는 A열 이름 반환
⑥ 와일드카드 부분 일치	=INDEX(B:B, MATCH("*"&F1&"*", A:A, 0))	F1 문자를 포함하는 행 찾기

다중 조건 INDEX·MATCH 상세 예시

부서(A열)와 직급(B열)이 모두 일치하는 직원의 연봉(C열) 찾기:

배열 수식 (Ctrl+Shift+Enter로 입력):
=INDEX(C:C, MATCH(F1&G1, A:A&B:B, 0))

일반 수식 (365/2019 이상):
=INDEX(C:C, MATCH(1, (A:A=F1)*(B:B=G1), 0))

INDEX·MATCH vs XLOOKUP 선택 가이드

상황	추천 함수
Excel 2019 이하 사용 환경	INDEX·MATCH
다중 조건, 배열 조회, 동적 열 참조	INDEX·MATCH
단순 단일 조건 조회 (Microsoft 365)	XLOOKUP
공유 파일 (버전 혼재 환경)	INDEX·MATCH

자주 나오는 오류와 해결법

오류	원인	해결
#N/A	찾는값이 없음 / 공백·특수문자 차이	TRIM·CLEAN으로 전처리 후 재시도
#REF!	INDEX 범위보다 큰 행·열 번호	범위와 MATCH 검색 범위 크기 확인
#VALUE!	다중 조건 배열 수식에서 일반 Enter 입력	Ctrl+Shift+Enter로 재입력
엉뚱한 값 반환	MATCH 3번째 인수 생략 (기본값 1)	반드시 0을 명시적으로 입력

INDEX·MATCH를 익히면 어떤 복잡한 데이터 구조도 자유롭게 조회할 수 있습니다

마무리 — 오늘 바로 써먹는 핵심 요약

INDEX·MATCH의 핵심은 딱 하나입니다. MATCH가 위치를 찾고, INDEX가 값을 꺼낸다. 이 구조를 손에 익히면 역방향 조회, 다중 조건, 동적 열 참조까지 모두 해결됩니다. VLOOKUP은 이제 단순 조회에만, 복잡한 조건은 INDEX·MATCH로 대체해 보세요.

다음 포스팅에서는 INDEX·MATCH를 배열 수식 없이 다중 조건으로 처리하는 SUMPRODUCT 조합 패턴을 다루겠습니다.