다중 범위 조건 여러 결과 출력
지원 버전 자세히 보기
여러 조건 열을 모두 만족하는 행을 한 번에 출력하는 공식입니다.
인수 설명
012021 이후 FILTER 방식
=FILTER ( 출력범위, ISNUMBER ( MATCH ( 조건범위1, 조건1, 0 ) )*ISNUMBER ( MATCH ( 조건범위2, 조건2, 0 ) ) )인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
동작 원리
두 MATCH 함수가 각 조건 목록의 포함 여부를 계산하고 두 결과를 곱해 모든 조건을 만족하는 행만 FILTER 함수로 반환합니다.
2021 이후 FILTER 방식의 예제 시트에서 G2:G5에 스필되는 네 이름의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
첫 번째 MATCH 함수가 허용 부서를 찾습니다
MATCH 함수는 B2:B8의 부서를 D2:D3의 영업, 개발과 비교해 조건 목록의 순번 또는 #N/A 오류를 반환합니다.
=MATCH ( B2:B8, D2:D3, 0 )
= {1;#N/A;2;1;2;#N/A;1}
영업은 1, 개발은 2, 인사는 #N/A입니다. 두 번째 MATCH 함수가 허용 등급을 찾습니다
MATCH 함수는 C2:C8의 등급을 E2:E3의 A, B와 비교해 조건 목록의 순번 또는 #N/A 오류를 반환합니다.
=MATCH ( C2:C8, E2:E3, 0 )
= {1;1;2;#N/A;1;2;2}
A는 1, B는 2, C는 #N/A입니다. 두 조건 마스크를 곱합니다
ISNUMBER 함수가 각 MATCH 결과를 TRUE와 FALSE로 바꾸고 두 배열을 곱합니다. 두 조건이 모두 TRUE인 행만 1로 남습니다.
=ISNUMBER ( {1;#N/A;2;1;2;#N/A;1} )*ISNUMBER ( {1;1;2;#N/A;1;2;2} )
= {1;0;1;0;1;0;1}
두 조건 목록에 모두 포함된 행의 마스크입니다. FILTER 함수가 일치하는 이름을 반환합니다
FILTER 함수는 마스크가 1인 1, 3, 5, 7번째 행의 이름을 골라 G2부터 세로로 스필합니다.
=FILTER ( A2:A8, {1;0;1;0;1;0;1} )
= {"김민준";"이도윤";"정우진";"오세훈"}
두 조건 목록을 모두 만족하는 네 이름입니다. 02모든 버전 INDEX 배열 방식
=IFERROR ( INDEX ( 출력범위, SMALL ( IF ( ISNUMBER ( MATCH ( 조건범위1, 조건1, 0 ) )*ISNUMBER ( MATCH ( 조건범위2, 조건2, 0 ) ), ROW ( 출력범위 )-ROW ( INDEX ( 출력범위, 1 ) )+1, "" ), 반환순번 ) ), "" )인수 설명 자세히 보기
동작 원리
두 조건 마스크를 곱해 모두 일치하는 행 번호만 남기고 SMALL 함수와 INDEX 함수가 번호 순서대로 이름을 반환합니다.
모든 버전 INDEX 배열 방식의 예제 시트에서 G2 셀이 김민준을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
두 조건 마스크를 곱합니다
각 MATCH 결과를 ISNUMBER 함수로 확인한 뒤 곱합니다. 부서와 등급 조건을 모두 만족하는 행만 1로 남습니다.
=ISNUMBER ( MATCH ( B2:B8, D2:D3, 0 ) )*ISNUMBER ( MATCH ( C2:C8, E2:E3, 0 ) )
= {1;0;1;0;1;0;1}
두 조건 목록에 모두 포함된 행의 마스크입니다. IF 함수가 원본 행 번호만 남깁니다
IF 함수는 마스크가 1인 행의 상대 번호 1, 3, 5, 7만 남깁니다.
=IF ( {1;0;1;0;1;0;1}, ROW ( A2:A8 )-ROW ( INDEX ( A2:A8, 1 ) )+1, "" )
= {1;"";3;"";5;"";7}
두 조건을 모두 만족하는 원본 행의 상대 번호입니다. SMALL 함수가 다음 행 번호를 꺼냅니다
G2에서 ROWS 함수는 1을 반환하므로 SMALL 함수는 첫 번째로 작은 행 번호 1을 반환합니다. 수식을 아래로 채우면 3, 5, 7을 차례대로 반환합니다.
=SMALL ( {1;"";3;"";5;"";7}, ROWS ( $G$2:G2 ) )
= 1
G2에서 사용할 첫 번째 일치 행 번호입니다. INDEX 함수가 이름을 반환합니다
INDEX 함수는 A2:A8의 첫 번째 값인 김민준을 G2에 반환합니다. G3:G5에서는 행 번호 3, 5, 7에 있는 이름을 같은 방식으로 반환합니다.
=INDEX ( A2:A8, 1 )
= "김민준"
G2에 출력되는 첫 번째 결과입니다. 