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.

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)

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