[엑셀 문답] INDEX MATCH 함수로 교차셀 추출하기

하나의 표에서 행 레이블과 열 레이블이 교차하는 셀의 값을 추출하는 수식이 실무에서 자주 필요할 것이다. 이때는 VLOOKUP 함수와 다른 함수의 중첩보다는 INDEX 함수와 MATCH 함수의 조합을 활용하는 것이 실무에서 활용도가 높다.

아래의 질문은 이런 교차값을 추출하는 수식을 묻는 문제이다. 함께 해결해 보자.

** 님의 질문입니다

제목 : 고1 이고 횟수가 4회인 값을 얻고 싶어요.
내용 :
.....

[그림1] 행과 열의 교차값을 얻을 원본 테이블

[그림1] 의 [표1] 을 이용하여 아래 [그림2] 의 튜터링 영역 함수를 설정하고 싶습니다. [표2] 각 학생의 학년과 횟수를 [그림1] 에서 찾고 이 행과 열이 교차하는 셀의 값을 얻는 것입니다.

[그림2] 표1에 사용된 행과 열 레이블로 교차셀 추출

함수 설정 방법을 부탁합니다~ 한 칸씩 따로 설정하는거 말고 제일 윗 칸만 함수 설정 후 채우기 핸들로 드래그 할 수 있도록 부탁드려요.
.....

마늘빵의 답변입니다

.....
먼저 사용할 함수를 짐작해 봅니다. 행과 열 머리글에서 원하는 값을 찾아야 교차셀을 찾아갈 수 있습니다. 이 값을 찾는 함수로 MATCH 함수를 사용합니다. 찾고자 하는 값을 전달된 배열에서 찾아 그 위치를 숫자로 반환하는 함수입니다.

[컴활 2급] 값을 찾아 그 위치를 숫자로, 엑셀 MATCH 함수 :
http://secstart.tistory.com/547

다음은 이렇게 행과 열 머리글을 찾았다면 그 교차셀을 찾아갑니다. 전달 범위에서 행과 열의 상대 위치( 숫자 )로 그 셀의 값을 얻는 함수인 INDEX 함수를 사용해 봅니다.

[엑셀 모바일] INDEX 함수 행 열 주소로 셀 참조하기 - 기본 형식 :
http://secstart.tistory.com/750

이 두 함수를 중첩하여 사용합니다. INDEX + MATCH 함수의 조합은 엑셀에서 표를 참조하는 환상의 조합이라 불립니다.
MATCH 함수가 INDEX 함수의 행 번호, 열 번호 인수로 사용되어 해당 행과 열 위치의 셀 값을 추출하는 것입니다.

위 질문은 [표1] 의 행 머리글이 숫자로 설정되어 있습니다. 따라서, INDEX 함수에서 이 행을 바로 사용하는 수식도 좋지만 좀 더 넓은 방법으로 INDEX + MATCH 함수식을 작성해 봅니다.

[그림3] 교차셀 값을 추출하는 INDEX MATCH 함수식

강린 학생의 학년과 횟수로 셀 값을 얻어봅니다. D11 셀에 다음의 수식을 입력합니다.

=INDEX( B$3:H$7, MATCH( C11, A$3:A$7, 0 ), MATCH( B11, B$2:H$2, 0 ) )

함수식을 풀어 볼까요?

1) INDEX( B3:H7, 행 번호, 열 번호 )
이것이 위 함수식의 큰 모양입니다. B3:H7 의 행/열 머리글을 제외한 테이블 값만을 범위로 선택하고 행 번호와 열 번호로 해당 셀 값을 얻는 것입니다.

2) MATCH( C11, A3:A7, 0 )
이 함수식의 결과는 INDEX 함수에 행 번호로 전달됩니다.

C11 의 횟수를 A3:A7 의 범위에서 찾습니다. 세 번째 인수가 0 이기에 정확하게 일치하는 값을 찾습니다. 값을 찾아 범위에서의 상대 위치( 숫자 )를 반환합니다. 이것은 결국 찾고자 하는 행 머리글의 행 번호가 되는 것입니다.

횟수 4 를 찾고 이는 범위에서 5 번째 값이기에 5 가 결과로 반환되어 INDEX 함수의 행 번호로 사용됩니다.

3) MATCH( B11, B2:H2, 0 )
이번에는 INDEX 함수에 전달된 열 번호를 구합니다.

B11 의 '고1' 를 B2:H2 의 범위에서 찾고 그에 해당하는 결과 5 가 INDEX 함수의 열 번호로 사용됩니다.

4) 다시 1) 번으로 돌아가 MATCH 함수로 구해진 행과 열 번호를 반영하면 INDEX( B3:H7, 5, 5 ) 이 됩니다.
B3:H7 의 범위에서 상대 위치, 5행 5열의 셀 값을 얻는 것입니다. 96,000 이 결과로 반환됩니다.

추가로 질문에서 처럼 D11 에 함수식을 입력하고 채우기 핸들을 사용하려면 단계 설명에서 처럼 모두 상대 참조를 사용하여서는 안됩니다. 전체 함수식 처럼 [표1] 의 위치는 행을 절대 참조하거나 행 열 모두를 절대 참조하여야 합니다.

이렇게 하면 D11 에서 채우기 핸들을 사용하여 아래쪽으로 수식 복사하여도 [표1] 의 참조 위치는 변하지 않고 [표2] 각 학생의 '학년' 과 '횟수' 를 참조하는 셀 주소는 상대 참조 되었기에 변경됩니다.
.....

이렇게 참조의 방법을 달리하면 원하는 수식 복사가 가능하다.
이것이 INDEX 함수와 MATCH 함수의 조합이고 행과 열의 교차 셀을 얻을 때 자주 사용되는 함수식이다.

[엑셀 활용] INDEX + MATCH 함수로 VLOOKUP 대체하기 :
http://secstart.tistory.com/773
[엑셀 활용] INDEX + MATCH 함수의 장점 배우기 :
http://secstart.tistory.com/774

마늘빵.