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.
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.
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.
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
The code in the 6th row is CO005, and that’s what the INDEX function returns.
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.
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.
__________________
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
i cant seem to get the formula for non sorted list to work.
Thanks Debra!!! This is awesome. Exactly what I needed.
You’re welcome, Andrea! Thanks for letting me know that it helped.
You can VLOOKUP function and set the range_lookup to TRUE for approximate match
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
what if there is two category, hows the formula ?
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 ?