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:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)

  • michael

    Hi Sumit

    I tried this formula and it seems that your formula is returning the last date in some of the items and in other cases the date before the last occurrence. Am I doing something wrong in my setup?

    When I change the first argument in the Match function from 1 to 2, everything seems to work fine. When the argument is 1, it seems to work for some but not for others.

    {=+INDEX($J$2:$J$15,MATCH(1,MATCH($I$2:$I$15,L3,0),1))}

    Feb 14 is the last occurrence for Steve and Jan 14th is the first occurrence. Is there something wrong in my set up

    Steve 16-Jan-14 (Formula 2)
    13-Feb-14 (Formula 1 – correct)

  • IMC

    Hello, this is what I am looking for!

    I looking to find the last “ok” in the row then give me the number the row next to it.

    tried =LOOKUP(“ok”,G:G,F:F) gives not the last one but in the middle.
    tried =OFFSET(INDEX(F:F,MATCH(“ok”,G:G)),0,0) gives the same result.
    see attached picture I tried to get the balance next to the last “ok”
    balance in row F
    “ok” in row G
    thanks in advance,
    IMC

  • Mike Stout

    This same Match Formula works with Google Sheets as well! Very helpful. Thank you.

  • Pavel Paukov

    {=MAX(IF(A:A=”Apple”,1,0)*ROW(A:A),0)}

    Where
    First A:A is the range where you are looking for last occurrence (Can be any other range E.G. A4:B12).
    Row(A:A) – relative multiplier (Can be any letter, but should be same first and last rows as in first range. E.G. if first range is A4:B12, row() should be row(A4:A12) or Row(A4:B12) or Row(C4:C12).
    last A:A is the range
    “Apple” can be anything you are looking for e.g. Number, Text, or cell reference (like D4)

    P.S. If you are looking for 0, formula above will return last occurrence of 0 or blank cell. If you are looking exactly for 0 and not blank cell, use: =MAX(IF(A:A=””,0,IF(A:A=0,1,0))*ROW(A:A),0)

  • Pavel Paukov

    use {} brackets in second case as well – {=MAX(IF(A:A=””,0,IF(A:A=0,1,0))*ROW(A:A),0)}

  • Robert Wickberg

    Thanks for this, it was very helpful. IMO, one thing’s not very well explained. Put these formulas in cell E3, then fill in D3 with one of the values in column A, and E3 will tell you the date in column B that corresponds to the last occurance of that value.

  • jerryaldini

    Works great, but I need it to search for partial text. In my case, I am searching for the next cell in column C after the last occurrence of “OK” in column H. I need to match all “OK – blahblah”.
    =INDEX($C$60:$C$85,MATCH(1,MATCH($H$60:$H$85,H90,0),1)+1)
    works, but requires exact text in H90.