[EXCEL/엑셀] INDEX, MATCH 함수 다중조건으로 값 찾기

INDEX, MATCH 함수의 두 번째

다중조건에서의 값 찾기!!

 

VLOOKUP을 사용할 때 다중조건으로 값을 찾기 위해서는 기본 데이터를 일부 변형해서 값을 찾을 수 있었다.

 

아래의 포스팅 참조~

 

[EXCEL/엑셀] VLOOKUP 함수2 2가지 기준으로 값 찾기, 2개 이상의 값 찾기(FEAT.COUNTIF)

 

하지만 INDEX, MATCH 함수를 이용하면 기본 데이터의 변형 없이 값을 구할 수 있다.

 

조건은 2개 이상 설정할 수 있으며, 공식은 아래와 같다.

 

=INDEX(출력범위, MATCH(1,(조건범위1=조건1)*(조건범위2=조건2)......,0),)

여기서 출력범위와 조건범위의 시작행과 끝행은 동일해야 한다.

 

+ Ctrl + Shift + Enter (배열수식)

 

참고로 배열수식이긴 한데 내가 사용하고 있는 엑셀 365버전에서는 배열수식이 자동적용되기 때문에

그 보다 이전의 버전을 사용하는 환경에서는 배열수식으로 입력해주어야한다.

 

배열수식을 사용하면 수식 앞과 뒤에 { } 괄호가 표시된다.

 

INDEX,MATCH_2.xlsx

0.04MB

 

첨부파일의 데이터에서 이름에 맞는 과목의 강좌명을 구할 때 

VLOOKUP을 사용하는 경우 이름&과목의 데이터를 합쳐서 해당 값으로 출력될 값을 찾을 수 있었는데

이름&과목의 데이터를 C열에 합쳐서

C열의 데이터로 VLOOKUP 사용하여 값을 출력

 

INDEX, MATCH 함수를 사용하면 추가적인 데이터 합침 없이 값을 출력할 수 있다.

 

값은 VLOOKUP를 사용한 데이터와 동일한 값을 출력할 수 있다.

 

F2=INDEX($C$2:$C$426,MATCH(1,($A$2:$A$426=$E2)*($B$2:$B$426=F$1),0), ) .. 출력범위 강좌명을 출력하기 때문에 강좌명의 전체 데이터 범위를 입력

F2=INDEX($C$2:$C$426,MATCH(1,($A$2:$A$426=$E2)*($B$2:$B$426=F$1),0), ) .. 1은 O, 0은 X 조건에 맞는 값을 찾기 때문에 1

F2=INDEX($C$2:$C$426,MATCH(1,($A$2:$A$426=$E2)*($B$2:$B$426=F$1),0), ) .. 조건1 이름이 강O민, (이름 전체 범위=강O민)

F2=INDEX($C$2:$C$426,MATCH(1,($A$2:$A$426=$E2)*($B$2:$B$426=F$1),0), ) .. 조건2 과목이 국어, (과목 전체 범위=국어)

F2=INDEX($C$2:$C$426,MATCH(1,($A$2:$A$426=$E2)*($B$2:$B$426=F$1),0), ) .. 정확히 일치

F2=INDEX($C$2:$C$426,MATCH(1,($A$2:$A$426=$E2)*($B$2:$B$426=F$1),0), ) .. 행번호가 지정되었기 때문에 열번호는 생략가능

 

이렇게 수식을 설정하면 VLOOKUP을 사용한 데이터와 동일한 값을 구할 수 있다.

 

여기서 출력범위 $C$2:$C$426와 조건범위1 $A$2:$A$426, 조건범위2 $B$2:$B$426 행의 시작과 끝이 동일하게 설정해주어야 한다.

 

MATCH(1,($A$2:$A$426=$E2)*($B$2:$B$426=F$1),0)에서 

 

조건범위 $A$2:$A$426 데이터에서 {강O민,강O민,강O민,강O혁,강O혁....} 조건 E2에 맞는지 보면 {1,1,1,0,0...} 값이 출력된다.

조건범위 $B$2:$B$426 데이터에서 {국어,수학,영어,국어,수학......} 조건 F1에 맞는지 보면 {1,0,0,1,0,0....} 값이 출력된다.

 

이 두 가지의 값을 곱하면 {1,0,0,0,0.....} 값으로 출력되며

MATCH 함수 첫 번째 인수가 1이기 때문에 1의 값을 1번째 위치에서 찾을 수 있기 때문에 $C$2:$C$426의 1번째 위치인 언어와매체가 출력되는 것이다.