메뉴
실무 위키›응용 공식›다중 범위 + 여러 조건 검색 공식

다중 범위 + 여러 조건 검색 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

여러 조건 열을 모두 만족하는 행을 한 번에 출력하는 공식입니다.

예제파일 다운로드
다중 범위 + 여러 조건 검색 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01FILTER 함수 활용 (엑셀 2021 이후)

=FILTER ( 출력범위, ISNUMBER ( MATCH ( 조건범위1, 조건1, 0 ) )*ISNUMBER ( MATCH ( 조건범위2, 조건2, 0 ) ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
출력범위필수두 조건 목록을 모두 만족할 때 반환할 값 범위입니다.
조건범위1필수첫 번째 조건 목록과 비교할 값 범위입니다. 출력범위와 높이가 같아야 합니다.
조건1필수첫 번째 조건범위에서 허용할 조건 목록입니다.
조건범위2필수두 번째 조건 목록과 비교할 값 범위입니다. 출력범위와 높이가 같아야 합니다.
조건2필수두 번째 조건범위에서 허용할 조건 목록입니다.
G2=FILTER(A2:A8,ISNUMBER(MATCH(B2:B8,D2:D3,0))*ISNUMBER(MATCH(C2:C8,E2:E3,0)))
A
B
C
D
E
F
G
H
1
이름
부서
등급
허용 부서
허용 등급
결과
2
김민준
영업
A
영업
A
김민준
3
박서연
인사
A
개발
B
이도윤
4
이도윤
개발
B
정우진
5
최하은
영업
C
오세훈
6
정우진
개발
A
7
한지민
인사
B
8
오세훈
영업
B
9

수식을 입력한 셀자동으로 채워진 범위

부서가 영업 또는 개발이면서 등급이 A 또는 B인 김민준, 이도윤, 정우진, 오세훈을 G2:G5에 차례대로 스필합니다.

02INDEX/MATCH 응용 공식 (모든 버전)

=IFERROR ( INDEX ( 출력범위, SMALL ( IF ( ISNUMBER ( MATCH ( 조건범위1, 조건1, 0 ) )*ISNUMBER ( MATCH ( 조건범위2, 조건2, 0 ) ), ROW ( 출력범위 )-ROW ( INDEX ( 출력범위, 1 ) )+1, "" ), 반환순번 ) ), "" )
인수 설명 자세히 보기
인수구분설명
출력범위필수두 조건 목록을 모두 만족할 때 반환할 값 범위입니다. 배열수식에서는 절대참조로 고정합니다.
조건범위1필수첫 번째 조건 목록과 비교할 값 범위입니다. 출력범위와 높이가 같아야 하며 절대참조로 고정합니다.
조건1필수첫 번째 조건범위에서 허용할 조건 목록입니다. 절대참조로 고정합니다.
조건범위2필수두 번째 조건 목록과 비교할 값 범위입니다. 출력범위와 높이가 같아야 하며 절대참조로 고정합니다.
조건2필수두 번째 조건범위에서 허용할 조건 목록입니다. 절대참조로 고정합니다.
반환순번필수현재 결과 셀에서 몇 번째 일치 행을 반환할지 정하는 값입니다. 수식을 아래로 채우면 1씩 증가합니다.
G2=IFERROR(INDEX($A$2:$A$8,SMALL(IF(ISNUMBER(MATCH($B$2:$B$8,$D$2:$D$3,0))*ISNUMBER(MATCH($C$2:$C$8,$E$2:$E$3,0)),ROW($A$2:$A$8)-ROW(INDEX($A$2:$A$8,1))+1,""),ROWS($G$2:G2))),"")
A
B
C
D
E
F
G
H
1
이름
부서
등급
허용 부서
허용 등급
결과
2
김민준
영업
A
영업
A
김민준
3
박서연
인사
A
개발
B
이도윤
4
이도윤
개발
B
정우진
5
최하은
영업
C
오세훈
6
정우진
개발
A
7
한지민
인사
B
8
오세훈
영업
B
9
두 조건을 모두 만족하는 원본 행 번호 1, 3, 5, 7을 작은 순서대로 꺼내 G2:G5에 김민준, 이도윤, 정우진, 오세훈을 출력합니다.
이 공식이 사용하는 함수
FILTER 함수 MATCH 함수 IFERROR 함수 INDEX 함수 IF 함수 ROW 함수

동작 원리

01FILTER 함수 활용 (엑셀 2021 이후)

두 MATCH 함수가 각 조건 목록의 포함 여부를 계산하고 두 결과를 곱해 모든 조건을 만족하는 행만 FILTER 함수로 반환합니다.

FILTER 함수 활용 공식의 예제 시트에서 G2:G5에 스필되는 네 이름의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

첫 번째 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입니다.
2

두 번째 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입니다.
3

두 조건 마스크를 곱합니다

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} 두 조건 목록에 모두 포함된 행의 마스크입니다.
4

FILTER 함수가 일치하는 이름을 반환합니다

FILTER 함수는 마스크가 1인 1, 3, 5, 7번째 행의 이름을 골라 G2부터 세로로 스필합니다.

=FILTER ( A2:A8, {1;0;1;0;1;0;1} )
= {"김민준";"이도윤";"정우진";"오세훈"} 두 조건 목록을 모두 만족하는 네 이름입니다.

02INDEX/MATCH 응용 공식 (모든 버전)

두 조건 마스크를 곱해 모두 일치하는 행 번호만 남기고 SMALL 함수와 INDEX 함수가 번호 순서대로 이름을 반환합니다.

모든 버전 INDEX 배열 방식의 예제 시트에서 G2 셀이 김민준을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

두 조건 마스크를 곱합니다

각 MATCH 결과를 ISNUMBER 함수로 확인한 뒤 곱합니다. 부서와 등급 조건을 모두 만족하는 행만 1로 남습니다.

=ISNUMBER ( MATCH ( B2:B8, D2:D3, 0 ) )*ISNUMBER ( MATCH ( C2:C8, E2:E3, 0 ) )
= {1;0;1;0;1;0;1} 두 조건 목록에 모두 포함된 행의 마스크입니다.
2

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} 두 조건을 모두 만족하는 원본 행의 상대 번호입니다.
3

SMALL 함수가 다음 행 번호를 꺼냅니다

G2에서 ROWS 함수는 1을 반환하므로 SMALL 함수는 첫 번째로 작은 행 번호 1을 반환합니다. 수식을 아래로 채우면 3, 5, 7을 차례대로 반환합니다.

=SMALL ( {1;"";3;"";5;"";7}, ROWS ( $G$2:G2 ) )
= 1 G2에서 사용할 첫 번째 일치 행 번호입니다.
4

INDEX 함수가 이름을 반환합니다

INDEX 함수는 A2:A8의 첫 번째 값인 김민준을 G2에 반환합니다. G3:G5에서는 행 번호 3, 5, 7에 있는 이름을 같은 방식으로 반환합니다.

=INDEX ( A2:A8, 1 )
= "김민준" G2에 출력되는 첫 번째 결과입니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료