엑셀 조건을 만족하는 n번째 값 찾기 공식
엑셀 중복값 포함 n 번째 값 찾기 목차 바로가기
함수 공식
{ =INDEX($출력범위,SMALL(IF($조건범위=조건,ROW($조건범위)-ROW(INDEX($조건범위,1,1))+1),n번째)) }
본 공식은 배열수식이므로 Microsoft 365 이전버전 사용자일 경우 Ctrl + Shift + Enter 로 입력합니다.
인수 설명
| 인수 | 설명 |
| $출력범위 | n번째 값을 찾을 대상 범위입니다. |
| $조건범위 | 조건이 입력된 범위입니다. |
| 조건 | 조건범위에서 찾을 조건입니다. |
| n번째 | 조건을 만족하는 n 번째 값을 출력범위에서 반환합니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 특정 조건 만족하는 n번째 값 찾기예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 조건 만족하는 n번째 값 찾기 공식 알아보기
공식 설명
조건범위에서 조건을 만족하는 값들 중에서 n 번째 값을 찾아 반환하는 공식입니다. 배열수식이므로 Microsoft 365 이전 버전 사용자일 경우 Ctrl + Shift + Enter키로 수식을 입력합니다.
만약 조건범위에 만족하는 조건이 없을경우, 본 공식은 #NUM! 오류를 반환합니다. 또한, 조건을 만족하는 값의 개수가 3개인데 n번째로 4를 입력할 경우 4번째 값은 공백이므로 해당 공식은 #NUM! 오류를 반환합니다.
1개의 조건이 아닌 여러개의 조건을 만족하는 경우의 n 번째 값을 찾으려면 아래 공식을 사용하세요.
=INDEX(출력범위,SMALL(IF((조건범위1=조건1)*(조건범위2=조건2)...,ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1),N번째))공식의 동작원리
- ROW(조건범위) 는 조건범위의 시작행~마지막행의 행 번호를 세로배열로 반환합니다.
=ROW(A3:A8)
={3,4,5,6,7,8} - ROW(INDEX(조건범위,1,1)) 는 조건범위의 시작셀 행 번호를 반환합니다.
=ROW(INDEX(A3:A8,1,1))
=3 - ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1는 1부터 시작하는 순번을 조건범위의 행 개수만큼 배열로 반환합니다.
=ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1
=ROW(A3:A8)-ROW(INDEX(A3:A8,1,1))+1
={3,4,5,6,7,8}-3+1
={1,2,3,4,5,6} - IF(조건범위=조건,ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1) 로 조건범위에서 조건을 만족할 경우 해당 순번만 배열로 반환합니다.
=IF({"과일","채소","과일","과일","채소","과일"}="채소",ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1)
=IF({FALSE,TRUE,FALSE,FALSE,TRUE,FALSE},{1,2,3,4,5,6})
={0,2,0,0,5,0} - SMALL(IF(조건범위=조건,ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1),N번째)는 <span
style="text-decoration: underline;">반환된 값 중 n번째로 작은값을 출력합니다.
=SMALL({0,2,0,0,5,0},2)
=5 '2번째로 작은값인 5를 반환합니다. - INDEX 함수를 통해 출력범위에서 k 번째 위치한 값을 반환하며 공식이 마무리됩니다.
=INDEX(출력범위,SMALL(IF(조건범위=조건,ROW(조건범위)-ROW(INDEX(조건범위,1,1))+1),N번째))
=INDEX({"사과","배추","딸기","복숭아","오이","귤"},5)
="복숭아"
- ROW(조건범위) 는 조건범위의 시작행~마지막행의 행 번호를 세로배열로 반환합니다.
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 중 편한 범위를 넣어주면 됩니다.
단, 모든 조건 범위의 높이는 반드시 동일해야 합니다.