Excel How To Get the Last Row and Column Value

In this blog post, we are going to learn how to use two Excel functions, TAKE and XLOOKUP, to retrieve data from the very last row of a table. Knowing how to use these two functions can be incredibly handy when you add a new data row every day, week, or month and want your formula to retrieve a value added to the bottom of the table.

Video

Step by step

Let’s say we have a Price table where we add a new row each month with the updated price.

We want to write a formula to automatically retrieve the price from the last row. And when we add a new row, we want the formula to retrieve the new value. For this, we can use the TAKE function.

TAKE

The TAKE function is used to grab the first or last (or first few or last few) rows or columns from a table. To use this function, the first argument is the array or range of values we want to retrieve. The second argument is the number of rows and the third argument (optional) is the number of columns. When we use a positive number for these two arguments, Excel starts at the beginning and returns the first rows/columns. By using a negative number instead, we tell Excel we want to retrieve from the end … that is, the last rows/columns.

Since our table is named Prices, we can retrieve all values in the last row with the following formula:

=TAKE(Prices, -1)

If we only want the value from the Price column (the last column) instead of all columns, we can additionally use -1 for the columns argument like this:

=TAKE(Prices, -1, -1)

This would return the value from the last row and the last column in the table.

But, what if we didn’t want the last row … but instead, the last row of a specific item? That brings us to XLOOKUP.

XLOOKUP

The XLOOKUP function is used to do a lookup and return a related value. By default, XLOOKUP searches for the items from the top and proceeds down until it finds a matching value. However, the last argument allows us to change the default so that it searches starting from the bottom.

To demonstrate how this works, consider the following screenshot of the Items table.

Rather than retrieving the last row, we want to retrieve the last row of a given item. For example, the last row for X100 or the last row for Y200.

We would use the XLOOKUP function, where the first argument is the lookup value (X100), the second argument is the lookup array (the column of items), the third argument is the return array (the column of prices), and the last argument is -1 to indicate a bottom up search.

=XLOOKUP("X100", Items[Item], Items[Price],,,-1)

This would return the last row for the item X100, which would be the price of 10.

If you have any suggestions or alternative ways to accomplish this, please share by posting a comment below … thanks!

Sample file