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).