[ 응용 ] 엑셀 여러 조건을 만족하는 값 찾기, INDEX() 함수와 MATCH() 함수
여러 조건을 만족하는 값을 찾는 방법입니다.
준비하기
아래 그림과 같이 학급과 학생, 과목 그리고 해당 과목의 점수가 있는 데이터가 있습니다.
학급, 학생, 과목을 조건으로 주었을 때
해당 조건이 모두 만족하는 과목의 점수를 찾는 방법입니다.
함수식
INDEX() 함수와 MATCH() 함수를 사용하면
위 그림처럼 두 개 이상의 조건을 만족하는 값도 찾을 수 있습니다.
INDEX()와 MATCH() 함수의 기본 내용은 아래 블로그를 참고하시면 됩니다.
※ INDEX() 함수 : Index() 함수
※ MATCH() 함수 : Match() 함수
※ INDEX() + MATCH() 함수값 찾기 기본 : Vlookup 보다 강력한 값 찾기 Index+Match
사용한 함수식은 다음과 같습니다.
=INDEX($D$2:$D$13,(MATCH(1,($F$2=$A$2:$A$13)*($G$2=$B$2:$B$13)*($H$2=$C$2:$C$13),0)))
함수식 설명
먼저 INDEX() 함수의 첫번째 인자는 값을 찾을 범위입니다.
성적을 찾을 것이기 때문에 D2:D13 범위를 첫 번째 인자로 지정합니다.
두 번째 인자는 행의 위치입니다.
두 번째 인자애서 행의 위치를 찾으면
D2:D13의 범위의 해당 행의 값을 출력합니다.
INDEX() 함수의 두 번째 인자로 MATCH() 함수를 사용하여
행의 위치를 찾습니다.
MATCH() 함수의 첫 번째 인자는 찾을 값입니다.
찾을 값은 1입니다.
왜 1을 찾는지 알아보겠습니다.
두 번째 인자로 아래 수식이 사용되었습니다.
($F$2=$A$2:$A$13)*($G$2=$B$2:$B$13)*($H$2=$C$2:$C$13)
F2의 값이 A2:A13범위에 있는 지 확인을 합니다.
F2의 값은 하늘반이고
A2:A13범위에서 하늘반은 A2:A4 범위에 있습니다.
F2=A2:A13의 반환값은 다음과 같습니다.
{TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
다음 A001이라는 이름을 갖고 있는 학생을 찾습니다.
G2=B2:B13의 수식을 사용하고 반환값은 다음과 같습니다.
{TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
마지막으로 과목을 찾습니다.
H2=C2:C13의 수식을 사용하고 반환값은 다음과 같습니다.
{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}
결국 ($F$2=$A$2:$A$13)*($G$2=$B$2:$B$13)*($H$2=$C$2:$C$13) 함수식은
반환값 세 개가 모두 TRUE인것을 찾는 겁니다.
위 함수의 반환값은 다음과 같습니다.
{1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
엑셀에서 TRUE와 TRUE를 곱하면 반환값은 1이고
TRUE와 FALSE를 곱하면 반환값은 0입니다.
FALSE와 FALSE를 곱하면 반환값은 0입니다.
결국 MATCH() 함수의 첫 번째 인자 1과 일치하는 행은
범위에서 첫번째 행이 됩니다.
반환값 {1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}에서 1은 맨 첫 번째에 있기 때문입니다.
마지막으로 MATCH() 함수의 세 번째 인자로 0을 주어 정확히 일치하는 값을 찾습니다.
마치며
INDEX() 함수와 MATCH() 함수를 위와 같이 조합하면
더 많은 조건의 값도 찾을 수 있습니다.
여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.