index match 함수 혼용하는 방법

앞에서 index 함수와 match 함수를 배웠습니다.

보통 엑셀 시트에서 찾기/참조 함수에서 가장 많이 사용하는 것이 vlookup 함수라고 밝혔습니다.

하지만 vlookup 함수는 기준값이 각 데이터 레코드의 가장 왼쪽(1열)에 와 있어야 합니다.

기준값을 중간에 있는 필드(열) 값으로 할 수가 없죠.

그래서 이를 보완할 수 있는 방법이 바로 index 함수와 match 함수를 혼용하여 활용하는 방법입니다.

서두가 길었습니다. 아무래도 글로 설명하는 방법보다는 직접 예를 드는 것이 낫겠죠.

기본적인 문법은 다음과 같습니다.

INDEX(자료범위, MATCH(행 찾을값, 행 찾을범위, 찾을방법), MATCH(열 찾을값, 열 찾을범위, 찾을방법))

INDEX 함수의 기본은 INDEX(자료범위, 행 위치, 열 위치)라는 건 지난번에 배웠죠.

MATCH 함수와 결합하면서 위치를 찾는 것이 포함된 것이죠.

예제를 보겠습니다.

위 표에서 석차 1등인 학생의 이름과 총점을 묻는 문제죠.

그런데 그냥 표만 가지고 보면 누구나 쉽게 찾을 수 있습니다.

석차에서 1을 찾아서 그 열에 있는 데이터중 이름과 총점을 가져오면 되는거죠.

우선 엑셀에서 위 표의 데이터를 아래와 같이 좌표로 인지합니다.

아래 두개의 좌표가 다른점이라면 필드명이 들어갈지 말지의 차이입니다.

위 표는 필드명까지 모두 자료범위에 들어간 경우를 엑셀에서 인식하는 경우입니다.

그리고 아래 표는 필드명은 자료범위에 안 들어간 경우죠.

원리는 간단합니다.

먼저 석차에 1등이 들어간 것이 전체 데이터에서 몇 번째에 있는지를 셉니다.(match 사용)

여기서는 석차~8까지 모두 10개의 행이 있습니다. 

이중에서 1이 있는 위치는 위에서 4번째가 됩니다.(석차, 5, 7, 1 순으로 네번째)

이까지만 하면 우리가 구하고자 하는 행을 찾을 수 있습니다.

다음은 각 위치에 들어갈 성명과 총점이 되겠죠.

성명은 기준값으로 표시되어 있지만 범위에 포함시켜야겠죠.

성명은 A3:I3 행에서 첫번째에 있습니다. 마찬가지로 총점은 7번째가 되죠. (여기도 match 사용)

엑셀에서 이해하는 위치는 석차 1등이 있는 줄(행)과 성명, 총점이 들어 있는 열만 알고 좌표로 나타납니다.

그래서 성명이 들어갈 자리는 (4,1), 그리고 총점은 (4, 7)이 되는 겁니다.

위 그림에서 보면D18셀에 들어있는 수식은 아래와 같습니다.

=INDEX($A$3:$I$12,MATCH(C18,$I$3:$I$12,0),MATCH(D17,$A$3:$I$3,0))

MATCH(C18,$I$3:$I$12,0) : C18에 있는 1이라는 숫자를 $I$3:$I$12(석차, 5, 7, …… , 8) 범위에서 몇 행인지 찾습니다.

MATCH(F17,$I$3:$I$12,0) : F17에 있는 총점이라는 항목을 $A$3:$I$3(성명, 국어, 수학, …… , 석차) 범위에서 몇 열인지 찾습니다.

왜 수식이 이렇게 되는 지 이해가 가나요?

필드명이 있는 행이나 열을 포함할 지 말지는 상황에 따라 판단해야 합니다.

이 두 함수를 자주 혼용하여 파워 유저가 되세요. ^^