[엑셀]INDEX*MATCH함수로 다중조건 일치값 찾기!!

ㅇㅇㅇ (1)

이번 포스트를 보고나면

INDEX * MATCH함수의 사용방법과 다중조건 만족값을 찾을 수 있습니다.

검색함수

INDEX는 참조범위에서 행, 열 위치를 지정하여 값을 출력 또는 행,열 위치를 지정하지 않고 참조범위내 모든 값을 출력할 수 있는 함수 입니다.

검색함수

MATCH는 찾을 범위에서 찾을 값이 갖는 상대적 위치값을 반환하는 함수로 보통 INDEX함수,OFFSET함수등과 같이 쓰여 사용되는 함수 입니다.

배열입력

INDEX*MATCH함수의  기본적인 사용방법과 배열입력의 방법을 알고 이를 어떻게 활용하는지 포스트내에서 확인할 수 있습니다.

목차


설명 및 구문

INDEX,MATCH함수를 활용한 다중조건을 만족하는 값을 찾는 방법을 알아보도록 하겠습니다.

다중조건을 만족하는 모든값을 보고 싶을 경우 FILTER함수를 사용가능하며, FILTER함수는 엑셀2019이상버전부터 지원하므로 엑셀2016이하 사용자분들은 아래 FILTER함수효과내기 포스트를 참고하시면 됩니다.

 

관련포스트 :

1.FITLER함수 사용효과내기

2.INDEX함수사용법

3.MATCH함수사용법

 


주의사항

배열수식입력을 사용합니다. ARRAYFORMULA를 입력하지 않으면 오류를 반환합니다.

*참고사항

배열수식입력은 엑셀에서는 CTRL+SHIFT+ENTER로 입력으로 가능/스프레드시트에서는 ARRAYFORMULA, CTRL+SHIFT+ENTER등으로 직접 입력가능함.


예제1

INDEXMATCH수식

내용에 앞서 알아두어야 할 사항

INDEX*MATCH로 다중조건을 구하려고 할 경우 우선 먼저 이해하고 넘어갈 사항이 있습니다.

엑셀을 사용하다보면 보통 숫자 ‘1’은 TRUE, ‘0’은 FALSE로 나타내는 것을 볼 수 있습니다.

TRUE는 참값일 경우 FALSE는 참값이 아닐 경우를 의미합니다.

다중조건은 여러개의 조건이죠, 그리고 이 다중조건을 만족하려면 당연히 모든 조건의 값이 모두 다 참값이어야 한다는 건데요, 그러면 이걸 수식으로 나타내야 한다면 어떻게 될까요?

TRUE는 엑셀에서 다르게 표현하면 1이라고 위에서 설명을 했습니다. 그렇다면 결국엔 다중조건의 일치하는 값이 1이되어야 한다는 것이죠. 각각의 1을 하나의 1로 표현하는 방법은 결국 곱셈이 되는데요. 수식으로 표현하면 1*1*1이 되겠죠. 이 방법을 사용하여 INDEX*MATCH함수로 다중조건 일치값을 구하게 됩니다.

사용된 수식 설명

위 예제에서 사용된 수식은 다음과 같습니다.

 

=INDEX(C8:C20,MATCH(1,ARRAYFORMULA((D8:D20=C4)*(H8:H20=D4)*(F8:F20=F4)),0))

 

ARRAYFORMULA는 해당범위에 대한 조건값을 모두 반환합니다. 배열수식으로 입력하지 않을시 범위의 첫번째 행에서만 값을 찾고 반환하게 되는데 배열수식으로 입력시 모든 범위에 대해 값을 찾고 이를 반환하게 됩니다. 엑셀에서는 ARRAYFORMULA가 없기 때문에 수식줄에서 ARRAYFORMULA를 제외한 수식을 모두 입력 후 CTRL+SHIFT+ENTER로 입력하면 됩니다.

 

MATCH함수는 조건을 만족하는 행위치를 반환하게 되고 INDEX함수가 찾으려는 범위에서 MATCH함수가 찾은 결과값을 행값으로 하여 해당 결과값(이름)을 반환합니다.

위의 예제에서 값 “타레사”는 C8:C20의 범위에서 10번째 행에 위치하고 있습니다.

 

수식 MATCH(1,ARRAYFORMULA((D8:D20=C4)*(H8:H20=D4)*(F8:F20=F4)),0)은 각 조건을 모두 만족하는 값인 “타레사”의 행 10을 반환하고 있다는 것을 알 수 있습니다.

 

이상으로 INDEX*MATCH함수를 사용하여 다중조건의 검색방법에 대하여 알아보았습니다.