목차
엑셀 INDEX/MATCH 함수 사용 가이드
이 문서는 Microsoft Excel에서 데이터를 조회하고 추출하는 데 가장 강력하고 유연한 도구 중 하나인 `INDEX` 함수와 `MATCH` 함수를 사용하는 방법에 대한 포괄적인 가이드입니다. 이 두 함수를 함께 사용하면 기존의 `VLOOKUP` 함수가 가진 한계를 극복하고, 더욱 복잡하고 다양한 조회 시나리오를 처리할 수 있습니다. 각 함수의 기본 개념부터 실제 적용 예시, 그리고 `INDEX/MATCH` 조합의 장점까지 상세하게 다룹니다.
이 가이드를 통해 엑셀 데이터 관리 및 분석 능력을 한 단계 더 향상시킬 수 있을 것입니다.
1. MATCH 함수 이해
`MATCH` 함수는 지정된 범위(단일 행 또는 단일 열) 내에서 특정 항목의 상대적인 위치를 찾아 반환합니다. 예를 들어, 목록에서 “사과”가 몇 번째 항목인지 알고 싶을 때 유용합니다.
1) MATCH 함수의 목적
- 특정 값을 찾을 범위 내에서 몇 번째 위치에 있는지 숫자로 반환합니다.
- 이 숫자는 `INDEX` 함수에서 행 번호나 열 번호로 사용될 수 있습니다.
2) MATCH 함수의 구문
`MATCH` 함수의 기본 구문은 다음과 같습니다.
- `MATCH(lookup_value, lookup_array, [match_type])`
3) 각 인수의 설명
lookup_value: 찾으려는 값입니다. 숫자, 텍스트, 논리값 또는 숫자에 대한 셀 참조일 수 있습니다.lookup_array: 값을 찾을 영역입니다. 단일 행 또는 단일 열의 범위여야 합니다.[match_type]: 선택 사항이며, 일치 유형을 지정합니다.0(또는 생략):lookup_value와 정확히 일치하는 첫 번째 값을 찾습니다.lookup_array는 정렬될 필요가 없습니다.1:lookup_value보다 작거나 같은 값 중 가장 큰 값을 찾습니다.lookup_array는 오름차순으로 정렬되어야 합니다.'-1':lookup_value보다 크거나 같은 값 중 가장 작은 값을 찾습니다.lookup_array는 내림차순으로 정렬되어야 합니다.
4) MATCH 함수 예시
다음 표에서 “바나나”가 몇 번째 위치에 있는지 찾아봅시다.
| 과일 |
| ———- |
| 사과 |
| 바나나 |
| 오렌지 |
| 포도 |
=MATCH("바나나", A2:A5, 0)
위 수식은 “바나나”가 `A2:A5` 범위에서 2번째 위치에 있으므로 2를 반환합니다.
2. INDEX 함수 이해
`INDEX` 함수는 지정된 범위 또는 배열 내에서 특정 행과 열의 교차점에 있는 값을 반환합니다. 이는 `MATCH` 함수가 반환하는 위치 값을 활용하여 데이터를 추출할 때 매우 유용합니다.
1) INDEX 함수의 목적
- 주어진 배열 또는 범위에서 특정 행 번호와 열 번호에 해당하는 값을 반환합니다.
- 단일 행 또는 단일 열에서 값을 찾을 수도 있고, 2차원 범위에서 값을 찾을 수도 있습니다.
2) INDEX 함수의 구문
`INDEX` 함수는 두 가지 구문 형태를 가집니다.
배열 형태: `INDEX(array, row_num, [column_num])`참조 형태: `INDEX(reference, row_num, [column_num], [area_num])`
일반적으로 배열 형태가 `INDEX/MATCH` 조합에서 사용됩니다.
3) 각 인수의 설명
array: 값을 반환할 셀 또는 셀 범위입니다.row_num:array내에서 값을 반환할 행의 번호입니다.[column_num]: 선택 사항이며,array내에서 값을 반환할 열의 번호입니다.array가 단일 열인 경우 생략할 수 있습니다.[area_num]: 선택 사항이며,reference가 여러 범위로 구성될 때 어떤 영역에서 값을 가져올지 지정합니다. (일반적인 `INDEX/MATCH` 조합에서는 거의 사용되지 않습니다.)
4) INDEX 함수 예시
다음 표에서 3행 2열에 있는 값을 찾아봅시다.
| 제품명 | 가격 | 재고 |
|---|---|---|
| 키보드 | 50000 | 100 |
| 마우스 | 20000 | 250 |
| 모니터 | 200000 | 50 |
| 웹캠 | 30000 | 120 |
=INDEX(A2:C5, 3, 2)
위 수식은 `A2:C5` 범위에서 3행 2열에 있는 값인 200000을 반환합니다.
3. INDEX와 MATCH 결합
`INDEX`와 `MATCH` 함수를 결합하면 `VLOOKUP`의 단점을 보완하고 훨씬 유연한 데이터 조회를 수행할 수 있습니다. `MATCH` 함수로 원하는 값의 위치(행 또는 열 번호)를 찾고, 그 위치를 `INDEX` 함수에 전달하여 실제 데이터를 추출하는 방식입니다.
1) INDEX/MATCH 조합의 개념
- `MATCH` 함수는
lookup_value가lookup_array에서 몇 번째 위치에 있는지 반환합니다. - `INDEX` 함수는 `MATCH` 함수가 반환한 위치를 기반으로 특정 범위에서 값을 가져옵니다.
- 기본 형태: `INDEX(데이터_범위, MATCH(찾을_값, 찾을_열_범위, 0), [MATCH(찾을_열_이름, 찾을_행_범위, 0)])`
2) 단일 조건 조회 예시 (VLOOKUP 대체)
다음 표에서 제품 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번째 위치에 있는 값인 “마우스”를 반환합니다.
3) 양방향(Two-Way) 조회 예시
다음 표에서 특정 제품과 특정 월의 매출액을 조회해봅시다.
| 제품명 | 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을 반환합니다.
4. INDEX/MATCH의 장점
`INDEX/MATCH` 조합은 `VLOOKUP` 함수에 비해 여러 가지 중요한 장점을 제공합니다.
1) 조회 열의 위치에 구애받지 않음
- `VLOOKUP`은 항상 조회하려는 값이 범위의 가장 왼쪽 열에 있어야 합니다.
- `INDEX/MATCH`는 `MATCH` 함수가 조회 값의 위치만 반환하므로, 조회하려는 열이 데이터 범위의 어느 곳에 있든 상관없이 값을 추출할 수 있습니다. 즉, 왼쪽에 있는 데이터도 조회 가능합니다.
2) 양방향(수평 및 수직) 조회 가능
- `VLOOKUP`은 수직 조회만 가능합니다.
- `INDEX/MATCH`는 행과 열의 `MATCH` 함수를 중첩하여 사용하여 수직 및 수평(양방향) 조회를 동시에 수행할 수 있습니다.
3) 열 삽입/삭제에 강함
- `VLOOKUP`은 조회 범위 내에 새로운 열이 삽입되거나 삭제되면, 세 번째 인수인
col_index_num이 변경되어 수식이 깨질 위험이 있습니다. - `INDEX/MATCH`는 `MATCH` 함수가 열의 이름을 기반으로 위치를 찾으므로, 중간에 열이 추가되거나 삭제되어도 수식에 영향을 주지 않습니다.
4) 대규모 데이터셋에서 성능 우수
- 매우 큰 데이터셋을 다룰 때 `INDEX/MATCH`는 `VLOOKUP`보다 더 효율적인 계산 성능을 보일 수 있습니다. 이는 `INDEX` 함수가 전체 범위가 아닌 특정 셀만 참조하기 때문입니다.
5) 다중 조건 조회에 용이
- 배열 수식(Ctrl+Shift+Enter)과 함께 사용하거나, 추가적인 `MATCH` 함수를 중첩하여 여러 조건을 동시에 만족하는 데이터를 조회할 수 있습니다.
5. 실용적인 예제
`INDEX/MATCH`를 사용하여 실제 시나리오에서 데이터를 조회하는 방법을 더 자세히 살펴봅니다.
1) 왼쪽 조회 (Leftward Lookup)
고객명으로 고객 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`으로는 불가능한 조회입니다.
2) 두 가지 조건으로 조회 (Two-Criteria Lookup)
제품명과 색상 두 가지 조건을 만족하는 제품의 가격을 조회해봅시다.
| 제품 | 색상 | 가격 |
|---|---|---|
| 티셔츠 | 빨강 | 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을 반환합니다.
3) 근사치 일치 (Approximate Match)
`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를 반환합니다.
6. 결론
`INDEX` 함수와 `MATCH` 함수는 엑셀에서 데이터를 조회하고 분석하는 데 있어 매우 강력하고 유연한 도구입니다. 이 두 함수를 함께 사용하면 `VLOOKUP`의 한계를 뛰어넘어 왼쪽 조회, 양방향 조회, 그리고 복잡한 다중 조건 조회까지 다양한 시나리오를 효과적으로 처리할 수 있습니다.
처음에는 구문이 다소 복잡하게 느껴질 수 있지만, 각 함수의 역할과 조합 원리를 이해하고 꾸준히 연습한다면 엑셀 데이터 처리 능력을 크게 향상시킬 수 있을 것입니다. 이 가이드에서 제시된 예제들을 직접 실습해보면서 `INDEX/MATCH` 조합의 진정한 힘을 경험해보시길 바랍니다.