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:

=LOOKUP(2,1/(B3:B11=E3),C3:C11)

Learn to add more criteria:

Find last matching value in an unsorted table

DonW asks: Ok, you've shown it for regular ranges....how about within tables. I have a table similar to: ID Name […]

Comments(0) Filed in category: Excel, Lookup function

Explaining the array formula

Step 1 - Find matching values

This logical expression returns an array corresponding to cell range B3:B11.

B3:B11=E3

becomes

{"SV";"AD";"WE";"SV";"SX";"HJ";"KL";"SV";"XC"}="SV"

returns

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}

TRUE means value is equal to cell E3.

Step 2 - Divide 1 with array

The LOOKUP function allows you to fetch the last value in an array if all other values are errors. 1/0 returns #DIV/0!.

1/{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE}

returns

{1;#DIV/0!;#DIV/0!;1; #DIV/0!;#DIV/0!; #DIV/0!;1;#DIV/0!}

Step 3 - Find last value in array

LOOKUP(2,1/(B3:B11=E3),C3:C11)

becomes

LOOKUP(2,1/{1;#DIV/0!;#DIV/0!;1; #DIV/0!;#DIV/0!; #DIV/0!;1;#DIV/0!},C3:C11)

Step 4 - Return corresponding value

LOOKUP(2,1/(B3:B11=E3),C3:C11)

becomes

LOOKUP(2,1/{1;#DIV/0!;#DIV/0!;1; #DIV/0!;#DIV/0!; #DIV/0!;1;#DIV/0!},,{25;20;30; 80;50; 60;70;40; 90})

and returns 40 in cell F3.

Download *.xlsx file

Find last matching value in an unsorted listv2.xlsx