I read an interesting blog post Find Last Item in Group With Index Match written by Debra Dalgleish. It is about finding the last matching value in a sorted list. It got me thinking how to find the last matching item in an unsorted list.

This picture shows random text strings in column B and values in column C. Cell E3 contains the search value and F3 contains an array formula that returns the last matching value in a list.

find last matching value in an unsorted list

Array formula in cell F3:

=INDEX($C$3:$C$11,MAX(($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11))))

Explaining the array formula

Step 1 - Find matching values

($E$3=$B$3:$B$11) returns {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}

Step 2 - Create row numbers for cell range $B$3:$B$11

MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11)) returns {1; 2; 3; 4; 5; 6; 7; 8; 9}

Step 3 - Multiply arrays

($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11))

becomes

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE} *  {1; 2; 3; 4; 5; 6; 7; 8; 9} returns {1; 0; 0; 4; 0; 0; 0; 8; 0}

Step 4 - Maximum value in array

MAX(($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11)))

becomes

MAX({1; 0; 0; 4; 0; 0; 0; 8; 0}) and returns 8.

Step 5 - Return a value of a cell at the intersection of a particular row and column

=INDEX($C$3:$C$11,MAX(($E$3=$B$3:$B$11)*MATCH(ROW($B$3:$B$11),ROW($B$3:$B$11))))

becomes

=INDEX($C$3:$C$11, 8)

Download *.xlsx file

Find last matching value in an unsorted list.xlsx

Functions in this post

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MAX(number1, number2)
Returns the largest value in a set of values

MATCH(lookup_value, lookup_array, [match_type]
Returns the relative position of an item in an array that matches a specified value

ROW(reference)
Returns the rownumber of a reference