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:

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**

*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**

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

**You May Also Like the Following Excel Tutorials:**

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)