INDEX & MATCH 함수 실전 예제와 조합법
엑셀을 사용하다 보면 #N/A, 인덱스가 배열 경계를 벗어났습니다(index was outside the bounds of the array) 같은 오류를 자주 마주하게 됩니다. 특히 데이터가 많고 복잡할수록 정확한 위치에서 값을 찾아오는 것이 중요해지는데요, 이럴 때 진가를 발휘하는 함수가 바로 INDEX와 MATCH입니다.
오늘은 이 두 함수를 조합해 사용하는 법을 구체적인 예제와 함께 살펴보며, 관련 개념까지 확실히 잡아드릴게요. 검색 상위에 자주 등장하는 관련 키워드들도 함께 엮어, 엑셀을 더 똑똑하게 쓰는 방법을 알려드립니다.
INDEX 함수란?
INDEX 함수는 특정 범위 내에서 행과 열을 기준으로 원하는 위치의 값을 찾아주는 함수입니다. 쉽게 말해, '주소를 보고 물건을 찾는' 것과 같다고 보시면 됩니다.
예시
다음과 같은 표가 있을 때:
| 상품코드 | 이름 | 가격 |
|---|---|---|
| A001 | 키보드 | 30,000 |
| A002 | 마우스 | 15,000 |
| A003 | 모니터 | 200,000 |
=INDEX(C2:C4, 2)
→ 결과: 15,000
이유: C열의 두 번째 값이기 때문입니다.
MATCH 함수란?
MATCH 함수는 특정 값이 범위 내에서 몇 번째 위치에 있는지 찾아주는 함수입니다.
-
정확히 일치:
0 -
작거나 같은 값:
1 -
크거나 같은 값:
-1
예시
→ 결과: 2
이유: "마우스"는 B열에서 두 번째에 있기 때문이죠.
INDEX & MATCH 함수 조합법
이제 두 함수를 조합해볼까요?
MATCH 함수로 위치를 찾고, 그 위치를 INDEX 함수에 전달하면 정해진 조건에 맞는 값을 자동으로 찾아낼 수 있습니다.
기본 조합 예시
→ 결과: 15,000
"마우스"라는 이름을 B열에서 찾고, 그 위치를 바탕으로 C열에서 값을 반환합니다.
VLOOKUP과 INDEX MATCH 비교
많은 분들이 VLOOKUP 함수를 더 익숙하게 사용하는데요, 사실 INDEX MATCH 조합이 더 유연하고 강력합니다.
| 기능 | VLOOKUP | INDEX & MATCH |
|---|---|---|
| 검색 방향 | 왼쪽에서 오른쪽만 가능 | 왼쪽/오른쪽 모두 가능 |
| 열 삽입 시 오류 발생 | 예 | 없음 |
| 속도 (대량 데이터) | 느림 | 빠름 |
예를 들어 ‘상품명을 기준으로 가격’을 가져오면서도, 표 구조가 변경될 가능성이 있다면 INDEX & MATCH 조합이 훨씬 안전합니다.
실전 예제: 다중조건 검색
실제 업무에서는 단일 조건보다 여러 조건을 동시에 만족하는 값을 찾아야 할 때가 많습니다. 이때는 INDEX MATCH를 배열 수식과 함께 활용합니다.
예시
아래 표에서 ‘전자제품’ 카테고리이면서, ‘재고가 500 이상인 제품’의 가격을 찾는다고 가정합시다.
| 항목 | 가격 | 재고 | 카테고리 |
|---|---|---|---|
| A-001 | 950 | 50 | 전자제품 |
| A-002 | 500 | 500 | 전자제품 |
| B-001 | 150 | 1000 | 가구 |
※ Ctrl + Shift + Enter로 입력 (엑셀 365 이상은 자동 배열 수식 처리 가능)
→ 결과: 500
관련 오류 메시지와 해결법
❗ index was outside the bounds of the array
의미: 범위 밖의 값을 요청했을 때 발생하는 오류입니다.
해결법:
-
MATCH함수가 못 찾았을 경우#N/A반환 -
INDEX함수가 그 값을 그대로 받아 오류 발생
→IFERROR로 감싸면 깔끔하게 처리됩니다.
색인(index)의 의미와 활용
엑셀에서 ‘색인(index)’은 찾고자 하는 데이터를 빠르게 찾기 위한 기준표 역할을 합니다. 마치 책의 목차(index) 나 도서관의 색인 카드(index card) 와 비슷한 개념이죠.
영어에서 ‘index’는 지표, 색인, 목차 등 다양한 의미로 쓰이는데, index finger(집게손가락) 도 ‘무언가를 가리키는 역할’이라는 의미로 연결됩니다. 엑셀의 INDEX도 결국 원하는 데이터를 ‘가리키는’ 역할을 하죠.
INDEX 관련 용어들도 함께 이해하기
| 용어 | 의미 |
|---|---|
| index 복수형 | indexes 또는 indices (모두 사용 가능) |
| index of | 어떤 값이 배열이나 텍스트에서 몇 번째인지 |
| index 0 out of bounds for length 0 | 배열이 비어있을 때 발생하는 에러 |
| indexing | 인덱스를 사용하여 데이터 구조를 최적화하는 행위 |
| index of refraction | 물리학에서 굴절률을 의미 |
마무리하며: INDEX MATCH로 데이터 관리 능력 향상시키기
엑셀을 잘 다루는 사람들은 단순한 계산보다 구조화된 데이터에서 필요한 정보를 얼마나 효율적으로 추출하느냐에 따라 실력이 갈립니다. INDEX MATCH 함수는 단순한 검색을 넘어, 다중 조건, 역방향 검색, 대용량 데이터 처리 등에서 압도적인 효율을 자랑합니다.
기억하세요:
-
VLOOKUP보다 더 유연하고 안전한 INDEX MATCH
-
다중 조건도 해결 가능
-
오류 처리로 더욱 깔끔하게
독자 Q&A
Q. INDEX MATCH 함수는 어디서 자주 사용되나요?
A. 재고 관리, 급여 계산, 상품 검색, 회원 정보 찾기 등 거의 모든 업무에서 활용됩니다.
Q. ‘index match 여러 값 가져오기’는 어떻게 하나요?
A. 필터링된 값을 한 셀에 나열하거나, 동적 배열을 사용하여 여러 셀에 표시할 수 있습니다. 이 경우 TEXTJOIN 함수 또는 FILTER 함수와 함께 사용합니다.
궁금하신 점이 있다면 댓글로 질문해주세요!
실제 업무에 맞춘 추가 예제도 댓글 요청 주시면 반영해드릴게요 :)
댓글