Get the last three values of column in Google Sheets

Question

I need to create a report that pulls the last 3 cell values in a column.

This will need to be in three separate cells.

Column C will contain data which will be added to daily.

• D2 will contain the value of the last but 2 cell in column C.
• D3 will contain the value of the last but 1 cell in column C.
• D4 will contain the value of the last cell in column C.

Note : I'm using Google Sheets.

Any help will be greatly appreciated.

Answer 1

You should be able to use this:

=QUERY(SORTN(FILTER({C:C,ROW(C:C)},C:C<>""),3,0,2,0),"Select Col1 ORDER BY Col2")

FILTER creates a virtual array containing all non-blank data from C:C alongside the row number for each row.

SORTN returns three results, discounting for ties (which won't exist since each row number is unique), from the virtual array after sorting it upside-down by row number.

QUERY reorders in the original row order and then returns just the three results in the data column (i.e., excludes the no-longer-needed row numbers).

Answer 2

I know this a very old question, and my answer may not be relevant to you now. But when I was searching for a similar solution, I came across this answer and was frankly overwhelmed by it.

I found a simpler (for me) solution using offset, count and transpose. Hopefully, it helps anyone who may search for a similar solution and end up at this page!

In your case, in cell D2 you have to enter the following formula:

=transpose(offset(C:C,counta(C:C)-3,0))

Explanation:

• Offset returns a range starting at a given 'offset' value - so taking a count of no. of rows and starting the range from 3 rows above the end gives you the last three rows.
• Transpose converts it from a column to a row of 3 items.