INDEX MATCH 함수 조합 사용법 – VLOOKUP이 안 될 때 쓰는 방법

엑셀에서 표의 값을 찾을 때 VLOOKUP을 가장 많이 씁니다. 그런데 찾으려는 키값이 첫 번째 열이 아닌 경우, 또는 열을 추가한 뒤 수식이 틀어지는 경험을 해 본 적이 있다면 INDEX와 MATCH 함수 조합이 해결책이 됩니다. 이 글에서는 두 함수의 구문과 동작 원리, 기본 조합 사용법, 다중조건 검색, 그리고 실수하기 쉬운 부분까지 다룹니다.

---

VLOOKUP의 한계

키값이 첫 번째 열에 없으면 VLOOKUP은 쓸 수 없다

VLOOKUP 함수의 구문은 =VLOOKUP(찾을값, 범위, 열번호, [일치옵션])입니다. 범위의 첫 번째 열에서 찾을값을 검색한 뒤, 지정한 열번호 위치의 값을 반환합니다. 이 구조 때문에 키값이 두 번째 열 이후에 있거나, 반환할 값이 키값 열의 왼쪽에 있을 경우 VLOOKUP으로는 처리할 수 없습니다.

예를 들어 A열에 상품명, B열에 단가, C열에 상품코드가 있는 표에서 상품코드로 상품명을 찾으려 하면 VLOOKUP은 동작하지 않습니다. 키값(상품코드)이 첫 번째 열이 아니고, 반환값(상품명)이 키값 열의 왼쪽에 있기 때문입니다.

열 번호 직접 입력의 문제점

VLOOKUP은 반환할 값의 열 번호를 숫자로 직접 지정합니다. 표에 열을 추가하거나 삭제하면 이 번호가 맞지 않아 수식을 일일이 수정해야 합니다. 데이터가 많거나 수식이 여러 곳에 퍼져 있다면 유지 관리가 번거로워집니다.

---

INDEX 함수 – 위치로 값을 찾는다

구문과 인수 설명

=INDEX(array, row_num, [col_num])

인수	설명	필수 여부
array	값을 찾을 범위	필수
row_num	반환할 값이 있는 행 번호	필수
col_num	반환할 값이 있는 열 번호. 단일 열 범위이면 생략 가능.	선택

INDEX 함수는 지정한 범위에서 몇 번째 행, 몇 번째 열에 있는 값을 반환합니다. 범위가 단일 열이면 col_num은 생략해도 됩니다.

단독 사용 예시

B5:B11 범위의 5번째 행 값을 가져오려면 다음과 같이 입력합니다.

=INDEX(B5:B11, 5)

결과로 B9 셀의 값을 반환합니다. 단독으로는 행 번호를 직접 입력해야 하기 때문에 실용성이 낮습니다. MATCH와 조합해서 행 번호를 자동으로 구하는 것이 핵심입니다.

---

MATCH 함수 – 값의 위치(순번)를 찾는다

구문과 인수 설명

=MATCH(찾을값, 범위, [일치옵션])

인수	설명	필수 여부
찾을값	범위에서 검색할 값	필수
범위	검색 대상 범위 (단일 행 또는 단일 열)	필수
일치옵션	검색 방식 지정 (0 / 1 / -1)	선택

MATCH 함수는 값 자체가 아니라 값이 범위 안에서 몇 번째에 있는지 위치 번호를 반환합니다. 이 위치 번호를 INDEX의 row_num에 넘기는 것이 두 함수 조합의 핵심입니다.

일치옵션(0 / 1 / -1) 차이

옵션 값	동작
0	정확히 일치하는 값의 위치 반환. 실무에서 가장 많이 사용.
1 또는 생략	오름차순 정렬된 데이터에서 찾을값보다 작거나 같은 값 중 최대값의 위치 반환.
-1	내림차순 정렬된 데이터에서 찾을값보다 크거나 같은 값 중 최소값의 위치 반환.

정확한 값을 찾을 때는 반드시 0을 입력합니다. 1이나 -1 옵션은 데이터가 정렬되어 있어야만 올바르게 동작합니다.

---

INDEX + MATCH 조합 – 기본 사용법

기본 공식과 실행 순서

=INDEX(출력범위, MATCH(참조값, 참조범위, 0))

실행 순서는 다음과 같습니다.

1. MATCH 함수가 먼저 실행되어 참조범위에서 참조값의 위치 번호를 반환합니다.
2. 그 번호를 INDEX 함수의 row_num 인수로 넘깁니다.
3. INDEX 함수가 출력범위의 해당 위치에서 값을 반환합니다.

VLOOKUP과 비교했을 때 출력범위와 참조범위를 독립적으로 지정하기 때문에 키값 열이 어디에 있든 조회할 수 있고, 왼쪽 방향 조회도 가능합니다.

실무 예시 – 상품코드로 상품명·단가 조회

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

B열 (상품명)	C열 (단가)	D열 (상품코드)
키보드	55,000	A001
마우스	32,000	A002
모니터	280,000	A003
스피커	48,000	A004
마우스패드	6,500	A005

키값(상품코드)이 D열에 있고 반환값(상품명)이 B열에 있어 VLOOKUP으로는 조회할 수 없습니다.

상품코드 'A005'로 상품명을 찾는 수식:

=INDEX($B$5:$B$11, MATCH(C35, $D$5:$D$11, 0))

단가를 찾는 수식 (출력범위만 C열로 변경):

=INDEX($C$5:$C$11, MATCH(C35, $D$5:$D$11, 0))

출력범위와 참조범위에 절대참조($)를 붙이는 이유는 수식을 아래 셀로 복사할 때 범위가 이동하지 않도록 하기 위해서입니다. 참조값(C35)은 한 행씩 내려가야 하므로 상대참조로 그대로 둡니다.

---

다중조건 검색 – 두 가지 조건을 동시에 만족하는 값 찾기

다중조건 공식

단가를 연도별로 관리하는 경우처럼 두 가지 조건이 모두 맞아야 값을 찾을 수 있는 상황에서는 아래 공식을 사용합니다.

=INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2), 0))

(조건1=조건범위1)은 TRUE/FALSE 배열을 반환하고, TRUE는 1, FALSE는 0으로 처리됩니다. 두 배열을 곱하면 두 조건이 모두 참인 위치만 1이 되므로, MATCH(1, ..., 0)으로 그 위치를 찾습니다.

각 조건은 반드시 괄호로 묶어야 합니다. 곱하기(*)가 같다(=) 연산보다 우선순위가 높기 때문에 괄호 없이 입력하면 비교 연산이 먼저 실행되지 않습니다.

배열수식 입력 방법 (Ctrl+Shift+Enter)

다중조건 공식은 배열수식으로 입력해야 합니다. 수식을 입력한 뒤 Enter 대신 Ctrl + Shift + Enter를 누릅니다. 정상적으로 입력되면 수식 입력줄에 중괄호가 자동으로 붙습니다.

{=INDEX(출력범위, MATCH(1, (조건1=조건범위1)*(조건2=조건범위2), 0))}

중괄호는 직접 입력하는 것이 아니라 Ctrl+Shift+Enter를 눌러야 자동으로 생성됩니다. Microsoft 365 버전에서는 배열수식 없이 일반 Enter로도 동작하는 경우가 있습니다.

---

자주 하는 실수와 주의사항

셀 서식 문제

숫자를 찾을 때 셀 서식이 텍스트로 설정되어 있으면 MATCH가 값을 찾지 못해 #N/A 오류가 발생합니다. 특히 다른 프로그램(한글 문서, 웹 페이지 등)에서 복사한 데이터는 숫자처럼 보여도 텍스트로 저장된 경우가 있습니다. 셀을 선택한 뒤 홈 탭의 숫자 서식이 '숫자'로 설정되어 있는지 확인합니다.

절대참조 누락

수식을 다른 셀로 복사할 때 출력범위나 참조범위가 함께 이동하면 엉뚱한 결과가 나옵니다. 범위에는 $ 기호로 절대참조를 적용하고, 찾는 값(참조값)에는 상대참조를 유지합니다. F4 키를 눌러 절대참조로 전환할 수 있습니다.

다중조건에서 배열수식 미적용

다중조건 공식을 일반 Enter로 입력하면 결과가 #VALUE! 또는 #N/A로 나옵니다. 반드시 Ctrl+Shift+Enter로 입력해야 합니다. 수식 입력줄에 중괄호({})가 표시되는지 확인하면 됩니다.

Excel 버전 참고

INDEX+MATCH는 Excel 2007 이상 모든 버전에서 동작합니다. Excel 2021 또는 Microsoft 365를 사용한다면 XLOOKUP 함수로 왼쪽 조회와 다중조건을 더 간결하게 처리할 수도 있습니다.

---

INDEX와 MATCH 함수는 개별로는 단순하지만 조합하면 VLOOKUP으로 해결하지 못하는 조회 작업을 처리할 수 있습니다. 키값 위치 제약이 없고 열을 추가하거나 삭제해도 수식이 유지된다는 점에서 실무 데이터를 다룰 때 활용 범위가 넓습니다.