[EXCEL/엑셀] INDEX, SMALL 함수 조건을 충족하는 값의 목록 찾기2 다중조건

INDEX 함수의 활용 5번째!

 

SMALL 함수와 같이 사용해서 조건에 맞는 값을 목록 형태로 출력하기!!

이번에는 다중조건!!

 

{=INDEX( 출력할데이터의범위,SMALL(IF(조건1,IF(조건2,ROW( 조건을충족할때출력할데이터범위))),ROW(열번호)))}

 

이 공식 역시 배열 수식이므로 Ctrl + Shift + Enter로 입력해야한다.

 

아래의 링크는 조건 하나일때의 방식

[EXCEL/엑셀] INDEX, SMALL 함수 조건을 충족하는 값의 목록 찾기

 

 

이번에도 예제 파일로 공식을 파헤쳐 보자.

INDEX,SMALL_2.xlsx

0.04MB

 

위 파일을 열면 아래와 같은 데이터가 있다.

A:E까지의 데이터를 가지고 조건1, 조건2를 만족하는 학생코드, 학년, 계열 데이터를 가져와보도록 하자.

시트1 데이터

 

{=INDEX( 출력할데이터의범위,SMALL(IF(조건1,IF(조건2,ROW(조건을충족할때출력할데이터범위))),ROW(열번호)))}

 

H8=INDEX($A$1:$A$267,SMALL(IF($B$1:$B$267=$J$1,IF($D$1:$D$267=$J$2,ROW($A$1:$A$267))),ROW(A1)))

>> 출력할데이터의범위 학생코드

 

H8=INDEX($A$1:$A$267,SMALL(IF($B$1:$B$267=$J$1,IF($D$1:$D$267=$J$2,ROW($A$1:$A$267))),ROW(A1)))

>> 조건1 성별 데이터에서 "남"을 충족 {FALSE(B1값_성별);TRUE(B2값_남);TRUE(B3값_남);FALSE(B4값_여);......} 

 

H8=INDEX($A$1:$A$267,SMALL(IF($B$1:$B$267=$J$1,IF($D$1:$D$267=$J$2,ROW($A$1:$A$267))),ROW(A1)))

>> 조건2 계열 데이터에서 "수원"을 충족 {FALSE(D1값_지역);FALSE(D2값_군포);FALSE(D3값_의왕);....}

 

>> 조건1,2를 모두 충족하는 값 {FALSE;FALSE;FALSE;FALSE;FALSE;TRUE(6행의값);....}

 

H8=INDEX($A$1:$A$267,SMALL(IF($B$1:$B$267=$J$1,IF($D$1:$D$267=$J$2,ROW($A$1:$A$267))),ROW(A1)))

>> 조건1,2가 TRUE이면 나타낼 값 {1;2;3;4;5;6;7....}

 

>> 조건1,2를 모두 충족하는 값일때 출력하는 값 TRUE값 6, 12 .....

 

H8=INDEX($A$1:$A$267,SMALL(IF($B$1:$B$267=$J$1,IF($D$1:$D$267=$J$2,ROW($A$1:$A$267))),ROW(A1)))

>> SMALL의 인수 ROW(A1)=1, 첫번째로 작은 값 출력

 

※ H8=INDEX($A$1:$A$267,6) = "신9670" 값 출력

 

시트2에서는 지난번 하나의 조건만 있었을 때 했었던 것처럼 아래와 같이 9행부터 시작하는 데이터로 수식을 짜보자.

시트2 데이터

■ 방법1

H8=INDEX($A$1:$A$275,SMALL(IF($B$9:$B$275=$J$1,IF($D$9:$D$275=$J$2,ROW($A$9:$A$275))),ROW(A1)))

 

위의 수식처럼 SMALL문의 범위 데이터만 변경하는 방법

 

IF($B$9:$B$275=$J$1 // {FALSE(B9값_성별);TRUE(B10값_남);TRUE(B11값_남);FALSE(B12값_여);......} 

IF($D$9:$D$275=$J$2 // {FALSE(D9값_지역);FALSE(D10값_군포);FALSE(D11값_의왕);....}

 

조건1,2를 모두 충족할 때 출력되는 열번호

ROW($A$9:$A$275) // {9;10;11;12;13;....} 데이터 범위가 A9부터라서 9~275로 출력

 

SMALL문의 값의 1번째로 작은 값 14

 

INDEX($A$1:$A$275,14) = "신9670"

$A$1:$A$275 범위에서 14번째의 값을 출력

 

■ 방법2

H8=INDEX($A$9:$A$275,SMALL(IF($B$9:$B$275=$J$1,IF($D$9:$D$275=$J$2,ROW($A$1:$A$267))),ROW(A1)))

 

위의 수식처럼 INDEX 첫 인수와 조건1,2의 범위 변경하는 경우

 

IF($B$9:$B$275=$J$1 // {FALSE(B9값_성별);TRUE(B10값_남);TRUE(B11값_남);FALSE(B12값_여);......} 

IF($D$9:$D$275=$J$2 // {FALSE(D9값_지역);FALSE(D10값_군포);FALSE(D11값_의왕);....}

 

조건1,2를 모두 충족할 때 출력되는 열번호

ROW($A$1:$A$267) // {1;2;3;4;5;....} 데이터 범위가 A9부터라서 9~275로 출력

 

SMALL문의 값의 1번째로 작은 값 6

 

INDEX($A$9:$A$275,6) = "신9670"

$A$9:$A$275 범위에서 6번째의 값을 출력

 

조건이 여러개 붙는다고 해도 범위만 잘 설정하고 괄호위치만 조금 신경써주면 맞는 수식을 작성할 수 있다.

 

나의 경우는 제일위 H8,I8,J8의 수식을 입력하고 긁어서 수식을 채웠는데 그렇지 않고 범위를 잡아서 Ctrl + Shift + Enter로 입력하는 경우는

SMALL의 2번째인수인 ROW(A1)을 ROW()-7로 입력하면된다.

 

여기서 -7해주는 것은 첫행의 수식을 1로 만들어주려는 것이다.H9부터 수식이 시작하면 ROW()-8로 입력하면 OK!