이 문서는 Microsoft Excel에서 데이터를 조회하고 추출하는 데 가장 강력하고 유연한 도구 중 하나인 `INDEX` 함수와 `MATCH` 함수를 사용하는 방법에 대한 포괄적인 가이드입니다. 이 두 함수를 함께 사용하면 기존의 `VLOOKUP` 함수가 가진 한계를 극복하고, 더욱 복잡하고 다양한 조회 시나리오를 처리할 수 있습니다. 각 함수의 기본 개념부터 실제 적용 예시, 그리고 `INDEX/MATCH` 조합의 장점까지 상세하게 다룹니다.
이 가이드를 통해 엑셀 데이터 관리 및 분석 능력을 한 단계 더 향상시킬 수 있을 것입니다.
`MATCH` 함수는 지정된 범위(단일 행 또는 단일 열) 내에서 특정 항목의 상대적인 위치를 찾아 반환합니다. 예를 들어, 목록에서 “사과”가 몇 번째 항목인지 알고 싶을 때 유용합니다.
`MATCH` 함수의 기본 구문은 다음과 같습니다.
lookup_value: 찾으려는 값입니다. 숫자, 텍스트, 논리값 또는 숫자에 대한 셀 참조일 수 있습니다.lookup_array: 값을 찾을 영역입니다. 단일 행 또는 단일 열의 범위여야 합니다.[match_type]: 선택 사항이며, 일치 유형을 지정합니다.0 (또는 생략): lookup_value와 정확히 일치하는 첫 번째 값을 찾습니다. lookup_array는 정렬될 필요가 없습니다.1: lookup_value보다 작거나 같은 값 중 가장 큰 값을 찾습니다. lookup_array는 오름차순으로 정렬되어야 합니다.'-1': lookup_value보다 크거나 같은 값 중 가장 작은 값을 찾습니다. lookup_array는 내림차순으로 정렬되어야 합니다.다음 표에서 “바나나”가 몇 번째 위치에 있는지 찾아봅시다.
| 과일 |
| ———- |
| 사과 |
| 바나나 |
| 오렌지 |
| 포도 |
=MATCH("바나나", A2:A5, 0)
위 수식은 “바나나”가 `A2:A5` 범위에서 2번째 위치에 있으므로 2를 반환합니다.
`INDEX` 함수는 지정된 범위 또는 배열 내에서 특정 행과 열의 교차점에 있는 값을 반환합니다. 이는 `MATCH` 함수가 반환하는 위치 값을 활용하여 데이터를 추출할 때 매우 유용합니다.
`INDEX` 함수는 두 가지 구문 형태를 가집니다.
배열 형태: `INDEX(array, row_num, [column_num])`참조 형태: `INDEX(reference, row_num, [column_num], [area_num])`
일반적으로 배열 형태가 `INDEX/MATCH` 조합에서 사용됩니다.
array: 값을 반환할 셀 또는 셀 범위입니다.row_num: array 내에서 값을 반환할 행의 번호입니다.[column_num]: 선택 사항이며, array 내에서 값을 반환할 열의 번호입니다. array가 단일 열인 경우 생략할 수 있습니다.[area_num]: 선택 사항이며, reference가 여러 범위로 구성될 때 어떤 영역에서 값을 가져올지 지정합니다. (일반적인 `INDEX/MATCH` 조합에서는 거의 사용되지 않습니다.)다음 표에서 3행 2열에 있는 값을 찾아봅시다.
| 제품명 | 가격 | 재고 |
|---|---|---|
| 키보드 | 50000 | 100 |
| 마우스 | 20000 | 250 |
| 모니터 | 200000 | 50 |
| 웹캠 | 30000 | 120 |
=INDEX(A2:C5, 3, 2)
위 수식은 `A2:C5` 범위에서 3행 2열에 있는 값인 200000을 반환합니다.
`INDEX`와 `MATCH` 함수를 결합하면 `VLOOKUP`의 단점을 보완하고 훨씬 유연한 데이터 조회를 수행할 수 있습니다. `MATCH` 함수로 원하는 값의 위치(행 또는 열 번호)를 찾고, 그 위치를 `INDEX` 함수에 전달하여 실제 데이터를 추출하는 방식입니다.
lookup_value가 lookup_array에서 몇 번째 위치에 있는지 반환합니다.다음 표에서 제품 ID로 제품명을 찾아봅시다.
| 제품 ID | 제품명 | 가격 |
|---|---|---|
| P001 | 키보드 | 50000 |
| P002 | 마우스 | 20000 |
| P003 | 모니터 | 200000 |
| P004 | 웹캠 | 30000 |
우리가 찾고자 하는 제품 ID가 P002라고 가정해 봅시다.
=INDEX(B2:B5, MATCH("P002", A2:A5, 0))
* `MATCH(“P002”, A2:A5, 0)`: `A2:A5` 범위에서 “P002”가 2번째 위치에 있음을 반환합니다. * `INDEX(B2:B5, 2)`: `B2:B5` 범위에서 2번째 위치에 있는 값인 “마우스”를 반환합니다.
다음 표에서 특정 제품과 특정 월의 매출액을 조회해봅시다.
| 제품명 | 1월 | 2월 | 3월 |
|---|---|---|---|
| 키보드 | 100 | 120 | 110 |
| 마우스 | 50 | 60 | 55 |
| 모니터 | 200 | 210 | 190 |
우리가 찾고자 하는 값은 “마우스”의 “2월” 매출액입니다.
=INDEX(B2:D4, MATCH("마우스", A2:A4, 0), MATCH("2월", B1:D1, 0))
* `MATCH(“마우스”, A2:A4, 0)`: `A2:A4` 범위에서 “마우스”가 2번째 위치(행)에 있음을 반환합니다.
* `MATCH(“2월”, B1:D1, 0)`: `B1:D1` 범위에서 “2월”이 2번째 위치(열)에 있음을 반환합니다.
* `INDEX(B2:D4, 2, 2)`: `B2:D4` 범위에서 2행 2열에 있는 값인 60을 반환합니다.
`INDEX/MATCH` 조합은 `VLOOKUP` 함수에 비해 여러 가지 중요한 장점을 제공합니다.
col_index_num이 변경되어 수식이 깨질 위험이 있습니다.`INDEX/MATCH`를 사용하여 실제 시나리오에서 데이터를 조회하는 방법을 더 자세히 살펴봅니다.
고객명으로 고객 ID를 조회하는 경우처럼, 조회하려는 열이 조회 기준 열의 왼쪽에 있는 경우 `INDEX/MATCH`가 유용합니다.
| 고객 ID | 고객명 | 연락처 |
|---|---|---|
| C001 | 김철수 | 010-1111-2222 |
| C002 | 이영희 | 010-3333-4444 |
| C003 | 박민준 | 010-5555-6666 |
고객명 “이영희”의 고객 ID를 찾고 싶다면:
=INDEX(A2:A4, MATCH("이영희", B2:B4, 0))
이 수식은 “이영희”의 고객 ID인 C002를 반환합니다. `VLOOKUP`으로는 불가능한 조회입니다.
제품명과 색상 두 가지 조건을 만족하는 제품의 가격을 조회해봅시다.
| 제품 | 색상 | 가격 |
|---|---|---|
| 티셔츠 | 빨강 | 15000 |
| 티셔츠 | 파랑 | 16000 |
| 바지 | 검정 | 30000 |
| 바지 | 흰색 | 32000 |
“티셔츠”이면서 “파랑”인 제품의 가격을 찾고 싶다면, 배열 수식을 사용해야 합니다. (수식을 입력하고 Ctrl+Shift+Enter를 눌러야 합니다.)
=INDEX(C2:C5, MATCH(1, (A2:A5="티셔츠")*(B2:B5="파랑"), 0))
* `(A2:A5=“티셔츠”)*(B2:B5=“파랑”)`: 이 부분은 배열을 생성합니다. 조건이 참이면 1, 거짓이면 0이 됩니다. 예를 들어, `{1;0;0;0}`과 같은 배열이 생성됩니다.
* `MATCH(1, …, 0)`: 이 배열에서 첫 번째 1의 위치를 찾습니다.
* `INDEX(C2:C5, …)`: 찾은 위치를 기반으로 `C2:C5` 범위에서 값을 반환합니다.
이 수식은 16000을 반환합니다.
`MATCH` 함수의 match_type을 1 또는 -1로 설정하여 근사치 일치를 수행할 수 있습니다. 예를 들어, 점수에 따른 등급을 조회할 때 유용합니다.
| 점수 | 등급 |
|---|---|
| 0 | F |
| 60 | D |
| 70 | C |
| 80 | B |
| 90 | A |
점수 75에 대한 등급을 조회하고 싶다면:
=INDEX(B2:B6, MATCH(75, A2:A6, 1))
* `MATCH(75, A2:A6, 1)`: A2:A6에서 75보다 작거나 같은 값 중 가장 큰 값인 70의 위치(3번째)를 찾습니다.
* `INDEX(B2:B6, 3)`: B2:B6에서 3번째 위치의 값인 C를 반환합니다.
`INDEX` 함수와 `MATCH` 함수는 엑셀에서 데이터를 조회하고 분석하는 데 있어 매우 강력하고 유연한 도구입니다. 이 두 함수를 함께 사용하면 `VLOOKUP`의 한계를 뛰어넘어 왼쪽 조회, 양방향 조회, 그리고 복잡한 다중 조건 조회까지 다양한 시나리오를 효과적으로 처리할 수 있습니다.
처음에는 구문이 다소 복잡하게 느껴질 수 있지만, 각 함수의 역할과 조합 원리를 이해하고 꾸준히 연습한다면 엑셀 데이터 처리 능력을 크게 향상시킬 수 있을 것입니다. 이 가이드에서 제시된 예제들을 직접 실습해보면서 `INDEX/MATCH` 조합의 진정한 힘을 경험해보시길 바랍니다.