엑셀지식
2026년 5월 1일
작성자: JH (중견기업 인사팀 출신)
엑셀 INDEX + MATCH 완벽 정리
왼쪽 열 조회, 2차원 검색, 열 삽입에도 끄떡없는 조합
VLOOKUP은 첫 번째 열만 검색 기준으로 쓸 수 있고, 중간에 열을 삽입하면 col_index_num이 틀어지는 문제가 있습니다. INDEX + MATCH 조합은 이 두 가지 한계를 모두 극복합니다. 처음에는 두 함수를 조합하는 방식이 낯설게 느껴지지만, 한 번 익히면 VLOOKUP보다 훨씬 유연하게 쓸 수 있습니다.
INDEX 함수란?
INDEX는 지정한 범위에서 행 번호와 열 번호로 값을 반환하는 함수입니다.
=INDEX(array, row_num, [col_num])
예: =INDEX(B2:B100, 3) → B2:B100 범위의 3번째 행 값(B4)을 반환합니다.
MATCH 함수란?
MATCH는 지정한 범위에서 찾는 값이 몇 번째 위치에 있는지 숫자로 반환하는 함수입니다.
=MATCH(lookup_value, lookup_array, [match_type])
| match_type | 동작 |
|---|
0 | 정확히 일치 (실무에서 거의 항상 0 사용) |
1 | 이하 근사값 (오름차순 정렬 필요) |
-1 | 이상 근사값 (내림차순 정렬 필요) |
예: =MATCH("박민준", A2:A100, 0) → A2:A100에서 "박민준"이 몇 번째 행에 있는지 반환
STEP 1 — INDEX + MATCH 기본 조합
MATCH가 위치를 찾아 주면, INDEX가 그 위치의 값을 가져오는 구조입니다.
1사번(A열)으로 이름(B열) 찾기
=INDEX(B2:B100, MATCH(E1, A2:A100, 0))
MATCH(E1, A2:A100, 0) — E1의 사번이 A열 몇 번째 행인지 찾음INDEX(B2:B100, ...) — 그 행 번호에 해당하는 B열 값(이름) 반환
이 수식은 A열이 첫 번째 열이 아니어도, B열이 A열의 왼쪽에 있어도 동일하게 작동합니다.
STEP 2 — 왼쪽 열 반환 (VLOOKUP이 못 하는 것)
1이름(B열)으로 사번(A열) 거꾸로 찾기
=INDEX(A2:A100, MATCH("박민준", B2:B100, 0))
B열(이름)에서 위치를 찾아 A열(사번)의 값을 가져옵니다. VLOOKUP은 이 방향이 불가능하지만 INDEX+MATCH는 어느 방향이든 가능합니다.
STEP 3 — 열 삽입에도 안전한 수식
1MATCH로 열 번호도 동적으로 찾기
헤더 행(1행)에서 원하는 열 이름을 검색해 열 번호를 자동으로 구합니다. 중간에 열을 삽입해도 수식이 깨지지 않습니다.
=INDEX(A1:Z100, MATCH(E1, A1:A100, 0), MATCH("부서", A1:Z1, 0))
- 첫 번째 MATCH — E1 사번이 A열 몇 번째 행인지
- 두 번째 MATCH — 1행 헤더에서 "부서"가 몇 번째 열인지
표에 열이 추가·삭제되어도 "부서" 헤더를 자동으로 찾아 올바른 데이터를 반환합니다.
STEP 4 — IFERROR와 결합해 오류 처리
=IFERROR(INDEX(B2:B100, MATCH(E1, A2:A100, 0)), "없음")
찾는 값이 없으면 #N/A 대신 "없음"을 표시합니다.
INDEX + MATCH vs VLOOKUP 비교
| 기능 | VLOOKUP | INDEX + MATCH |
|---|
| 왼쪽 열 반환 | 불가 | 가능 |
| 열 삽입 후 안전성 | col_index_num 틀어짐 | MATCH로 자동 추적 |
| 범위 크기 | 전체 표를 범위로 지정 | 필요한 열만 지정 가능 → 속도 유리 |
| 2차원 교차 조회 | 불가 | 가능 (행+열 MATCH 동시 사용) |
| Excel 버전 제한 | 없음 | 없음 |
자주 묻는 질문
INDEX+MATCH가 VLOOKUP보다 어렵게 느껴지는데 꼭 배워야 하나요?
Microsoft 365 이상이라면 XLOOKUP으로 대체할 수 있으므로 반드시 배울 필요는 없습니다. 하지만 구버전 Excel 환경에서 "왼쪽 열 반환"이나 "열 삽입에 강한 수식"이 필요하다면 INDEX+MATCH가 유일한 해결책입니다. 한 번 익혀두면 매우 강력합니다.
MATCH가 #N/A를 반환하면 어떻게 해야 하나요?
찾는 값이 범위에 없는 경우입니다. IFERROR로 감싸거나, 데이터에 공백·숨겨진 문자가 있지 않은지 확인하세요. 텍스트와 숫자 형식 불일치도 흔한 원인입니다.
중복 값이 있을 때 첫 번째 일치가 아닌 특정 행을 반환할 수 있나요?
MATCH는 기본적으로 첫 번째 일치값의 위치를 반환합니다. 마지막 일치값을 원한다면 XLOOKUP의 search_mode=-1을 사용하거나, 배열 수식을 이용해 마지막 MATCH를 구현해야 합니다.