Find Last Occurrence

I was recently asked how to find the last occurrence of a value in a column. Although there are multiple ways to accomplish this, in this post, I’ll talk about how to use XLOOKUP.

Objective

Before we jump into Excel, let’s confirm our objective. Let’s say we have a bunch of transactions that look like this:

Now let’s say that we want to retrieve the Transaction ID for the last transaction for a given Item. For example, the last transaction for Item A is 1010. The last transaction for Item C is 1009. In other words, we want to start at the bottom and search up.

In this post, we’ll use our friend XLOOKUP.

Note: depending on when you are reading this, your Excel version may not have XLOOKUP. A quick way to determine if it does is to type =X in any cell and see if XLOOKUP appears in the resulting list.

Video

Narrative

XLOOKUP

XLOOKUP is designed to look for a matching value and return a related item. The basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array)

There are also a few optional arguments, including one that enables us to define the search order. For example, first to last or … wait for it … last to first!

It is the 6th argument that enables us to define the search order. If we use 1 or omit it, it will search first to last. If we use -1, it will search last to first.

If we wanted to search last to first, our formula would look more like this:

=XLOOKUP(lookup_value, lookup_array, return_array, , ,-1)

Let’s give it a try.

Illustration

We type in the Item we are searching for in C6, and set up a few empty cells to store the Transaction, Amount, and Row, like this:

Let’s start by retrieving the last Transaction.

Transaction

To retrieve the Transaction ID, we write the following in cell C7:

=XLOOKUP(C6,C14:C23,B14:B23,,,-1)

Since we used -1 for the 6th argument, the formula returns 1010:

Now, we can use the same technique to grab the Amount.

Amount

To retrieve the Amount, we write the following in C8:

=XLOOKUP(C6,C14:C23,D14:D23,,,-1)

The formula returns 308 … nice:

And … what about returning the row … can we do that?

Row

To get the row number, there’s one more thing about XLOOKUP to unpack. As we saw above, XLOOKUP returns a cell value … right? Seems like it.

But under the hood, it returns a range. When nothing else is going on in the formula, it simple returns the value from that range. But, we can use it with other functions as desired.

For example, the ROW function returns the row number of the argument. So, we can wrap the ROW function around the XLOOKUP function like this:

=ROW(XLOOKUP(C6,C14:C23,C14:C23,,,-1))

And it returns the row number of the last transaction for the specified Item:

Nice!

Bonus

Also, here is a little bonus. Let’s say we would like Excel to highlight the last transaction for the specified item, like this:

We can use Conditional Formatting to accomplish this. We can select the entire data range, in this case B14:D23 and select Home > Conditional Formatting > New Rule.

We select the Use a formula to determine which cells to format option, and enter the following formula:

=ROW()=$C$C9

We can then click Format to select our desired formatting.

Now, Excel will highlight the last transaction. So when we type in a new item, such as E, the worksheet updates:

As you can see, XLOOKUP offers a great way to find the last occurrence of an item in a column. Let me know what you think by posting a comment below. Also, if you have a preferred method or other option, please share it with others by posting a comment below … thanks!

Sample File

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

2 Comments

  1. Polly Grell on July 29, 2020 at 8:34 am

    Hi Jeff, Love your posts. What are alternate ways to accomplish this? I have Office 2016, and xlookup isn’t available. However, this is something I am often needing…

    • jim on August 3, 2020 at 9:22 am

      This always used to be a headache – the best solution is to subscribe to 365

      The best way I found to do this pre-365 is to use this formula:
      =INDEX(lookuprange,MATCH(2,1/(matchrange=matchvalue)))

      This works by finding the last value less than 2 in the range returned by the a=b part of the formula (matches return 1, all the rest are errors and are ignored), and then indexing to the equivalent row in the lookup range

      Convoluted, but it works (XLOOKUP is so much better)

      jim

Leave a Comment