엑셀에서 INDEX, MATCH 함수 활용하기

엑셀에서 INDEX, MATCH 함수 활용하기

엑셀은 데이터를 다루는 데 매우 유용한 프로그램이에요. 엑셀에는 다양한 함수들이 있는데, 그 중에서 특히 INDEX와 MATCH 함수는 데이터를 검색하고 참조하는 데 강력한 도구라고 할 수 있어요. INDEX와 MATCH 함수를 조합하면, VLOOKUP이나 HLOOKUP 함수보다 더 유연하고 효율적인 방식으로 데이터를 다룰 수 있어요.

INDEX 함수란?

INDEX 함수란, 주어진 배열이나 범위에서 원하는 위치의 값을 반환하는 함수입니다. INDEX 함수의 기본 구문은 다음과 같습니다.

=INDEX(배열, 행_번호, [열_번호])

• 배열 : 값을 찾을 배열이나 범위입니다.
• 행_번호 : 배열이나 범위에서 값을 찾을 행의 위치입니다.
• 열_번호 : 배열이나 범위에서 값을 찾을 열의 위치입니다. 생략하면 1로 간주됩니다.

예를 들어, 다음과 같은 데이터가 있다고 가정해 봅시다.

이름성별나이직업

김철수	남	25	회사원
박영희	여	23	간호사
이민수	남	27	선생님
최지연	여	24	디자이너

이 데이터에서 B2:D4 범위를 배열로 지정하고, 행_번호를 2, 열_번호를 3으로 입력하면, 다음과 같은 결과를 얻을 수 있습니다.

=INDEX(B2:D4, 2, 3)

결과

23

즉, B2:D4 범위에서 2번째 행과 3번째 열에 해당하는 값인 23을 반환합니다.

MATCH 함수란?

MATCH 함수란, 주어진 배열이나 범위에서 원하는 값을 찾아서 그 위치를 반환하는 함수입니다. MATCH 함수의 기본 구문은 다음과 같습니다.

=MATCH(찾을_값, 찾을_범위, [일치유형])

• 찾을_값 : 찾고자 하는 값입니다.
• 찾을_범위 : 값을 찾을 배열이나 범위입니다. 일차원 배열이어야 합니다.
• 일치유형 : 찾을 값과 정확히 일치하는지 아니면 근사치로 찾을지를 결정하는 인수입니다. 생략하면 1로 간주됩니다. 다음과 같은 값들을 사용할 수 있습니다.
  • 1 : 찾을 값보다 작거나 같은 값 중에서 가장 큰 값을 찾습니다. 찾을 범위는 오름차순으로 정렬되어 있어야 합니다.
  • 0 : 찾을 값과 정확히 일치하는 값을 찾습니다. 찾을 범위는 정렬되어 있지 않아도 됩니다.
  • -1 : 찾을 값보다 크거나 같은 값 중에서 가장 작은 값을 찾습니다. 찾을 범위는 내림차순으로 정렬되어 있어야 합니다.

예를 들어, 앞서 사용한 데이터에서 A2:A5 범위를 찾을_범위로 지정하고, 찾을_값을 “이민수”, 일치유형을 0으로 입력하면, 다음과 같은 결과를 얻을 수 있습니다.

=MATCH("이민수", A2:A5, 0)

결과

3

즉, A2:A5 범위에서 "이민수"와 정확히 일치하는 값이 3번째 위치에 있음을 반환합니다.

INDEX와 MATCH 함수의 조합

INDEX와 MATCH 함수를 조합하면, VLOOKUP이나 HLOOKUP 함수보다 더 유연하고 효율적인 방식으로 데이터를 검색하고 참조할 수 있습니다. VLOOKUP이나 HLOOKUP 함수는 다음과 같은 한계점들이 있습니다.

• VLOOKUP 함수는 왼쪽에서 오른쪽으로만 검색할 수 있습니다. 즉, 찾을 값이 가장 왼쪽 열에 있어야 하고, 반환할 값이 그 오른쪽 열에 있어야 합니다.
• HLOOKUP 함수는 위에서 아래로만 검색할 수 있습니다. 즉, 찾을 값이 가장 위쪽 행에 있어야 하고, 반환할 값이 그 아래쪽 행에 있어야 합니다.
• VLOOKUP이나 HLOOKUP 함수는 열_번호나 행_번호를 고정된 숫자로 입력해야 합니다. 즉, 데이터의 구조가 변경되면 열_번호나 행_번호도 수정해야 합니다.

INDEX와 MATCH 함수를 조합하면, 이러한 한계점들을 극복할 수 있습니다. INDEX와 MATCH 함수의 조합은 다음과 같은 구문으로 사용할 수 있습니다.

=INDEX(반환할_범위, MATCH(찾을_값, 찾을_범위, 일치유형))

• 반환할_범위 : 값을 반환할 배열이나 범위입니다. INDEX 함수의 배열 인수와 동일합니다.
• 찾을_값 : 찾고자 하는 값입니다. MATCH 함수의 찾을_값 인수와 동일합니다.
• 찾을_범위 : 값을 찾을 배열이나 범위입니다. MATCH 함수의 찾을_범위 인수와 동일합니다. 일차원 배열이어야 합니다.
• 일치유형 : 찾을 값과 정확히 일치하는지 아니면 근사치로 찾을지를 결정하는 인수입니다. MATCH 함수의 일치유형 인수와 동일합니다.

예를 들어, 앞서 사용한 데이터에서 이름을 입력하면 직업을 반환하는 수식을 만들고 싶다면, 다음과 같은 방법으로 INDEX와 MATCH 함수를 조합할 수 있습니다.

=INDEX(D2:D5, MATCH("박영희", A2:A5, 0))

결과

간호사

즉, D2:D5 범위에서 A2:A5 범위에서 "박영희"와 정확히 일치하는 값의 위치에 해당하는 값을 반환합니다.

INDEX와 MATCH 함수의 조합은 다음과 같은 장점들이 있습니다.

• 반환할 값과 찾을 값이 같은 행이나 열에 있지 않아도 됩니다. 즉, 어느 방향으로든 검색할 수 있습니다.
• 열_번호나 행_번호를 고정된 숫자로 입력하지 않고, 동적으로 계산할 수 있습니다. 즉, 데이터의 구조가 변경되어도 수식을 수정하지 않아도 됩니다.
• 여러 개의 조건을 사용해서 데이터를 검색하고 참조할 수 있습니다. 즉, AND나 OR 연산자를 사용해서 복잡한 조건들을 만들 수 있습니다.

 

INDEX와 MATCH 함수의 활용 예시

• 네 번째 질문에 대한 답을 찾기 위해서는 다음과 같은 방법으로 피벗테이블을 만들 수 있습니다.

1. 데이터 소스에서 A1:G101 범위를 선택하고, 삽입 탭에서 피벗테이블 버튼을 클릭합니다.
2. 새로운 워크시트에 피벗테이블을 만들도록 선택합니다.
3. 필드 목록 창에서 제품명 필드를 행 영역에 넣고, 지역명 필드를 열 영역에 넣습니다.
4. 필드 목록 창에서 판매량 필드와 매출액 필드를 값 영역에 넣습니다.
5. 분석 탭에서 필드 설정 버튼을 클릭하고, 값 영역의 집계 함수를 합계로 선택합니다.
6. 디자인 탭에서 보고서 레이아웃 버튼을 클릭하고, 표 형식으로 선택합니다.

• 만들어진 피벗테이블은 다음과 같습니다.

제품명부산대구대전광주서울합계

A	200 / 400,000	100 / 200,000	100 / 200,000	200 / 400,000	400 / 800,000	1,000 / 2,000,000
B	300 / 600,000	200 / 400,000	100 / 200,000	100 / 200,000	500 / 1,000,000	1,200 / 2,400,000
C	100 / 300,000	100 / 300,000	200 / 600,000	200 / 600,000	200 / 600,000	800 / 2,400,000
D	400 / 800,000	300 / 600,000	300 / 600,000	100 / 200,000	300 / 600,000	1,400 / 2,800,000
E	200 / 400,000	100 / 200,000	100 / 200,000	100 / 200,000	100 / 200,000	600 / 1,2000
합계	1,200 / 2,5000	800 / 1,7000	800 / 1,8000	700 / 1,6000	1,500/ 3.2000	5.00/ 10.00

• 피벗테이블에서 볼 수 있듯이,
  • 제품별로 판매량과 매출액은 다음과 같습니다.
    • 제품 A는 총 1.00개의 판매량과 총 2.00원의 매출액을 기록했습니다.
    • 제품 B는 총 1.20개의 판매량과 총 2.40원의 매출액을 기록했습니다.
    • 제품 C는 총 0.80개의 판매량과 총 2.40원의 매출액을 기록했습니다.
    • 제품 D는 총 1.40개의 판매량과 총 2.80원의 매출액을 기록했습니다.
    • 제품 E는 총 0.60개의 판매량과 총 1.20원의 매출액을 기록했습니다.
  • 지역별로 판매량과 매출액은 다음과 같습니다.
    • 부산 지역은 총 1.20개의 판매량과 총 2.50원의 매출액을 기록했습니다.
    • 대구 지역은 총 0.80개의 판매량과 총 1.70원의 매출액을 기록했습니다.
    • 대전 지역은 총 0.80개의 판매량과 총 1.80원의 매출액을 기록했습니다.
    • 광주 지역은 총 0.70개의 판매량과 총 1.60원의 매출액을 기록했습니다.
    • 서울 지역은 총 1.50개의 판매량과 총 3.20원의 매출액을 기록했습니다.
• 피벗테이블을 사용하면, 복잡하고 방대한 데이터를 쉽게 정리하고, 원하는 정보를 빠르게 찾아낼 수 있습니다. 피벗테이블의 장점과 구성 요소, 만드는 방법, 활용 예시에 대해 알아보았습니다. 피벗테이블을 잘 활용하면, 엑셀에서 데이터 분석을 더 효과적으로 할 수 있습니다.
• 이상으로 엑셀의 피벗테이블을 이용한 데이터 분석에 대한 블로그 글을 마칩니다. 도움이 되셨다면 좋아요와 댓글 부탁드립니다. 감사합니다. 😊