엑셀 INDEX MATCH 함수 활용법: 여러 조건 조회, 왼쪽 값 찾기, VLOOKUP 차이

핵심 요약
엑셀 INDEX MATCH 함수는 VLOOKUP으로 풀기 어려운 조회를 대신합니다. 기준 열이 오른쪽에 있어도 =INDEX(반환범위, MATCH(찾는값, 찾을범위, 0)) 형태로 찾고, 여러 조건 조회는 =INDEX(반환범위, MATCH(1, (조건1)*(조건2), 0))으로 처리합니다. 엑셀 365·2021은 Enter, 구버전은 Ctrl+Shift+Enter로 입력합니다.

 
VLOOKUP으로 값을 찾다 보면 기준 열이 표의 왼쪽에 없거나, 조건이 두 개 이상이라 한 번에 걸리지 않는 경우를 만납니다.
 
이때는 위치를 숫자로 돌려주는 MATCH와 그 위치의 값을 꺼내는 INDEX를 조합하면 됩니다. 아래에서는 두 함수의 문법을 따로 본 뒤, 둘을 합친 기본 조회식과 여러 조건을 한꺼번에 거는 배열 수식까지 정리합니다. 엑셀 365와 구버전의 입력 차이도 함께 짚습니다.
 

 

1. 위치를 지정해 값을 산출 (INDEX)

INDEX 함수는 범위에서 지정한 위치의 값을 돌려줍니다. 배열형 문법은 다음과 같습니다.

=INDEX(array, row_num, [column_num])

 
array와 row_num은 필수이고, column_num은 선택입니다. 아래 사원 표에서 B열(이름)의 세 번째 값을 꺼내 보겠습니다.

2	1001	김서연	영업	2021
3	1002	박도윤	개발	2022
4	1003	이하준	영업	2023
5	1004	최지우	개발	2024

 
=INDEX(B2:B5, 3)은 B2:B5의 세 번째 칸, 즉 B4의 "이하준"을 반환합니다. 여러 열을 넘긴 경우에는 =INDEX(A2:D5, 3, 2)처럼 행·열 번호를 모두 주면 두 값이 만나는 칸의 값을 돌려줍니다.
 

2. 값이 있는 위치 찾기 (MATCH)

MATCH 함수는 찾는 값이 범위에서 몇 번째에 있는지를 숫자로 돌려줍니다.

=MATCH(lookup_value, lookup_array, [match_type])

 
match_type은 조회 방식을 정합니다. 정확히 일치하는 값을 찾을 때는 0을 씁니다.

1 (기본값)	찾는 값보다 작거나 같은 값 중 가장 큰 값	오름차순
0	정확히 일치하는 첫 값 (와일드카드 ?, * 사용 가능)	상관없음
-1	찾는 값보다 크거나 같은 값 중 가장 작은 값	내림차순

• Microsoft 지원 문서(support.microsoft.com) MATCH 함수 설명 기준.

 
위 사원 표에서 =MATCH("박도윤", B2:B5, 0)은 박도윤이 B열의 두 번째에 있으므로 2를 돌려줍니다. match_type을 생략하면 기본값 1이 적용되므로, 정확히 찾을 때는 0을 꼭 넣어야 합니다.
 

3. 두 함수를 합쳐 조회 (INDEX + MATCH)

MATCH가 찾은 위치를 INDEX의 행 번호 자리에 넣으면 VLOOKUP처럼 값을 조회할 수 있습니다. 기본형은 다음과 같습니다.

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

 
사원 표에서 이름으로 사번을 찾아보겠습니다. 사번(A열)은 이름(B열)보다 왼쪽에 있습니다.

=INDEX(A2:A5, MATCH("이하준", B2:B5, 0))

 
MATCH("이하준", B2:B5, 0)이 3을 돌려주고, INDEX(A2:A5, 3)이 A4의 1003을 반환합니다. VLOOKUP은 찾을 기준 열의 왼쪽에 있는 값을 가져오지 못하지만, INDEX와 MATCH 조합은 방향에 상관없이 조회합니다. 중간에 열을 추가해도 열 번호가 밀리지 않는 점도 장점입니다.
 

 

4. 여러 조건으로 조회 (배열 수식)

기준 열 하나만으로는 값이 하나로 좁혀지지 않을 때가 있습니다. 지점과 제품을 모두 맞춰야 매출을 찾는 아래 표가 그렇습니다.

2	서울	노트북	120
3	서울	마우스	30
4	부산	노트북	95
5	부산	마우스	25

 
조건을 곱셈으로 묶어 MATCH에 넣으면 두 조건을 모두 만족하는 위치를 찾습니다.

=INDEX(C2:C5, MATCH(1, (A2:A5="부산")*(B2:B5="마우스"), 0))

 
각 조건은 참이면 1, 거짓이면 0이 되고, 두 조건을 곱하면 둘 다 참인 행만 1이 됩니다. MATCH가 그 1의 위치, 즉 네 번째를 찾아 INDEX가 C5의 25를 반환합니다. 조건이 늘면 (조건1)*(조건2)*(조건3)처럼 이어 붙이면 됩니다.
 
주의할 점은, 이 수식은 배열 수식이라 엑셀 365와 2021에서는 Enter로 바로 입력되지만, 구버전에서는 Ctrl+Shift+Enter로 확정해야 중괄호가 붙으며 제대로 계산된다는 겁니다. 조건에 쓰는 범위의 크기(A2:A5, B2:B5)는 서로 같아야 합니다.
 
값 하나를 기준 열 오른쪽에서 찾을 때는 VLOOKUP도 충분하지만, 기준이 왼쪽에 있거나 열이 자주 바뀌면 INDEX와 MATCH 조합이 안정적입니다. 조건이 둘 이상이면 MATCH에 곱셈 배열을 넣어 한 번에 거는 방식이 깔끔합니다.
 
쓰는 엑셀 버전이 365·2021인지 그 이전인지부터 확인한 뒤, 배열 수식 입력 방법을 맞춰 보세요.
 

참고 자료

• INDEX function - Microsoft Support
• MATCH function - Microsoft Support
• INDEX/MATCH 기본 검색 공식 - 오빠두엑셀

 
※ 더 보기
엑셀로 만드는 MBTI 궁합표 계산기: VLOOKUP과 IF 함수로 팀원 성향 자동 매칭하기
엑셀 VLOOKUP 오류 해결 완벽 가이드: #N/A, #VALUE! 원인과 IFERROR 함수 활용법