실무 필수! 엑셀 INDEX & MATCH 함수 활용법 알아보도록 하자

엑셀에서 데이터를 효율적으로 검색하고 추출하는 기능은 실무에서 매우 중요합니다.

대표적으로 많이 알려진 함수는 VLOOKUP이지만, 이보다 더 유연하고 강력한 조합이 있습니다.

바로 INDEX와 MATCH 함수입니다.

이 두 함수를 조합하면 방향 제약 없이 원하는 데이터를 정확하게 추출할 수 있습니다.

이번 글에서는 INDEX와 MATCH 함수의 기초 개념부터 실무에서 유용하게 쓰이는 예시까지 상세하게 소개하겠습니다.

 

 

 INDEX 함수란?

INDEX(array, row_num, [column_num]) 형식으로 사용됩니다.

INDEX 함수는 지정된 범위(array)에서 특정 행과 열에 위치한 값을 반환합니다.

예제:

=INDEX(A1:C3, 2, 3) → A1:C3 범위의 2행 3열에 있는 값 반환-이 함수 하나만으로는 원하는 데이터를 정확히 찾아내기 어렵기 때문에, 보통 MATCH 함수와 함께 사용됩니다.

MATCH 함수란?

MATCH(lookup_value, lookup_array, [match_type]) 형식으로 사용됩니다.

MATCH 함수는 특정 값이 범위에서 몇 번째 위치에 있는지를 반환합니다.

예제:

=MATCH("사과", A1:A5, 0) → A1:A5 중 "사과"가 몇 번째에 있는지 반환 (정확히 일치하는 값 찾기)-여기서 match_type은 다음과 같은 옵션을 가집니다:-0: 정확히 일치

1: 이하에서 가장 가까운 값 (정렬 필요)--1: 이상에서 가장 가까운 값 (역순 정렬 필요)-실무에서는 대부분 0을 사용해 정확히 일치하는 값을 찾습니다.

INDEX + MATCH 함수 조합 사용법

이제 본격적으로 두 함수를 조합하여 VLOOKUP을 대체하는 방법을 알아보겠습니다.

기본 구조는 다음과 같습니다:

=INDEX(결과_범위, MATCH(찾을값, 기준_범위, 0))-예제 상황:

A열: 이름 | B열: 부서

홍길동 인사팀

김철수 총무팀

이영희 마케팅팀

이름을 기준으로 부서를 찾고 싶다면:-=INDEX(B2:B4, MATCH("김철수", A2:A4, 0)) → 결과: 총무팀

INDEX & MATCH가 VLOOKUP보다 나은 이유

열 방향 제약 없음: VLOOKUP은 기준 열의 오른쪽에 있는 값만 참조 가능하지만, INDEX & MATCH는 왼쪽/오른쪽 모두 가능

범위 변경에 유연: VLOOKUP은 참조 열 번호가 고정되어 열 추가 시 오류 발생 가능

반면 MATCH는 동적으로 위치를 찾아 오류 가능성 낮음

속도 향상: 대용량 데이터에서 INDEX & MATCH 조합이 더 빠른 경우가 많음

 

 

 

 가로형 데이터에서의 INDEX & MATCH

세로뿐 아니라 가로 데이터에도 적용 가능합니다.

예제:

A1: 항목명 → B1~D1: 1월, 2월, 3월-A2: 판매량 → B2~D2: 100, 200, 150-2월의 판매량을 찾으려면:

=INDEX(B2:D2, MATCH("2월", B1:D1, 0)) → 결과: 200

2차원 검색 (행과 열 모두 활용)

INDEX 함수의 강력한 기능 중 하나는 행과 열을 동시에 사용할 수 있다는 점입니다.

예제:

B C D

| 1월 | 2월 | 3월

A 김철수 100 200 150-B 이영희 90 210 180-김철수의 3월 판매량을 찾으려면:-=INDEX(B2:D3, MATCH("김철수", A2:A3, 0), MATCH("3월", B1:D1, 0)) → 결과: 150

실무 응용 팁

데이터 유효성 검사와 함께 사용: 드롭다운에서 선택한 값을 기반으로 다른 값을 자동 표시할 수 있음-동적 보고서 생성: INDEX & MATCH 조합으로 조건에 따라 자동으로 내용이 바뀌는 리포트를 만들 수 있음-중복 없는 고유값 검색: 고급 필터와 함께 사용 시 유용함

오류 방지 팁

MATCH 함수의 결과가 오류일 경우 INDEX도 오류를 반환하므로, IFERROR 함수와 함께 사용하는 것이 좋습니다.

=IFERROR(INDEX(B2:B4, MATCH("홍길동", A2:A4, 0)), "데이터 없음")

마무리

INDEX & MATCH 함수는 엑셀 실무에서 매우 유용한 조합입니다. 특히 VLOOKUP의 한계를 극복하고 더 유연하게 데이터를 다룰 수 있다는 점에서 많은 전문가들이 선호합니다. 이번 글에서 소개한 내용을 토대로 직접 다양한 상황에 적용해 보세요. 사용하면 할수록 익숙해지고, 엑셀 활용 능력이 한 단계 업그레이드될 것입니다!