엑셀 INDEX MATCH 함수 조합 사용법 – VLOOKUP이 안 될 때 대안

엑셀에서 표 안의 값을 찾을 때 VLOOKUP을 가장 먼저 떠올리지만, 찾는 기준 열이 첫 번째 열이 아니거나 열을 추가·삭제하면 수식이 깨지는 상황을 경험했을 것입니다. 이 글은 VLOOKUP의 한계를 INDEX와 MATCH 함수를 조합해 해결하는 방법을 설명합니다. 함수 구문부터 실전 예시, VLOOKUP과의 비교, 자주 하는 실수까지 순서대로 다룹니다.

---

INDEX 함수와 MATCH 함수란

INDEX와 MATCH는 별개의 함수입니다. 각 함수의 역할을 먼저 이해하면 조합 공식이 자연스럽게 읽힙니다.

INDEX 함수 구문과 인수

INDEX 함수는 지정한 범위에서 특정 행·열 위치에 있는 값을 반환합니다.

=INDEX(array, row_num, [column_num])

인수	설명	필수 여부
array	값을 찾을 범위 또는 배열	필수
row_num	반환할 값이 있는 행 번호	필수
column_num	반환할 값이 있는 열 번호	생략 가능 (단일 열 범위인 경우)

예를 들어 =INDEX(B2:B10, 3)은 B2:B10 범위의 3번째 셀, 즉 B4의 값을 반환합니다.

MATCH 함수 구문과 인수

MATCH 함수는 범위에서 찾을 값의 위치(순번)를 반환합니다. 값 자체가 아니라 몇 번째에 있는지를 알려줍니다.

=MATCH(lookup_value, lookup_array, [match_type])

인수	설명	필수 여부
lookup_value	찾을 값	필수
lookup_array	검색할 범위 (단일 행 또는 단일 열만 가능)	필수
match_type	0=정확히 일치 / 1=이하 최대값(오름차순) / -1=이상 최솟값(내림차순)	생략 가능 (기본값 1)

예를 들어 D2:D10 범위에 상품코드가 있고 "A005"를 찾을 때 =MATCH("A005", D2:D10, 0)은 "A005"가 몇 번째 행에 있는지를 숫자로 반환합니다.

---

INDEX + MATCH를 조합하는 이유

VLOOKUP의 주요 제한 사항

VLOOKUP은 다음 세 가지 상황에서 한계를 보입니다.

첫째, 기준 열이 반드시 범위의 첫 번째 열이어야 합니다. 상품명 열을 기준으로 상품코드를 찾는 것처럼 왼쪽 방향으로는 조회가 불가능합니다.

둘째, 열 번호를 숫자로 직접 입력합니다. =VLOOKUP(A2, B:E, 3, 0)에서 3번째 열을 지정한 상태에서 B와 C 사이에 열을 삽입하면 원래 3번째이던 열이 4번째로 밀려 잘못된 값을 반환합니다.

셋째, 다중 조건 검색을 기본으로 지원하지 않습니다.

INDEX+MATCH가 해결하는 문제

INDEX는 '어느 위치의 값을 가져올지'를 담당하고, MATCH는 '그 위치가 몇 번째인지'를 알려줍니다. 두 함수를 결합하면 VLOOKUP의 세 가지 제한이 모두 해소됩니다.

---

기본 사용법 – 왼쪽 열 조회 예시

수식 구조 이해

기본 조합 형태는 다음과 같습니다.

=INDEX(반환범위, MATCH(찾을값, 검색범위, 0))

수식이 실행되는 순서입니다.

1. MATCH 함수가 먼저 실행되어 찾을 값의 위치(순번)를 반환합니다.
2. 반환된 순번이 INDEX 함수의 row_num 자리에 들어갑니다.
3. INDEX 함수가 반환범위의 해당 순번 위치 값을 가져옵니다.

실전 예시 테이블

아래와 같은 상품 정보 테이블이 있다고 가정합니다.

A열: 상품명	B열: 단가	C열: 상품코드
키보드	45,000	A001
마우스	28,000	A002
모니터	320,000	A003
헤드셋	89,000	A004
마우스패드	6,500	A005

상품코드 "A005"에 해당하는 상품명을 찾으려면 VLOOKUP으로는 불가능합니다. 기준이 되는 상품코드(C열)가 반환하려는 상품명(A열)의 오른쪽에 있기 때문입니다.

INDEX+MATCH를 사용하면 다음과 같이 작성합니다.

=INDEX(A2:A6, MATCH("A005", C2:C6, 0))

MATCH("A005", C2:C6, 0)이 5를 반환하고, INDEX(A2:A6, 5)가 A6 셀의 "마우스패드"를 가져옵니다.

단가를 가져오려면 반환범위만 변경합니다.

=INDEX(B2:B6, MATCH("A005", C2:C6, 0))

실무에서 수식을 복사할 때는 절대 참조($)를 사용합니다.

=INDEX($A$2:$A$6, MATCH(E2, $C$2:$C$6, 0))

---

VLOOKUP vs INDEX+MATCH 비교표

항목	VLOOKUP	INDEX+MATCH
조회 방향	왼쪽→오른쪽만 가능	좌·우 모두 가능
기준 열 위치	반드시 첫 번째 열	어느 열이든 가능
열 추가·삭제 영향	열 번호가 밀려 오류 발생 가능	범위 참조라 영향 없음
대용량 데이터 속도	상대적으로 느림	상대적으로 빠름
다중 조건 검색	기본 지원 안 함	배열 수식으로 가능
수식 복잡도	단순	다소 복잡

단순한 오른쪽 방향 조회에는 VLOOKUP이 더 간결합니다. 기준 열이 첫 번째가 아니거나 열 삽입·삭제가 잦은 표에서는 INDEX+MATCH가 안정적입니다.

---

다중 조건으로 값 찾기

배열 수식을 이용한 다중 조건 예시

단일 조건이 아닌 두 개 이상의 조건을 동시에 만족하는 값을 찾을 때도 INDEX+MATCH를 활용할 수 있습니다.

A열: 상품코드	B열: 연도	C열: 단가
A001	2023	45,000
A001	2024	48,000
A002	2023	28,000
A002	2024	30,000

상품코드 "A001", 연도 2024에 해당하는 단가를 찾는 수식입니다.

=INDEX(C2:C5, MATCH(1, (A2:A5="A001")*(B2:B5=2024), 0))

Excel 2019 이하에서는 Ctrl+Shift+Enter로 입력하여 배열 수식으로 처리합니다. Microsoft 365와 Excel 2021 이상에서는 일반 Enter로 입력해도 작동합니다.

수식 내부에서 (A2:A5="A001")*(B2:B5=2024)는 두 조건이 모두 참인 행에서만 1을 반환합니다. MATCH는 그 1의 위치를 찾아 INDEX로 넘깁니다.

---

자주 하는 실수와 주의사항

match_type을 생략하면 의도치 않은 결과가 납니다.
MATCH 함수의 세 번째 인수를 생략하면 기본값이 1로 적용됩니다. 검색 범위가 오름차순으로 정렬되어 있다는 전제로 동작하기 때문에, 정렬되지 않은 범위에서는 잘못된 위치를 반환하거나 오류가 납니다. 정확히 일치하는 값을 찾으려면 반드시 0을 입력합니다.

숫자와 텍스트 형식 불일치.
검색 범위에 있는 값이 숫자인데 찾을 값이 텍스트 형식이면 MATCH가 값을 찾지 못해 #N/A 오류가 발생합니다. 반대의 경우도 동일합니다. 셀 서식을 확인하거나 VALUE() 함수로 형식을 통일합니다.

lookup_array는 단일 행 또는 단일 열이어야 합니다.
MATCH의 두 번째 인수에 다중 열 범위(예: A2:C10)를 입력하면 오류가 납니다. 검색 기준이 되는 열만 단일 열로 지정해야 합니다.

INDEX 범위와 MATCH 범위의 행 수를 일치시킵니다.
INDEX(A2:A10, MATCH(..., B2:B8, 0))처럼 두 범위의 행 수가 다르면 의도하지 않은 결과가 나올 수 있습니다. 두 범위가 같은 행 수를 가지도록 통일합니다.

---

마치며

INDEX+MATCH 조합은 VLOOKUP보다 수식이 한 단계 복잡하지만, 왼쪽 열 조회, 열 구조 변경에 강한 안정성, 다중 조건 지원이라는 실질적인 장점이 있습니다. VLOOKUP이 부족한 상황에서 INDEX+MATCH를 먼저 시도해 보면 대부분의 조회 문제를 해결할 수 있습니다. 엑셀 2021 이상 또는 Microsoft 365 환경이라면 XLOOKUP도 대안이 될 수 있으나, 하위 버전 호환성이 필요한 경우에는 INDEX+MATCH가 안전한 선택입니다.