Return value associated with last occurrence

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

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

EXCEL

Hard coded formula
Return value associated with last occurrence
Cell reference formula
Return value associated with last occurrence
{=IFERROR(INDEX(C7:C13,SMALL(IF(B7:B13="Yes",ROW(B7:B13)-ROWS(A1:A6),""),COUNTIF(B7:B13,"Yes"))),"")}
{=IFERROR(INDEX(C7:C13,SMALL(IF(B7:B13=C4,ROW(B7:B13)-ROWS(A1:A6),""),COUNTIF(B7:B13,C4))),"")}
GENERIC FORMULA

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

ARGUMENTS
value_rng: A range that contains the values which you want to return that is associated with the last 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 searching for.

GENERIC FORMULA

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

ARGUMENTS
value_rng: A range that contains the values which you want to return that is associated with the last 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 searching for.

EXPLANATION

This is an array formula that uses the IFERROR, INDEX, SMALL, IF, ROW, ROWS and COUNTIF functions to return the value associated with the last 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 directly entered into the formula or referenced to a specific cell.

RELATED TOPICS

Related Topic Description Related Topic and Description
Lookup nth largest value How to return the nth largest value in a range
Return value associated with nth occurrence How to get the value associated with the nth occurrence in a range, with criteria
Return value associated with first occurrence How to get the value associated with the first occurrence in a range, with criteria

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
COUNTIF Function The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria