Get Last Data Row from PivotTable

The post was written to answer a question I recently received: How can we retrieve the last row from a PivotTable? Buckle up as we solve this challenge by nesting two Excel functions into a single formula.

Video

Step-by-step

Let’s visualize the essenence of the question. We have a PivotTable in our worksheet, perhaps something like this:

And we’d like to write a formula in another cell, perhaps even on a different worksheet, that retrieves the last row value from the PivotTable. We want the formula to continue to work even as the values in the PivotTable change. That is, when we update the PivotTable next period, we want the formula to continue to retrieve the last data row.

We will walk through the functions and application in three exercises.

Exercise 1: Warm-up

Before we embark on this quest, let’s get to know our key functions: INDEX and MATCH.

The MATCH function returns the position of a matching value in a range. The INDEX function returns a value from a specific position in a range. While these sound similar, we will use them both to accomplish our objective.

Let’s begin by understating what they do and what values they return.

We have a list that looks like this:

The MATCH function returns the position of a matching value. So, let’s say we wanted it to figure out the position of Wed within the list B11:B15. We can type the value we are trying to find, Wed, into a cell D11, and then prepare to write a formula in the adjacent cell E11:

We use the following formula:

=MATCH(D11,B11:B15,0)

The MATCH function returns 3, letting us know that Wed is in the third position (row) within the range B11:B15.

So, this demonstrates that the MATCH function returns the position of a matching value in a range.

Now let’s use INDEX to return a cell value. We can ask the INDEX function to return the cell value from a given position within a list. If we wanted to return the first cell value from the range B11:B12, we could use this:

=INDEX(B11:B15,1)

But, in addition to entering 1 as the second argument, we can also point it to a cell reference. For example, we could ask it to return a cell value from the position determined by the MATCH function like this:

=INDEX(B11:B15,E11)

Here are the results:

Now, in practice, this specific series of formulas doesn’t accomplish much. However, now that we are warmed up, we can take the next step and point them to different lists to accomplish our goal.

Exercise 2: PivotTable

Let’s take another look at our PivotTable.

Let’s start by retrieving the last row … the Grand Total. (Then we’ll move up one row to retrieve the last data row Mar.)

First, we’ll ask the MATCH function to return the position of the string “Grand Total” as follows:

=MATCH("Grand Total",B:B,0)

This returns the position of 10:

And now that the MATCH function returns 10, we can have the INDEX function use that result to return the value in the 10th row of column C like this:

=INDEX(C:C,E7)

It returns 480, which is perfect:

Now, what if we aren’t interested in the Grand Total value, but the value above (that is, the last data row) instead? Not a problem. We can simply subtract 1 from the result of the MATCH function like this:

=MATCH("Grand Total",B:B,0)-1

This in turn returns 9, which causes the INDEX function to return 151:

Now, let’s eliminate the intermediate MATCH function and combine everything into a single formula.

Exercise 3: Remove intermediate formula

Wouldn’t it be great if we could combine the MATCH and INDEX functions into one single formula and avoid the intermediate step? Of course, it would! So, here’s how we can combine them:

=INDEX(C:C,MATCH("Grand Total",B:B,0))

And this returns the Grand Total value like this:

If we wanted to return the last data row instead of the Grand Total, we can subtract 1 from the result of the MATCH function like this:

=INDEX(C:C,MATCH("Grand Total",B:B,0)-1)

And results:

And what’s nice is that it continues to return the last data row when the PivotTable is refreshed:

And that is how we can combine INDEX/MATCH to return the last row from a PivotTable. Mission accomplished!

Conclusion

We used the INDEX and MATCH functions to extract the last row in a PivotTable. This combo provides us a seamless way to find the position of the “last row” label and pull the corresponding cell value. Furthermore, by simply subtracting from (or adding to) the MATCH function results, we are able to easily move the position up (or down) as needed.

If you have any alternatives, questions, or enhancements, please share by posting a comment below … thanks!

File Download

Frequently Asked Questions (FAQs)

Q: What does the MATCH function in Excel do?

A: The MATCH function returns the relative position of a matching value in a specified range.

Q: What does INDEX function do?

A: The INDEX function retrieves a value from a specific location in a range based on its position.

Q: How can MATCH and INDEX functions be used together?

A: We can use the MATCH function to find the position of a required value within a range, and the INDEX function can retrieve the actual value at that position.

Q: How can I use MATCH and INDEX functions to find the grand total row in a PivotTable?

A: Begin by using the MATCH function to find the position of the “Grand Total” label in your PivotTable, then use the INDEX function to return the corresponding value for that position.

Q: What if I am interested in the last data row instead of the Grand Total line?

A: Subtract one from the result of the MATCH function.

Q: How do I make the INDEX/MATCH formula update automatically when new data is added?

A: Simply ensuring your INDEX/MATCH formula refers to the entire column should suffice. Every time you refresh your PivotTable, the function will update to reflect the last data row.

Q: Can the MATCH function return multiple matches?

A: No, the MATCH function will only return the first match found.

Q: Can the INDEX function return a value from a 2-dimensional array?

A: Yes, the INDEX function can work with both 1-dimensional and 2-dimensional ranges.

Q: What would happen if the MATCH function doesn’t find a match?

A: The MATCH function returns #N/A error if it cannot find a match.