엑셀 ~ INDEX & MATCH 의 모든 것.

 
 
안녕하세요, 엑셀을 처음 배우는 학생 여러분! 오늘은 엑셀의 강력한 기능 중 하나인 INDEX 함수와 MATCH 함수, 그리고 이 둘을 조합해서 사용하는 방법에 대해 자세히 알아보겠습니다. 이 기능들은 처음에는 조금 어렵게 느껴질 수 있지만, 차근차근 배워나가면 여러분의 엑셀 실력을 한 단계 높여줄 수 있는 매우 유용한 도구입니다.

1. INDEX 함수 이해하기

INDEX 함수는 특정 범위 내에서 위치를 기반으로 값을 찾아주는 함수입니다. 쉽게 말해, 엑셀 표에서 "몇 번째 행, 몇 번째 열"에 있는 값을 가져오는 기능을 합니다.

INDEX 함수의 기본 구조

=INDEX(array, row_num, [column_num])

• array: 값을 찾을 범위
• row_num: 찾고자 하는 값의 행 번호
• column_num: 찾고자 하는 값의 열 번호 (선택사항)

INDEX 함수 예시

학생들의 성적 데이터가 있다고 가정해봅시다:

이름	국어	영어	수학
김철수	80	85	90
이영희	90	95	85
박민수	85	80	95

 

이 데이터에서 INDEX 함수를 사용해 특정 값을 찾아보겠습니다.

=INDEX(B2:D4, 2, 3)

이 함수는 B2:D4 범위에서 2번째 행, 3번째 열의 값을 가져옵니다. 결과는 85가 됩니다 (이영희의 수학 점수).

2. MATCH 함수 이해하기

MATCH 함수는 특정 값이 범위 내에서 몇 번째 위치에 있는지를 찾아주는 함수입니다. 이 함수는 "찾고자 하는 값이 어디 있니?"라고 물어보는 것과 같습니다.

MATCH 함수의 기본 구조

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: 찾고자 하는 값
• lookup_array: 값을 찾을 범위
• match_type: 찾는 방식 (0: 정확히 일치, 1: 같거나 작은 값 중 가장 큰 값, -1: 같거나 큰 값 중 가장 작은 값)

MATCH 함수 예시

위의 성적 데이터를 다시 사용해보겠습니다.

=MATCH("이영희", A2:A4, 0)

이 함수는 A2:A4 범위에서 "이영희"라는 이름이 몇 번째에 있는지 찾습니다. 결과는 2가 됩니다 (이영희는 2번째 행에 있습니다).

반응형

3. INDEX와 MATCH 함수 조합하기

이제 INDEX와 MATCH 함수를 조합해서 사용하는 방법을 알아보겠습니다. 이 조합은 VLOOKUP 함수보다 더 유연하고 강력한 기능을 제공합니다.

INDEX-MATCH 조합의 기본 구조

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• return_range: 결과를 가져올 열 또는 행
• lookup_value: 찾고자 하는 값
• lookup_range: 찾을 값이 있는 열 또는 행

INDEX-MATCH 조합 예시

다음과 같은 직원 정보 테이블이 있다고 가정해봅시다:

사원번호	이름	부서	연봉
1001	김철수	영업	5000
1002	이영희	마케팅	5500
1003	박민수	개발	6000

 

이제 사원번호를 입력하면 해당 직원의 연봉을 찾는 함수를 만들어보겠습니다.

=INDEX(D2:D4, MATCH(1002, A2:A4, 0))

이 함수는 다음과 같이 작동합니다:

1. MATCH 함수가 A2:A4 범위에서 1002라는 사원번호의 위치를 찾습니다 (결과: 2).
2. INDEX 함수가 D2:D4 범위에서 2번째 값을 가져옵니다.

결과는 5500이 됩니다 (이영희의 연봉).

4. INDEX-MATCH의 장점

INDEX-MATCH 조합은 VLOOKUP 함수에 비해 여러 가지 장점이 있습니다:

1. 열 순서에 구애받지 않음: VLOOKUP은 항상 왼쪽에서 오른쪽으로 찾지만, INDEX-MATCH는 어느 방향으로든 찾을 수 있습니다.
2. 성능이 더 좋음: 대량의 데이터를 다룰 때 INDEX-MATCH가 더 빠르게 작동합니다.
3. 동적 참조 가능: 열 번호 대신 다른 함수나 셀 참조를 사용할 수 있어 더 유연합니다.
4. 여러 조건 검색 가능: 복수의 MATCH 함수를 사용해 여러 조건을 만족하는 값을 찾을 수 있습니다.

5. 실전 예제: 학생 성적 관리 시스템

이제 좀 더 복잡한 예제를 통해 INDEX-MATCH의 활용법을 알아보겠습니다. 다음과 같은 학생 성적 데이터가 있다고 가정해봅시다:

학번	이름	학년	반	국어	영어	수학
2501	김철수	2	A	80	85	90
2502	이영희	2	B	90	95	85
2503	박민수	2	A	85	80	95
2504	최지은	2	C	95	90	80
2505	정동훈	2	B	75	85	90

 

예제 1: 학번으로 특정 과목 점수 찾기

학번을 입력하면 해당 학생의 영어 점수를 찾는 함수를 만들어보겠습니다.

=INDEX(F2:F6, MATCH(2503, A2:A6, 0))

이 함수는 학번 2503인 학생(박민수)의 영어 점수인 80을 반환합니다.

예제 2: 이름과 과목으로 점수 찾기

이번에는 학생 이름과 과목명을 입력하면 해당 점수를 찾는 함수를 만들어보겠습니다. 이를 위해 두 개의 MATCH 함수를 사용할 것입니다.
먼저, 과목명과 열 번호를 매칭하는 별도의 표를 만듭니다:

과목열	번호
국어	5
영어	6
수학	7

 

이제 함수를 작성해보겠습니다:

=INDEX($E$2:$G$6, MATCH("이영희", $B$2:$B$6, 0), MATCH(H1, $A$9:$A$11, 0))

여기서 H1 셀에는 찾고자 하는 과목명을 입력합니다.
이 함수는 다음과 같이 작동합니다:

1. 첫 번째 MATCH 함수가 B2:B6 범위에서 "이영희"의 위치를 찾습니다.
2. 두 번째 MATCH 함수가 A9:A11 범위에서 H1에 입력된 과목의 열 번호를 찾습니다.
3. INDEX 함수가 E2:G6 범위에서 위에서 찾은 행과 열의 교차점에 있는 값을 반환합니다.

예를 들어, H1에 "영어"를 입력하면 이 함수는 95를 반환합니다 (이영희의 영어 점수).

예제 3: 최고 점수 학생 찾기

이번에는 특정 과목에서 가장 높은 점수를 받은 학생의 이름을 찾는 함수를 만들어보겠습니다. 이를 위해 MAX 함수와 INDEX-MATCH를 조합하여 사용할 것입니다.

=INDEX($B$2:$B$6, MATCH(MAX(INDEX($E$2:$G$6, 0, MATCH(H1, $A$9:$A$11, 0))), INDEX($E$2:$G$6, 0, MATCH(H1, $A$9:$A$11, 0)), 0))

이 함수는 다음과 같이 작동합니다:

1. 내부 MATCH 함수가 입력된 과목의 열 번호를 찾습니다.
2. 내부 INDEX 함수가 해당 과목의 모든 점수를 선택합니다.
3. MAX 함수가 그 중 최고 점수를 찾습니다.
4. 외부 MATCH 함수가 최고 점수의 위치를 찾습니다.
5. 외부 INDEX 함수가 그 위치에 해당하는 학생의 이름을 반환합니다.

예를 들어, H1에 "수학"을 입력하면 이 함수는 "박민수"를 반환합니다 (수학 최고 점수 95점을 받은 학생).

728x90

6. INDEX-MATCH 활용 팁

- 여러 열 검색하기:
여러 열을 동시에 검색하려면 MATCH 함수 대신 SMALL과 IF를 조합하여 사용할 수 있습니다.

=INDEX(return_range, SMALL(IF((criteria1_range=criteria1)*(criteria2_range=criteria2), ROW(criteria1_range)-ROW(first_cell)+1), 1))

 
- 대소문자 구분 없이 검색하기:
MATCH 함수는 기본적으로 대소문자를 구분합니다. 대소문자를 구분하지 않고 검색하려면 EXACT 함수 대신 LOWER 함수를 사용할 수 있습니다.

=INDEX(return_range, MATCH(TRUE, EXACT(LOWER(lookup_value), LOWER(lookup_range)), 0))

 
- 부분 일치 검색하기
MATCH 함수는 기본적으로 완전 일치만을 찾습니다. 부분 일치를 찾으려면 SEARCH 함수를 활용할 수 있습니다.

=INDEX(return_range, MATCH(TRUE, ISNUMBER(SEARCH(lookup_value, lookup_range)), 0))

 
- 오류 처리하기
검색 결과가 없을 때 오류 대신 특정 메시지를 표시하고 싶다면 IFERROR 함수를 사용할 수 있습니다.

=IFERROR(INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), "찾을 수 없음")

 

7. INDEX-MATCH 연습 문제

이제 여러분의 이해도를 확인하기 위한 몇 가지 연습 문제를 풀어보겠습니다.

문제 1: 직원 정보 찾기

다음과 같은 직원 정보 테이블이 있습니다:

사원번호	이름	부서	입사일	연봉
1001	김철수	영업	2020-03-01	5000
1002	이영희	마케팅	2019-05-15	5500
1003	박민수	개발	2021-01-10	6000
1004	최지은	인사	2018-09-20	5800
1005	정동훈	영업	2022-04-05	4800

 

문제: 사원번호를 입력하면 해당 직원의 부서와 연봉을 동시에 표시하는 함수를 만드세요.
해답:

=INDEX(C2:E6, MATCH(G1, A2:A6, 0), {1,3})

여기서 G1 셀에는 찾고자 하는 사원번호를 입력합니다.
설명:

1. MATCH 함수가 입력된 사원번호의 위치를 찾습니다.
2. INDEX 함수가 해당 위치의 부서(1번째 열)와 연봉(3번째 열)을 반환합니다.
3. {1,3}은 부서와 연봉 열을 지정합니다.

결과: 예를 들어, G1에 1003을 입력하면 {"개발", 6000}이 반환됩니다.

문제 2: 최고 연봉 직원 찾기

위의 직원 정보 테이블을 사용하여, 각 부서별로 가장 높은 연봉을 받는 직원의 이름을 찾는 함수를 만드세요.
해답:

=INDEX($B$2:$B$6, MATCH(MAX(IF($C$2:$C$6=H1, $E$2:$E$6)), IF($C$2:$C$6=H1, $E$2:$E$6, ""), 0))

여기서 H1 셀에는 찾고자 하는 부서명을 입력합니다.
설명:

1. IF 함수가 입력된 부서와 일치하는 행만 선택합니다.
2. MAX 함수가 해당 부서의 최고 연봉을 찾습니다.
3. MATCH 함수가 최고 연봉의 위치를 찾습니다.
4. INDEX 함수가 그 위치에 해당하는 직원의 이름을 반환합니다.

결과: 예를 들어, H1에 "영업"을 입력하면 "김철수"가 반환됩니다.

문제 3: 입사일 기준 정보 찾기

특정 날짜를 기준으로, 그 이전에 입사한 직원 중 가장 최근에 입사한 직원의 정보(이름, 부서, 연봉)를 찾는 함수를 만드세요.
해답:

=INDEX($B$2:$E$6, MATCH(MAX(IF($D$2:$D$6<=H1, $D$2:$D$6)), $D$2:$D$6, 0), {1,2,4})

여기서 H1 셀에는 기준 날짜를 입력합니다 (예: 2021-01-01).
설명:

1. IF 함수가 기준 날짜 이전에 입사한 직원만 선택합니다.
2. MAX 함수가 그 중 가장 최근 입사일을 찾습니다.
3. MATCH 함수가 해당 입사일의 위치를 찾습니다.
4. INDEX 함수가 그 위치에 해당하는 직원의 이름, 부서, 연봉을 반환합니다.

결과: 예를 들어, H1에 2021-01-01을 입력하면 {"이영희", "마케팅", 5500}이 반환됩니다.

SMALL

8. INDEX-MATCH의 고급 활용

이제 INDEX-MATCH를 더욱 고급스럽게 활용하는 방법을 알아보겠습니다.

8.1 다중 조건 검색

때로는 두 개 이상의 조건을 만족하는 데이터를 찾아야 할 때가 있습니다. 이럴 때는 여러 개의 MATCH 함수를 조합하여 사용할 수 있습니다.
예를 들어, 부서와 입사년도를 기준으로 직원을 검색하는 함수를 만들어보겠습니다:

=INDEX($B$2:$B$6, MATCH(1, (($C$2:$C$6=H1) * (YEAR($D$2:$D$6)=H2)), 0))

여기서 H1에는 부서명, H2에는 입사년도를 입력합니다.
설명:

1. ($C$2:$C$6=H1)은 부서가 일치하는 행에 TRUE를 반환합니다.
2. (YEAR($D$2:$D$6)=H2)는 입사년도가 일치하는 행에 TRUE를 반환합니다.
3. 두 조건을 곱하면, 두 조건을 모두 만족하는 행에만 1이 남습니다.
4. MATCH 함수가 1의 위치를 찾습니다.
5. INDEX 함수가 해당 위치의 직원 이름을 반환합니다.

8.2 동적 범위와 함께 사용하기

데이터의 범위가 계속 변할 수 있는 경우, OFFSET 함수나 테이블 참조를 활용하여 동적 범위를 만들 수 있습니다.
OFFSET 함수를 사용한 예:

=INDEX(OFFSET($A$1, 1, 0, COUNTA($A:$A)-1, 5), MATCH(H1, OFFSET($A$1, 1, 0, COUNTA($A:$A)-1, 1), 0), 3)

이 함수는 A열의 데이터 개수에 따라 자동으로 범위를 조정합니다.
테이블 참조를 사용한 예 (테이블 이름이 "직원정보"라고 가정):

=INDEX(직원정보[연봉], MATCH(H1, 직원정보[사원번호], 0))

이 방식은 테이블에 행이 추가되거나 삭제되어도 자동으로 범위가 조정됩니다.

8.3 대량 데이터에서의 성능 최적화

대량의 데이터를 다룰 때는 INDEX-MATCH의 성능을 최적화하는 것이 중요합니다. 다음과 같은 방법을 사용할 수 있습니다:

1. 정렬된 데이터 활용: MATCH 함수의 세 번째 인수를 1 또는 -1로 설정하여 이진 검색을 활용합니다.

=INDEX($B$2:$B$6, MATCH(H1, $A$2:$A$6, 1))

 

이 방법은 A2:A6 범위가 오름차순으로 정렬되어 있을 때 사용할 수 있습니다.

1. VLOOKUP과 병행 사용: 때로는 VLOOKUP이 더 빠를 수 있으므로, 상황에 따라 적절히 선택합니다.
2. 배열 수식 대신 도우미 열 사용: 복잡한 배열 수식 대신, 중간 계산을 위한 도우미 열을 만들어 사용합니다.

8.4 오류 처리와 사용자 친화적 결과

INDEX-MATCH 함수가 오류를 반환할 경우를 대비하여, IFERROR 함수를 함께 사용할 수 있습니다:

=IFERROR(INDEX($B$2:$B$6, MATCH(H1, $A$2:$A$6, 0)), "해당 직원을 찾을 수 없습니다.")

이렇게 하면 검색 결과가 없을 때 사용자 친화적인 메시지를 표시할 수 있습니다.

9. INDEX-MATCH vs VLOOKUP

많은 엑셀 사용자들이 VLOOKUP 함수에 익숙해져 있지만, INDEX-MATCH 조합이 여러 면에서 더 유용할 수 있습니다. 두 방식을 비교해보겠습니다:

1. 유연성:
   • VLOOKUP: 항상 왼쪽에서 오른쪽으로만 검색 가능
   • INDEX-MATCH: 어느 방향으로든 검색 가능
2. 성능:
   • VLOOKUP: 큰 데이터셋에서 상대적으로 느림
   • INDEX-MATCH: 대체로 더 빠름, 특히 큰 데이터셋에서 효과적
3. 오류 가능성:
   • VLOOKUP: 열이 삽입되거나 삭제되면 오류 발생 가능성 높음
   • INDEX-MATCH: 열 변경에 영향을 받지 않음
4. 다중 조건:
   • VLOOKUP: 기본적으로 단일 조건만 가능
   • INDEX-MATCH: 여러 MATCH 함수를 조합하여 다중 조건 검색 가능
5. 근사값 찾기:
   • VLOOKUP: 네 번째 인수로 TRUE를 사용하여 근사값 찾기 가능
   • INDEX-MATCH: MATCH 함수의 세 번째 인수로 1 또는 -1을 사용하여 근사값 찾기 가능
6. 가독성:
   • VLOOKUP: 상대적으로 이해하기 쉬움
   • INDEX-MATCH: 처음에는 복잡해 보일 수 있지만, 익숙해지면 더 직관적

결론적으로, INDEX-MATCH는 VLOOKUP보다 더 강력하고 유연한 도구입니다. 초기 학습 곡선이 조금 가파를 수 있지만, 숙달되면 훨씬 더 효과적인 데이터 분석이 가능해집니다.

10. 실무에서의 INDEX-MATCH 활용 사례

INDEX-MATCH는 다양한 실무 상황에서 유용하게 활용될 수 있습니다. 몇 가지 구체적인 사례를 살펴보겠습니다.

10.1 재무 분석

재무제표 분석에서 INDEX-MATCH를 활용할 수 있습니다. 예를 들어, 여러 해의 재무제표에서 특정 항목의 값을 추출하는 경우:

=INDEX(재무데이터!$B$2:$F$100, MATCH("매출액", 재무데이터!$A$2:$A$100, 0), MATCH(연도, 재무데이터!$B$1:$F$1, 0))

이 함수는 "매출액" 항목의 특정 연도 값을 찾아냅니다.

10.2 인사 관리

직원 데이터베이스에서 특정 조건을 만족하는 직원을 찾는 데 활용할 수 있습니다:

=INDEX(직원DB!$B$2:$B$1000, MATCH(1, (직원DB!$C$2:$C$1000="영업부")*(직원DB!$D$2:$D$1000>5), 0))

이 함수는 영업부 소속이면서 근속년수가 5년 이상인 첫 번째 직원의 이름을 찾습니다.

10.3 재고 관리

제품 코드와 창고 위치를 기반으로 재고량을 찾는 데 사용할 수 있습니다:

=INDEX(재고DB!$D$2:$D$1000, MATCH(1, (재고DB!$B$2:$B$1000=제품코드)*(재고DB!$C$2:$C$1000=창고위치), 0))

이 함수는 특정 제품 코드와 창고 위치에 해당하는 재고량을 반환합니다.

10.4 고객 데이터 분석

고객 ID를 기반으로 최근 구매 날짜와 금액을 동시에 찾을 수 있습니다:

=INDEX(고객DB!$C$2:$D$10000, MATCH(고객ID, 고객DB!$A$2:$A$10000, 0), {1,2})

이 함수는 특정 고객 ID에 해당하는 최근 구매 날짜와 금액을 배열로 반환합니다.

10.5 프로젝트 관리

프로젝트 코드를 입력하면 해당 프로젝트의 담당자와 마감일을 찾는 함수를 만들 수 있습니다:

=INDEX(프로젝트DB!$C$2:$D$100, MATCH(프로젝트코드, 프로젝트DB!$A$2:$A$100, 0), {1,2})

이 함수는 입력된 프로젝트 코드에 해당하는 담당자와 마감일을 반환합니다.

11. INDEX-MATCH 함수의 한계와 대안

INDEX-MATCH 함수는 강력하지만, 몇 가지 한계가 있습니다:

1. 복잡성: 초보자에게는 이해하기 어려울 수 있습니다.
2. 다중 반환: 기본적으로 단일 값만 반환합니다. 여러 값을 반환하려면 추가적인 기법이 필요합니다.
3. 대량 데이터: 매우 큰 데이터셋에서는 성능이 저하될 수 있습니다.

이러한 한계를 극복하기 위한 대안으로는:

1. XLOOKUP 함수: Excel 2021 이상 버전에서 사용 가능한 새로운 함수로, INDEX-MATCH의 많은 기능을 더 간단하게 구현할 수 있습니다.
2.  
3. Power Query: 대량의 데이터를 처리할 때 더 효과적인 도구입니다.
4. VBA: 매우 복잡한 검색 로직이 필요한 경우, VBA를 사용하여 사용자 정의 함수를 만들 수 있습니다.

=XLOOKUP(검색값, 검색범위, 반환범위, [못찾을때값], [일치모드], [검색모드])

12. INDEX-MATCH 학습 전략

INDEX-MATCH를 효과적으로 학습하기 위한 전략을 제시하겠습니다:

1. 기초부터 시작하기: INDEX와 MATCH 함수를 각각 독립적으로 이해하고 연습합니다.
2. 단계적 접근: 간단한 예제부터 시작하여 점차 복잡한 사용 사례로 나아갑니다.
3. 실제 데이터 활용: 자신의 업무나 일상생활과 관련된 실제 데이터로 연습합니다.
4. 오류 분석: 의도적으로 오류를 만들어보고, 그 원인을 분석합니다.
5. VLOOKUP과 비교: VLOOKUP으로 할 수 있는 작업을 INDEX-MATCH로 변환해보며 차이점을 이해합니다.
6. 온라인 리소스 활용: 다양한 튜토리얼과 포럼을 활용하여 추가 예제와 팁을 학습합니다.
7. 정기적인 연습: 배운 내용을 잊지 않도록 정기적으로 연습합니다.

13. 결론

INDEX-MATCH 함수 조합은 엑셀에서 데이터를 검색하고 분석하는 강력한 도구입니다. 초기에는 복잡해 보일 수 있지만, 숙달되면 VLOOKUP보다 더 유연하고 효과적인 솔루션을 제공합니다.
이 기능을 마스터하면:

1. 더 복잡한 데이터 분석 작업을 수행할 수 있습니다.
2. 스프레드시트의 성능을 개선할 수 있습니다.
3. 데이터 처리 오류를 줄일 수 있습니다.
4. 더 동적이고 유연한 보고서를 만들 수 있습니다.

INDEX-MATCH는 단순한 함수 이상의 의미를 갖습니다. 이는 데이터를 바라보는 새로운 시각과 접근 방식을 제공합니다. 지속적인 학습과 연습을 통해 이 강력한 도구를 마스터하면, 여러분의 엑셀 실력은 한 단계 더 도약할 것입니다.
엑셀을 사용하는 여정에서 INDEX-MATCH는 중요한 이정표가 될 것입니다. 이 기능을 통해 데이터를 더 깊이 이해하고, 더 효과적으로 분석하며, 궁극적으로 더 나은 의사결정을 내릴 수 있게 될 것입니다. 끊임없는 호기심과 학습 의지로 INDEX-MATCH의 세계를 탐험해 나가시기 바랍니다!