Return value associated with nth occurrence

This tutorial shows how to get the value associated with the nth occurrence in a range, with criteria, using an Excel formula with the IFERROR, INDEX, SMALL, IF, ROW and ROWS functions

EXCEL FORMULA 1. Return value associated with nth occurrence in a range

EXCEL

Hard coded formula
Return value associated with nth occurrence
Cell reference formula
Return value associated with nth occurrence
{=IFERROR(INDEX(C8:C14,SMALL(IF(B8:B14="Yes",ROW(B8:B14)-ROWS(A1:A7),""),3)),"")}
{=IFERROR(INDEX(C8:C14,SMALL(IF(B8:B14=C4,ROW(B8:B14)-ROWS(A1:A7),""),C5)),"")}
GENERIC FORMULA

{=IFERROR(INDEX(value_rng,SMALL(IF(criteria_rng="criteria",ROW(criteria_rng)-ROWS(row_rng),""),nth_occurrence)),"")}

ARGUMENTS
value_rng: A range that contains the values which you want to return that is associated with the nth occurrence.
criteria_rng: A range that contains the criteria.
row_rng: A range from first row to the row just before the criteria range starts.
criteria: The criteria that you are search for.
nth_occurrence: The nth occurrence of the criteria.

GENERIC FORMULA

{=IFERROR(INDEX(value_rng,SMALL(IF(criteria_rng=criteria,ROW(criteria_rng)-ROWS(row_rng),""),nth_occurrence)),"")}

ARGUMENTS
value_rng: A range that contains the values which you want to return that is associated with the nth occurrence.
criteria_rng: A range that contains the criteria.
row_rng: A range from first row to the row just before the criteria range starts.
criteria: The criteria that you are search for.
nth_occurrence: The nth occurrence of the criteria.

EXPLANATION

This is an array formula that uses the IFERROR, INDEX, SMALL, IF, ROW and ROWS functions to return the value associated with the nth occurrence in a range, with criteria.
Given that this is an array formula once you have entered the formula into a cell you will need to press the Control + Shift + Enter keys simultaneously.

Click on either the Hard Coded or Cell Reference button to view the formula that has the criteria and the nth occurrence directly entered into the formula or referenced to specific cells.

RELATED TOPICS

Related Topic Description Related Topic and Description
Lookup nth largest value How to return the nth largest value in a range

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
IFERROR Function The Excel IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result
INDEX Function The Excel INDEX function returns a value that is referenced from a specified range
SMALL Function The Excel SMALL function returns the numeric value from a specified range based on the nth smallest position
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
ROW Function The Excel ROW function returns the first row number of the selected reference
ROWS Function The Excel ROWS function returns the number of rows in a specified array