엑셀 여러 조건을 만족하는 n번째 값 찾기 공식
지원 버전 자세히 보기
두 개 이상의 조건을 모두 만족하는 n번째 값을 찾는 공식입니다.
인수 설명
01모든 버전 호환 배열수식
{=INDEX ( 출력범위, SMALL ( IF ( ( 조건범위1=조건1 ) * ( 조건범위2=조건2 ), ROW ( 조건범위1 ) - ROW ( INDEX ( 조건범위1, 1, 1 ) ) + 1 ), n번째 ) )}인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
022021 이후 버전 공식
=INDEX ( FILTER ( 출력범위, ( 조건범위1=조건1 ) * ( 조건범위2=조건2 ) ), n번째 )인수 설명 자세히 보기
동작 원리
01모든 버전 호환 배열수식
두 조건의 일치 여부를 곱해 공통 행을 남기고, SMALL 함수와 INDEX 함수가 n번째 행의 값을 반환합니다.
모든 버전 호환 배열수식의 예제 시트에서 G5 셀이 오이를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
두 조건을 동시에 비교합니다
분류가 채소인 행과 지역이 서울인 행을 각각 비교한 뒤 두 결과를 곱합니다. 두 조건을 모두 만족하는 2번째, 5번째, 7번째 행만 1로 남습니다.
=( A2:A8=G2 ) * ( B2:B8=G3 )
= {0;1;0;0;1;0;1}
두 조건을 모두 만족하는 행만 1입니다. IF 함수가 일치한 행의 순번을 남깁니다
ROW 함수가 만든 1부터 7까지의 순번에서 두 조건을 만족하는 행만 남깁니다. 조건을 만족하지 않는 행의 결과는 0이 아니라 FALSE입니다.
=IF ( ( A2:A8=G2 ) * ( B2:B8=G3 ), ROW ( A2:A8 ) - ROW ( INDEX ( A2:A8, 1, 1 ) ) + 1 )
= {FALSE;2;FALSE;FALSE;5;FALSE;7}
일치한 행의 상대 순번은 2, 5, 7입니다. SMALL 함수가 n번째 순번을 찾습니다
SMALL 함수는 FALSE를 제외한 순번 2, 5, 7에서 G4 셀에 입력된 두 번째 순번인 5를 반환합니다.
=SMALL ( IF ( ( A2:A8=G2 ) * ( B2:B8=G3 ), ROW ( A2:A8 ) - ROW ( INDEX ( A2:A8, 1, 1 ) ) + 1 ), G4 )
= 5
두 번째로 작은 유효 순번입니다. INDEX 함수가 해당 품목을 반환합니다
INDEX 함수는 C2:C8의 다섯 번째 값인 오이를 결과값으로 반환합니다.
=INDEX ( C2:C8, SMALL ( IF ( ( A2:A8=G2 ) * ( B2:B8=G3 ), ROW ( A2:A8 ) - ROW ( INDEX ( A2:A8, 1, 1 ) ) + 1 ), G4 ) )
= "오이"
C2:C8의 다섯 번째 값입니다. 022021 이후 버전 공식
FILTER 함수는 여러 조건을 모두 만족하는 값만 추리고 INDEX 함수는 그중 n번째 값을 반환합니다.
2021 이후 버전 공식의 예제 시트에서 G5 셀이 오이를 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
두 조건을 동시에 비교합니다
분류가 채소인 행과 지역이 서울인 행을 각각 비교한 뒤 두 결과를 곱합니다. 두 조건을 모두 만족하는 2번째, 5번째, 7번째 행만 1로 남습니다.
=( A2:A8=G2 ) * ( B2:B8=G3 )
= {0;1;0;0;1;0;1}
두 조건을 모두 만족하는 행만 1입니다. FILTER 함수가 일치하는 품목을 추립니다
FILTER 함수는 조건 배열이 1인 행의 품목만 원래 순서대로 반환합니다.
=FILTER ( C2:C8, ( A2:A8=G2 ) * ( B2:B8=G3 ) )
= {"배추";"오이";"양파"}
두 조건을 모두 만족하는 품목입니다. INDEX 함수가 n번째 품목을 반환합니다
INDEX 함수는 FILTER 함수가 반환한 세 품목에서 G4 셀에 입력된 두 번째 값인 오이를 반환합니다.
=INDEX ( FILTER ( C2:C8, ( A2:A8=G2 ) * ( B2:B8=G3 ) ), G4 )
= "오이"
필터된 목록의 두 번째 값입니다. 
IF 함수의 조건으로 특정문자 포함 공식(ISNUMBER/SEARCH) 을 적용해보세요. 공식은 아래 링크를 확인해보세요.
https://www.oppadu.com/if-%ED%95%A8%EC%88%98-%ED%8A%B9%EC%A0%95-%EB%AC%B8%EC%9E%90-%ED%8F%AC%ED%95%A8/
SQL 사용한 data라서
출력 범위에
여러 조건 만족하는 값이 2개 이상인 것도 있고 1개만 있는 경우도 있는데
N번째 일치값 공식도 필요해서
조건 만족 값이 1개만 있는 경우에도 오류 없이 1번째 출력을 할수 있는 방법이 있을까요..?
조건범위와 조건을 OR 함수로 작성해보시겠어요?^^
형태로 수정하시면 바로 해결될겁니다.
위 공식을 사용해보세요.
=INDEX(출력범위,SMALL(IF((조건범위1=조건1)*(조건범위2=조건2)...,ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1),N번째))
조건범위1, 2 중 편한 범위를 넣어주면 됩니다.
단, 모든 조건 범위의 높이는 반드시 동일해야 합니다.