Find the Last Occurrence of an Item in a List in Excel

Recently, I was working on setting the agenda for a meeting, and the Meeting Moderator is supposed to be someone who hasn't moderated the meeting recently. Hence, I needed a way to identify the last occurrence when a person was the moderator.

Suppose I have a data set as shown below:

Last Occurrence of an Item in Excel Formula

The idea is to get the last occurrence of the selected name, and return the date next of it.

If you have a basic understanding of Excel Functions, you would know that there is no simple and direct way to do it. Nevertheless, you are in the Formula Hack section, and here we make it happen.

In this blog post, I will show you two ways to do this. The first method uses the MAX function and I got this from an Excel MVP Charley Kyd's Blog.

Method 1 (Using MAX)

Here is the formula that can make this happen:

=INDEX($B$2:$B$15,SUMPRODUCT(MAX(ROW($A$2:$A$15)*($D$3=$A$2:$A$15))-1))

Let me explain how this works

Testpic1
Method 2 (Using MATCH)

This is a new method from yours truly (that is me!!). It uses a smart MATCH trick, and also makes the formula shorter. Here is the formula

=INDEX($B$2:$B$15,MATCH(1,MATCH($A$2:$A$15,D3,0),1))

Let me explain how this works

Last Occurence of Item in Excel Method 2

Since this is an array formula, use Control + Shift + Enter (instead of Enter)

You May Also Like the Following Excel Tutorials:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)