INDEX MATCH : 엑셀 찾아오기 함수

 

INDEX 함수와 MATCH 함수는 왜 같이 언급될까요?

같이 사용해야  그 유명한 VLOOKUP 기능을 구현할 수 있기 때문입니다.

VLOOKUP 대신 INDEX MATCHF를 사용하는 이유는 VLOOKUP이 못하는 것도 할 수 있기 때문입니다.

 

1.  INDEX  함수에서,  "행번호", "열번호"  의미

 

INDEX 함수는 기본적으로 지정한 범위 안에서의 "행번호", "열번호"를  인수로 받아서, 

그 위치의 값을 가져오는 함수입니다.
"행번호", "열번호"는 "행 index, 열 index 라고 해도됩니다.

INDEX()의 인수는 아래와 같습니다.

INDEX( 범위,   행번호,    열번호)

여기서, 행번호, 열번호는 "범위"에 종속된 번호임을 분명히 아셔야 합니다.

아래와 같이 데이터가 있다고 합시다.
빨간 테두리 범위를 INDEX 함수에서 사용할 때, 
행번호, 열번호는 범위의 첫번째 셀이 시작점입니다.
행번호는 이 범위의 첫행이 1이고, 아래 방향으로 +1씩 됩니다.
열번호는 이 범위의 첫열이 1이고, 오른쪽 방향으로 +1씩 됩니다.

 

2.   INDEX, MATCH 의 조합

INDEX는 행과 열을 주면, 그 위치의 값을 가져옵니다.

MATCH는 어떤 값이 주어진 범위의 몇 번째인지 순번을 반환합니다.

"MATCH 는 순번을 반환한다" 라고 외우세요.

두 함수를 다음과 같이  조합하는 것이 가장 일반적입니다.

INDEX( 범위, 행, 열)  에서, 행 또는 열 번호를 MATCH 함수로 채우는 조합입니다.
MATCH 함수가 행번호 또는 열번호 위치에 들어갑니다.

다음 세가지 형태가 모두 가능합니다.

(범위는 각각 다른 것이 일반적입니다)

INDEX( 범위,  행, MATCH( 찾을 값, 범위) ) 

INDEX( 범위,  MATCH( 찾을 값, 범위) ,  열) 

INDEX( 범위, MATCH( 찾을 값, 범위),  MATCH( 찾을 값, 범위) ) 


아래 데이터에서, "홍길동4"를 찾아서 같은 행의 "몸무게"값을 가져오려면,
INDEX, MATCH를 어떻게 사용하면 될까요?

2)번 수식의 (4,3)을 보세요. 이렇게 숫자 2개만 지정하면 그 위치의 값을 찾아오는 것이 INDEX 함수입니다.
이 2개 숫자가 "범위 안에서의 행번호/열번호"입니다.

 

3.    MATCH 함수의 범위(목록)  인수

MATCH 함수에서 사용하는 범위는 "1차원 범위(영역)" 이라는 점을 잊기 쉽습니다.
MATCH 함수는 순번을 반환하고,
INDEX 함수는 순번을 이용해 값을 가져옵니다.

 행과 열을 모두 MATCH 함수로 채우면 다음과 같은 형태입니다.

INDEX( 범위, MATCH(lookup_value, 1차원 범위), MATCH(lookup_value, 1차원 범위) )  

1차원 범위는 가로든 세로든 한 방향으로만 값이 나열된 범위를 말합니다. 
vector라고 말해도 되고, 리스트(목록)라고 불러도 무방합니다.
중요한 점은 1차원 데이터 (한쪽 방향으로만 나열된 데이터)라는 점입니다.

아래 2개 데이터는 각각 1차원 데이터입니다.
세로방향, 가로방향으로 방향이 전혀 다르지만, 원소들의 순번은 같습니다.
홍길동1은 1, 홍길동2는 2,...  이렇게.

"홍길동4"의 순번을 MATCH()함수를 사용해서 가져오는 사례입니다.

두 수식 모두 4를 반환합니다.

MATCH() 함수는 가로 세로 상관없이, 1차원 범위를 인수로 사용한다는 점을 기억하세요.

 

4.  VLOOKUP은 못하는데, INDEX( MATCH )는 할 수 있는 경우

 

VLOOKUP은 못하는데, INDEX( MATCH )는 할 수 있는 경우

다음과 같이 데이터가 있다고 합시다.
"홍길동4"의 "학년"값을 가져오려면 수식을 어떻게 만들까요?

 

 

VLOOKUP()을 사용하려면, "성명"열이 "학년"열보다 왼쪽에 있어야 합니다.

INDEX( MATCH() )를 사용하면 그런 제약이 없습니다.
즉, lookup_value가 포함된 검색열이 테이블 범위안에서 어느 열에 있건 상관없습니다.
아래와 같이 식을 만들면 됩니다.

INDEX($B$3:$E$8, MATCH("홍길동4", $D$3:$D$8),1)

 

 

5.   다중조건을 만족하는 값을 가져오는 방법

INDEX ( MATCH() )를 사용하면 다중조건을 만족하는 값도 가져올 수 있습니다.

 

이런 데이터가 있다고 합시다.

"개나리"반이고, "나이"가 10살인 사람의 "이름"을 가져오려면 어떻게 할까요?
(조건식1)*(조건식2) 하면 됩니다. 이것은 (배열수식)* (배열수식) 으로서, 결과는 배열상수입니다.
예를 들면,  {0, 0, 0, 1, 0, 0} 과 같은 배열을 반환합니다.

1)  먼저, MATCH()에 조건식을 넣어서, 조건에 맞는 행index를 구합니다.
=MATCH(1,  ($C$3:$C$8="개나리")*($D$3:$D$8=10) , 0)
이렇게 하면, 4를 반환합니다. 
MATCH 함수의 첫번째 인수인 1은 조건식 ($C$3:$C$8="개나리")*($D$3:$D$8=10)이 반환하는 배열에서 1을 찾겠다는 의미입니다.

2)  전체 데이터 범위 $C$3:$C$8 에서 "이름"열의 열번호는 1입니다.  

따라서, INDEX( 범위, 4, 1) 이 되도록 만들면 됩니다.
=INDEX($B$3:$E$8, MATCH(1,  ($C$3:$C$8="개나리")*($D$3:$D$8=10), 0), 1)
이렇게 하면, "홍길동4"를 반환합니다.  조건에 맞는 결과입니다.

 

 

** INDEX( MATCH()) 다중조건 제약사항

- 곱하는 조건식들에 사용된 범위들은 방향과 크기가 같아야 합니다.
위 사례를 보면, "반이름"과 "나이"는 둘 다 세로방향이고, 6개 값을 가지고 있으므로, 다중 조건을 만들 수 있습니다.

-  AND 조건만 가능합니다.