Find Last Item in Group With Index Match

Find Last Item in Group With Index Match

How can you use a formula to find the last item in any category, in a sorted list? Someone asked me that question last week, and I used a combination of INDEX and MATCH to find the solution.

To show how I solved it, I’ve set up a small table, with food products, sorted by category and product code. The product codes have two letters, followed by three numbers. The table is named tblProducts.

NOTE: If the product codes aren’t sorted, this solution won’t work.

indexmatchlastcode01

Step 1: Find the Category Start

Cell B2 contains the category name – Cookies – and I want to find the last code for that category.

The first step is to find where the Cookies category starts, in the Category column. The MATCH function can return the position in that column.

I’ll create a formula in cell D2, to get the position number:

=MATCH(B2,tblProducts[Category],0)

The lookup value is in cell B2 (Cookies), and the lookup array is the Category column in tblProducts. The last argument is zero, because I want an exact match for the lookup value.

indexmatchlastcode02

The result of this formula is 4 – the first Cookies product is Arrowroot, in the table’s 4th row of data.

Step 2: Find the Number of Rows

If I want to find the last row in the category, I need to know how many rows are in that category. I’ll use the COUNTIF function in cell E2, to calculate the number of rows there are for Cookies:

=COUNTIF(tblProducts[Category],B2)

The formula counts the number of time that “Cookies” (in cell B2), occurs in the table’s Category column.

indexmatchlastcode03

The result is 3, the number of rows with Cookies products.

Step 3: Find the Last Code

The next step is to use the INDEX function to find the last code for Cookies. I’ll enter this formula in cell C2:

=INDEX(tblProducts[ProdCode],D2+E2-1)

To get the row for the index function, we add the values in D2 and E2, then subtract 1. In this example, this last code is in row 6 of the table’s data:

4 + 3 – 1 = 6

indexmatchlastcode04

The code in the 6th row is CO005, and that’s what the INDEX function returns.

indexmatchlastcode05

Step 4: (Optional) Combine the Formulas

Sometimes it helps to have the formulas in separate cells, so it’s easier to understand how the pieces work. In this example, I could leave the MATCH and COUNTIF formulas in cells D2 and E2.

If you don’t want to see the steps, or you don’t have enough room to show the steps separately, you can combine all the formulas, into cell C2:

  • In the INDEX formula, replace the reference to D2 with the MATCH formula from D2:

=INDEX(tblProducts[ProdCode],MATCH(B2,tblProducts[Category],0)+E2-1)

  • Next, replace the reference to E2 with the COUNTIF formula from E2:

=INDEX(tblProducts[ProdCode],MATCH(B2,tblProducts[Category],0)
+COUNTIF(tblProducts[Category],B2)-1)

  • Then, clear cells D1:E2.

To test the formula, type a different category name in cell B2, and its last code will appear in cell C2.

NOTE: I’ve added line breaks in the formula, so it is easier to read.

indexmatchlastcode06

Download the Sample File

To test the formulas with the data from this article, you can download the sample file from my Contextures website. On the Sample Files page, go to the Functions section, and look for FN0031 – Find Last Code With INDEX and MATCH.

The file is in xlsx format, and does not contain macros.

__________________

9 thoughts on “Find Last Item in Group With Index Match”

  1. And array entered formula
    =INDEX(tblProducts[ProdCode],MAX((B2=tblProducts[Category])*(ROW(tblProducts[Category])-ROW(tblProducts[[#Headers],[Category]]))))
    works for not sorted list as well

  2. Interesting article. You also use simple Index & Match as shown below
    {=INDEX(tblProducts[ProdCode], MATCH(2,1/(tblProducts[Category]=B2)))}
    This works because if no match is found MATCH function returns the position of the last value in the array

  3. what if there is two or more criteria ? how is the formula to find last cell with data if the criteria is more than one ?

Leave a Reply to dennis lee Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.