엑셀] index, match함수로 다중조건에 해당하는 값 찾기

 

여러 조건에 맞는 값 찾기 - index, match함수

이전 글에서 여러 조건에 맞는 데이터를 vlookup 함수를 이용하는 방법에 대해 포스팅했었다. 

이번 글에서는 index, match함수를 이용해서 다중 조건으로 데이터를 찾는 방법에 대해 정리해본다. 

 

- 엑셀팁] 여러 조건에 맞는 값을 vlookup함수로 찾는 방법

엑셀팁] 여러 조건에 맞는 값을 vlookup함수로 찾는 방법

위 포스팅과 동일한 상황을 전제로 vlookup 대신 index, match함수를 이용해서 원하는 답을 찾아보았다. 
특정한 유니크한 값 없이 여러개의 조건에 맞는 데이터를 구하려고 한다. 
여기서는 생산년도와 부품코드 두가지 조건에 맞는 데이터를 찾으려고 한다. 

 

먼저 결론적으로 사용된 수식은 아래와 같다. 
{=INDEX($C$3:$C$11, MATCH(1,(E3=$A$3:$A$11)*(F3=$B$3:$B$11),0))}

(수식의 양쪽 끝에 {}표시가 있는 것은 이 수식이 배열수식이기 때문이다. )

 

 

- 엑셀] 배열 수식의 기초적인 이해

엑셀] 배열 수식의 기초적인 이해

 

먼저 index함수 사용법은 아래와 같다. 

index(검색범위, 세로위치, 가로위치)



지금 상황에서 index 함수의 인수 중 
첫번째인 검색범위는 C열이고, 
두번째인 세로위치에 해당하는 단가는 찾아봐야하는 값이니 일단 넘어가고, 
세번째인 가로위치는 C열에서만 찾으면 되기 때문에 1이라고 하면 되는 1인 경우는 아예 생략가능하다. 
 
그러면 이걸 세번째 인수를 생략하고 index(C범위, ?? )로 나타내보자. 


이제 ??로 표현된 세로위치에 해당하는 값을 match함수로 찾아보자. 



match함수의 사용법은 아래와 같다. 

match(찾는 값, 검색범위, 0)

마지막 인수 0은 정확도를 위한 값이다. 


지금 생산년도와 부품코드의 조합으로 원하는 데이터를 찾을 때는 아래와 같이 사용한다.

match(1, (찾는 생산년도 = 생산년도 범위) * ( 찾는 부품코드 = 부품코드 범위), 0)
(만약 조건이 더 있으면 그 조건도 *로 연결할 수 있다. )

위 수식을 보면 조금 생소한 부분이 있다. 맨 처음 나오는 1이다. 
원래 match함수에서 첫번째 인수는 찾는 값이고 두번째 인수가 검색범위인데
찾는 값이 1이고 검색범위가 *로 연결된다???

조금 더 자세히 보자. 

match(1, (E3=$A$3:$A$11) * (F3=$B$3:$B$11), 0)  이 수식을 하나씩 뜯어서 이해해보자.

E3가 "2018년"이기 때문에 $A$3:$A$11범위와 하나씩 대응해서 일치하면 1, 일치하지 않으면 0의 값을 갖는다.

(1은 ture이고 0은 false이다)

(E3=$A$3:$A$11) 수식에서 E3와 같은 값은 2018년만 1이 되기 때문에

계산결과 {1, 1, 1, 0, 0, 0, 0, 0, 0}을 가지게 된다. 



두번째 조건도 마찬가지로 F3인 B형과 같은 값만 1(True)을 갖기 때문에 

계산결과 {0, 1, 0, 0, 1, 0, 0, 1, 0}을 갖게 된다. 

 

이 두 결과를 논리 곱으로 계산하면 모두 true인 경우만 true를 반환한다. 

{1, 1, 1, 0, 0, 0, 0, 0, 0}
{0, 1, 0, 0, 1, 0, 0, 1, 0}
--------------------------
{0, 1, 0, 0, 0, 0, 0, 0, 0}


최종적으로 match함수의 첫번째 인수로 주어진 찾는 값 "1" 이

배열 {0, 1, 0, 0, 0, 0, 0, 0, 0} 에서 2번째에 있기 때문에 match함수의 계산결과는 "2"를 반환한다. 

헥헥.. 길고 긴 길을 돌아왔다. 

이제 최종 수식이 나온다. 

{=index(검색범위, match(1, (조건값1=조건 비교범위1)*(조건값2=조건 비교범위2), 0))}

이 수식을 입력하고 배열함수이기 때문에 Ctrl + Shift + Enter로 입력하면 수식이 완성된다. 

덧. 

오피스365와 엑셀2019부터는 배열수식을 입력하고 Ctrl +  Shift + Enter를 입력하지 않고 그냥 엔터만 입력해도 알아서 배열수식으로 입력한다고 한다. 

나도 여기서 이 글을 쓰면서 처음에 아무생각없이 엔터만 쳤는데 수식이 제대로 작동하는 바람에 "엥?" 하고 놀랐다. 

 

그냥 엔터를 쳐도 동작한다는 건 확인했는데 구글링을 해봐도 MS에서 배열수식 입력에 관핸서 내놓은 공식적인 답변은 찾지 못했다.