도구/Excel

엑셀 INDEX MATCH 함수 완전 가이드

taxis 2025. 5. 16. 17:04

엑셀 INDEX MATCH 함수 완전 가이드

VLOOKUP을 넘어서는 실무 최강 조합!

안녕하세요, 엑셀 10년차 컨설턴트이자 블로거입니다 😊
오늘은 실무에서 꼭 알아야 할 함수 콤비, INDEX + MATCH 함수에 대해 알려드릴게요.

단순 조회는 VLOOKUP으로도 가능하지만, 더 유연하고 강력한 방법을 원하신다면 INDEX와 MATCH 조합이 정답입니다!

🔍 1. INDEX MATCH 함수 기초 개념 및 VLOOKUP과의 차이

▶ INDEX 함수 기본 구조

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

지정된 범위 내에서 행/열 번호에 해당하는 값을 반환

▶ MATCH 함수 기본 구조

=MATCH(찾을값, 범위, 0)

특정 값이 범위 내 몇 번째에 있는지 반환

▶ INDEX + MATCH 결합 구조

=INDEX(결과범위, MATCH(찾을값, 기준범위, 0))

기준값의 위치를 MATCH로 찾고, 그 위치의 값을 INDEX로 가져오는 방식

▶ VLOOKUP vs INDEX+MATCH 차이점

항목VLOOKUPINDEX + MATCH

조회 방향	왼쪽 → 오른쪽	왼쪽/오른쪽 모두 가능
열 추가 시 오류 가능성	있음	없음
성능	대량 데이터 느림	빠름
유연성	낮음	높음
가독성	간단함	약간 복잡함

📅 2. 주요 사용 목적 3가지 및 사용 서식 예시

왼쪽 데이터 참조 가능
→ 기준값보다 왼쪽에 있는 값을 조회할 수 있음 📌 예: 거래명세서에서 품명으로 코드 불러오기
범위 확장에 안전한 조회
→ 열/행 삽입에 의한 오류 발생 없음 📌 예: 급여대장, 성적표처럼 열 변경이 자주 일어나는 서식
복합조건과 동적범위 적용 가능
→ 다중 조건 검색, 유연한 범위 설정 가능 📌 예: 다중시트 통합조회, 상품별 수익성 분석표 등

🔎 3. INDEX MATCH 실무 예제 30가지 (목적 포함)

① 기본 조회

=INDEX(B2:B10, MATCH("A001", A2:A10, 0))
→ 코드 기준 품명 불러오기
📌 거래명세서 자동화
=INDEX(E2:E10, MATCH("홍길동", C2:C10, 0))
→ 이름으로 부서 조회
📌 인사기록카드 연결
=INDEX(F2:F20, MATCH(100, G2:G20, 0))
→ 점수 기준 등급 불러오기
📌 성적관리표
=INDEX(수당!B2:B100, MATCH(A2, 수당!A2:A100, 0))
→ 사번 기준 수당 조회
📌 급여대장 연동
=INDEX(D2:D20, MATCH(TRUE, B2:B20>10, 0))
→ 조건 만족 첫 항목 불러오기

② 반대방향(왼쪽) 조회

=INDEX(A2:A10, MATCH("사과", B2:B10, 0))
→ 품명 기준으로 코드 찾기
📌 거꾸로 조회
=INDEX(사원표!A2:A100, MATCH("개발팀", 사원표!C2:C100, 0))
→ 부서로 사원명 찾기
📌 부서별 대표자 조회
=INDEX(제품표!A2:A100, MATCH("노트북", 제품표!C2:C100, 0))
→ 설명 기준으로 제품코드 불러오기

③ 다중 조건 응용

=INDEX(D2:D10, MATCH(1, (B2:B10="A")*(C2:C10="Y"), 0))
→ 두 조건 모두 만족 시 데이터 반환
=INDEX(금액표!E2:E100, MATCH(1, (B2:B100="부산")*(C2:C100="3분기"), 0))
📌 지역+분기 기준 매출액 조회
=INDEX(점수!D2:D10, MATCH(1, (A2:A10=K2)*(B2:B10=K3), 0))
📌 학생 + 과목 기준 점수 불러오기
=INDEX(B2:E2, MATCH("4월", B1:E1, 0))
→ 가로방향 월별 데이터 조회

④ 동적 범위 및 구조

=INDEX(B2:B10, MATCH(MAX(C2:C10), C2:C10, 0))
→ 최대값 기준 이름 찾기
=INDEX(OFFSET(B1, 0, MATCH("2023", B1:Z1, 0), 10), MATCH("A001", A2:A11, 0))
📌 동적 연도별 표 구조 대응
=INDEX(INDIRECT("표_"&A2&"!B2:B100"), MATCH(B2, INDIRECT("표_"&A2&"!A2:A100"), 0))
📌 시트명이 동적인 경우

⑤ 숫자, 날짜, 텍스트 조합

=INDEX(B2:B20, MATCH(TODAY(), A2:A20, 0))
→ 날짜 기준 오늘 데이터 조회
=INDEX(D2:D10, MATCH(TRUE, ISNUMBER(SEARCH("홍", C2:C10)), 0))
→ 부분 텍스트 검색 후 값 추출
=INDEX(F2:F20, MATCH(TRUE, (E2:E20>=100000), 0))
→ 금액 기준 상위 데이터 찾기

⑥ 보완 및 예외 처리

=IFERROR(INDEX(B2:B10, MATCH(A2, A2:A10, 0)), "해당없음")
=IF(COUNTIF(A2:A10, A2)=0, "신규", INDEX(B2:B10, MATCH(A2, A2:A10, 0)))
=INDEX(B2:B10, MATCH(TRUE, ISBLANK(B2:B10)=FALSE, 0))
=INDEX(범위, MATCH(조건, 기준, 0)) + INDEX(범위2, MATCH(조건2, 기준2, 0))

⑦ 고급 활용 (복합계산, 배열, 중첩)

=SUM(INDEX(B2:D10, MATCH("A001", A2:A10, 0), 0))
→ 특정 항목의 월별 합계
=INDEX(B2:D10, MATCH("홍길동", A2:A10, 0), MATCH("2월", B1:D1, 0))
📌 행/열 동시 참조
=INDEX(D:D, MATCH(MAX(E:E), E:E, 0))
→ 최대값에 해당하는 행의 값
=INDEX(매출!B2:B100, MATCH(TRUE, 매출!C2:C100>100000, 0))
=INDEX(B2:B10, SMALL(IF(C2:C10>80, ROW(C2:C10)-ROW(C2)+1), 1))
=INDEX(결과범위, MATCH(TRUE, ISERROR(기준범위)=FALSE, 0))
=INDEX(B2:B10, MATCH(TRUE, (C2:C10>=60)*(D2:D10="Y"), 0))
=IF(MATCH(A2, A:A, 0)=ROW(A2), "최초", "중복")

⚠️ 4. INDEX MATCH 사용 시 주의사항 10가지

MATCH는 정확히 일치하도록 0 사용 (기본값 1 아님)
INDEX는 반환 범위, MATCH는 기준 범위 분리할 것
MATCH 결과가 #N/A일 경우 IFERROR로 감싸기
범위 크기 불일치 오류 주의
MATCH는 중복값이 있으면 첫 번째만 찾음
배열 수식 사용 시 Ctrl+Shift+Enter (구버전)
검색 기준 텍스트 정합성 주의 (공백 포함 여부 등)
열삽입 등 구조 변경 시 참조 셀 고정 권장 ($A$2:$A$100)
조건이 복잡할 경우 FILTER, XLOOKUP 고려
MATCH는 기준값 위치를 숫자로 반환한다는 점 이해 필요

📍 맺음말

INDEX + MATCH 함수는 단순 조회를 넘어, 유연한 방향 지정, 복합조건 검색, 대규모 데이터 대응까지 가능한 실무의 핵심 도구입니다.

이번에 소개한 30가지 예제를 바탕으로 VLOOKUP의 한계를 뛰어넘는 고급 자동화를 직접 적용해보세요.

다음 글에서는 XLOOKUP 함수와의 비교, 그리고 배열수식과의 결합도 다뤄볼 예정입니다 :) 업무 효율이 쑥쑥 오를 거예요!

저작자표시 비영리 변경금지 (새창열림)