INDEX MATCH: 더 뛰어난 조회 방법

Excel에서 모든 조회 작업에 VLOOKUP을 사용해 왔다면, 당신만 그런 것이 아닙니다 — VLOOKUP은 스프레드시트 세계에서 가장 널리 알려진 함수 중 하나입니다. 하지만 숙련된 Excel 사용자들은 거의 예외 없이 INDEX MATCH로 넘어갑니다. 이 두 함수의 조합은 더 유연하고, 더 안정적이며, VLOOKUP으로는 해결할 수 없는 문제까지 처리할 수 있습니다. 이 글에서는 실제 구문, 예제, 그리고 바로 따라 할 수 있는 실습 안내를 통해 그 이유를 명확하게 설명합니다.

INDEX와 MATCH란 무엇인가요?

두 함수를 조합하기 전에, 각 함수를 개별적으로 이해하는 것이 도움이 됩니다.

INDEX 함수

INDEX는 범위 또는 배열 내의 지정된 위치에 있는 셀의 값을 반환합니다.

=INDEX(array, row_num, [col_num])

• array — 조회할 셀 범위
• row_num — 해당 범위 내의 행 위치
• col_num — (선택 사항) 해당 범위 내의 열 위치

예를 들어, =INDEX(A1:A10, 3)은 A열의 1행부터 10행 중 세 번째 행에 있는 값을 반환합니다.

MATCH 함수

MATCH는 범위 내에서 값을 검색하고, 값 자체가 아닌 해당 값이 위치한 위치 번호를 반환합니다.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value — 검색할 값
• lookup_array — 검색할 단일 행 또는 열
• match_type — 정확히 일치하는 항목을 찾으려면 0을 사용합니다(가장 일반적). 보다 작은 값은 1, 보다 큰 값은 -1을 사용합니다.

예를 들어, A1:A5에 {Apple, Banana, Cherry, Date, Fig}가 입력되어 있다면, =MATCH("Cherry", A1:A5, 0)은 Cherry가 세 번째 항목이므로 3을 반환합니다.

INDEX와 MATCH 조합하기

INDEX 안에 MATCH를 중첩할 때 진정한 강점이 나타납니다. 행 번호를 직접 입력하는 대신, MATCH가 동적으로 계산하도록 할 수 있습니다.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

이 수식은 Excel에 다음과 같이 지시합니다: "조회 범위에서 내 조회 값의 위치를 찾은 다음, 반환 범위에서 해당하는 값을 반환하라." 두 범위는 크기가 같아야 하며 같은 방향으로 정렬되어 있어야 합니다.

실습 안내

다음과 같은 구조의 제품 재고 테이블을 예로 들어 보겠습니다.

제품 ID	제품명	카테고리	단가	재고
P-101	Wireless Mouse	Electronics	$29.99	142
P-102	USB-C Hub	Electronics	$49.99	87
P-103	Desk Lamp	Office	$34.99	55
P-104	Notebook A5	Stationery	$8.99	310
P-105	Ergonomic Chair	Furniture	$299.00	12

데이터는 A2:E6에 있으며, 헤더는 1행에 위치합니다. H2 셀에 입력된 ID를 가진 제품의 단가를 조회하려고 합니다.

INDEX MATCH를 사용하면 H3의 수식은 다음과 같습니다.

=INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0))

단계별 설명:

1. MATCH는 A열(제품 ID)에서 H2에 있는 값을 검색합니다.
2. 행 위치를 반환합니다 — 예를 들어 H2에 "P-103"이 입력되어 있으면 3을 반환합니다.
3. 그러면 INDEX는 D열(단가)의 3행에 있는 값인 $34.99를 반환합니다.

달러 기호를 사용한 절대 셀 참조의 활용에 주목하세요. 범위를 고정하면 수식을 다른 셀에 복사해도 올바르게 작동합니다.

INDEX MATCH가 VLOOKUP보다 뛰어난 이유

이미 VLOOKUP 완벽 가이드에서 VLOOKUP을 알고 있다면, 그 장점을 이해하실 것입니다. 하지만 VLOOKUP에는 잘 알려진 한계가 있으며, INDEX MATCH는 이를 깔끔하게 해결합니다.

1. 왼쪽 조회 — VLOOKUP은 불가능, INDEX MATCH는 가능

VLOOKUP은 테이블의 가장 왼쪽 열만 검색하고 오른쪽의 값을 반환합니다. 조회 열이 반환 열의 오른쪽에 있으면 VLOOKUP은 실패합니다. INDEX MATCH는 이러한 제약이 없습니다 — 반환 범위와 조회 범위가 완전히 독립적이므로, 검색 열의 왼쪽에 있는 열을 포함한 어떤 열에서도 값을 반환할 수 있습니다.

2. 열 번호 변경이 수식을 망가뜨리지 않습니다

VLOOKUP은 하드코딩된 열 인덱스 번호(예: 세 번째 열)를 사용합니다. 열을 삽입하거나 삭제하면 해당 번호가 틀려져 잘못된 데이터를 자동으로 반환합니다. INDEX MATCH는 실제 범위를 참조하기 때문에 열을 삽입해도 수식이 깨지지 않습니다.

3. 대용량 데이터셋에서 더 빠릅니다

VLOOKUP은 계산할 때마다 전체 테이블 배열을 스캔합니다. INDEX MATCH는 특정 조회 열과 특정 반환 열만 평가하므로, 수만 행의 통합 문서에서 체감할 수 있을 만큼 더 빠릅니다.

4. 양방향(2D) 조회

MATCH 함수를 두 개 중첩하여 — 하나는 행, 하나는 열을 위해 — 보조 수식 없이는 VLOOKUP이 재현할 수 없는 2차원 조회를 만들 수 있습니다.

=INDEX(B2:E6, MATCH(H2, A2:A6, 0), MATCH(H3, B1:E1, 0))

여기서 MATCH(H2, A2:A6, 0)은 올바른 행을 찾고, MATCH(H3, B1:E1, 0)은 올바른 열을 찾습니다. 입력 셀을 변경하면 수식이 즉시 적응합니다. 이는 여러 차원에 걸쳐 지표를 가져와야 하는 영업 대시보드에서 특히 유용합니다.

오류 우아하게 처리하기

일치하는 항목이 없으면 MATCH는 #N/A 오류를 반환합니다. 전체 INDEX MATCH를 IFERROR로 감싸서 사용자 친화적인 메시지를 표시하세요.

=IFERROR(INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0)), "Product not found")

이는 최종 사용자가 검색 값을 직접 입력하는 공유 통합 문서나 서식 파일에서 특히 중요합니다 — 깔끔한 오류 처리는 혼란과 불편함을 방지합니다. 입력 셀에 데이터 유효성 검사를 결합하여 유효한 목록으로 입력을 제한하면, 강력하고 사용자 실수를 방지하는 조회 도구가 완성됩니다.

INDEX MATCH를 사용한 다중 조건 조회

가장 많이 요청되는 조회 시나리오 중 하나는 두 개 이상의 조건으로 일치시키는 것입니다. 카테고리가 "Electronics"이면서 재고가 100 미만인 제품의 단가를 찾고 싶다고 가정해 보겠습니다. 배열 버전의 INDEX MATCH로 이를 구현할 수 있습니다.

=INDEX($D$2:$D$6, MATCH(1, ($C$2:$C$6="Electronics")*($E$2:$E$6<100), 0))

이전 Excel 버전(365 이전)에서는 Ctrl + Shift + Enter를 눌러 배열 수식으로 입력하세요 — Excel이 중괄호 {}로 감쌉니다. Excel 365 및 Excel 2021에서는 동적 배열이 자동으로 처리하므로 일반 Enter만으로 충분합니다.

작동 원리: 각 조건은 TRUE/FALSE 값(1과 0)의 배열을 생성합니다. 이들을 곱하면 두 조건이 모두 TRUE인 경우에만 1이 되는 새 배열이 만들어집니다. 그러면 MATCH가 첫 번째 1을 찾고, INDEX가 해당하는 가격을 반환합니다.

INDEX MATCH vs. XLOOKUP

Excel 365는 단일 함수로 많은 조회 작업을 간소화하는 XLOOKUP을 도입했습니다. XLOOKUP은 간단한 조회에 탁월하며, 왼쪽 조회도 기본적으로 처리합니다. 그러나 INDEX MATCH는 여러 이유로 여전히 유효합니다.

• 통합 문서를 공유할 수 있는 Excel 2010, 2013, 2016, 2019를 포함한 모든 Excel 버전에서 작동합니다.
• 중첩된 MATCH 함수를 사용하는 2차원 조회 패턴은 XLOOKUP 대안보다 여전히 더 간결합니다.
• 많은 전문 재무 모델과 감사 서식 파일이 INDEX MATCH를 기반으로 구축되어 있으므로, 이를 읽고 유지 관리하려면 해당 조합에 익숙해야 합니다.

INDEX MATCH를 이해하는 것은 조회 수식이 자동으로 업데이트되는 차트와 요약 테이블에 데이터를 제공하는 Excel에서 동적 대시보드 만들기와 같은 고급 작업을 수행할 때도 기초가 됩니다.

INDEX MATCH 성공을 위한 빠른 팁

• 근사 일치를 사용할 특별한 이유가 없다면 항상 match_type으로 0을 사용하세요(정확히 일치).
• 범위를 절대 참조($)로 고정하세요 — 수식을 행이나 열에 걸쳐 복사해도 올바르게 작동합니다.
• 조회 범위와 반환 범위의 길이를 동일하게 유지하세요 — 크기가 맞지 않으면 잘못된 결과가 나옵니다.
• 이름 상자(수식 입력줄 왼쪽)를 사용하여 범위 이름을 지정하세요 — 가독성이 높아집니다: =INDEX(UnitPrices, MATCH(H2, ProductIDs, 0))는 셀 참조보다 훨씬 감사하기 쉽습니다.
• IF 및 SUMIFS와 결합하세요 — 조건부 조회를 위해 관련 조건부 수식 기법은 SUMIF 및 SUMIFS 가이드를 참조하세요.

INDEX MATCH 수식 즉시 얻기

복잡한 조회 요구 사항 — 다중 조건, 비표준 테이블 레이아웃, 또는 시트 간 참조 — 앞에서 막막하다면, GPTExcel에 필요한 내용을 일반 언어로 설명하면 올바른 절대 참조와 오류 처리가 포함된 즉시 사용 가능한 INDEX MATCH 수식을 몇 초 안에 받을 수 있습니다. 이는 추측을 없애고, 수동으로 시행착오를 겪지 않고도 바로 작동하는 수식을 얻을 수 있게 해줍니다.

AI를 활용한 더 광범위한 수식 작성 기법에 대해서는 ChatGPT를 사용하여 Excel 수식 작성하기 글에서 전체 워크플로를 자세히 다룹니다.

자주 묻는 질문

INDEX MATCH가 항상 VLOOKUP보다 낫나요?

대부분의 전문적인 사용 사례에서는 그렇습니다. INDEX MATCH는 왼쪽 조회를 처리하고, 열 삽입으로 깨지지 않으며, 2차원 및 다중 조건 일치를 지원합니다. VLOOKUP은 기본적인 오른쪽 조회를 작성하기에는 더 간단하지만, 데이터가 복잡해질수록 그 한계가 명백해집니다.

INDEX MATCH에 Ctrl+Shift+Enter를 눌러야 하나요?

Excel 2019 이하에서 다중 조건 배열 버전의 수식을 사용하는 경우에만 해당됩니다. 표준 단일 조건 INDEX MATCH 수식은 모든 Excel 버전에서 일반 Enter 키로 입력합니다. 동적 배열을 지원하는 Excel 365 및 Excel 2021에서는 다중 조건 버전도 배열 바로 가기가 필요하지 않습니다.

조회 열에 중복 값이 있으면 어떻게 되나요?

MATCH는 항상 찾은 첫 번째 일치 항목의 위치를 반환합니다. 조회 열에 중복이 있고 각 항목의 데이터를 가져와야 한다면, 연결된 키를 사용하는 보조 열을 활용하거나, 파워 쿼리 가이드에서 다루는 파워 쿼리를 사용하여 조회를 적용하기 전에 데이터를 재구성하는 것을 고려해 보세요.

INDEX MATCH로 다른 시트의 값을 조회할 수 있나요?

예. 범위 참조에 시트 이름을 포함하기만 하면 됩니다. 예를 들어: =INDEX(Sheet2!$D$2:$D$100, MATCH(H2, Sheet2!$A$2:$A$100, 0)). 범위가 같은 시트에 있든 동일한 통합 문서 내의 다른 시트에 있든 수식은 동일하게 작동합니다.