Using SORTN or VLOOKUP, we can write a formula to return month-end rows from a daily data table in Google Sheets.

In other words, we can say, to return the rows containing the last day of each month from a table, we can use the function SORTN or VLOOKUP in Google Sheets.

I am talking about an array formula. We don’t need to use it in every row or use a helper column for the same.

In our example below, the sample data in the range A1:B contains daily entries.

The expected result in the range D2:F has only the month-end rows from the daily data in A1:B.

If multiple entries fall on the end-of-the-month date, the formula will only return the last entry based on the row position.

Just go through the result and the sample data to understand this.

Find below the step-by-step instructions for writing an array formula that returns month-end rows from daily data in Google Sheets.

SORTN to Return Month-End Rows from Daily Data Table

Before starting, please ensure the data is in chronological order based on the date column.

Here are the steps to return the last day of each month from a table in Google Sheets.

We will start writing the formula for a closed range A2:B20, not A2:B. So it will be easy to learn. Later on, we can very easily modify it for an open range.

1. Adding a Date and Year Column with the Data

Logic:- Using SORTN, we will remove duplicates from the data based on an end-of-the-month column. So we require one such column with the existing table. The below formula does this.

Step_1_Formula:

=ArrayFormula({eomonth(A2:A20,0),A2:B20})

Select D2:D20, go to the menu Format > Number > Custom number format, and enter mmmm-yyy in the given field. It’s optional, though.

Note:- D2:D20 acts as a month and year column.

Formula Explanation:

The EOMONTH function returns a column with the month-end dates. 

To combine the sample data with this column, I have used the Curly Brackets.

I have used the ARRAYFORMULA because of the EOMONTH non-array function in the formula.

2. Sort by Month and Year (End of the Month Column ) and Then by Row

Assume we unique the above output using SORTN. If so, we will end up with a table containing the first entry of each month.

So, we should sort this data by month and year (end of the month) column in ascending order and then by row numbers from 2 to 20 in descending order.

With the help of SORT and ROW functions, we can meet our requirements.

Generic Formula: Sort(Step_1_Formula,1,TRUE,row(A2:A20),FALSE)

Step_2_Formula (based on the above generic formula):

=sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE)

Note:- We can remove ARRAYFORMULA within SORT. So you won’t see it with the Step_1_Formula here.

It places the rows to extract on the top of every month’s group. Now we can extract month-end rows from daily data.

In step 3, you can learn the SORTN use with the above table.

3. SORTN to Extract Month-End Rows from Daily Data in Google Sheets

Out of the four tie modes from 0 to 3, we can use tie mode 2 in SORTN here. It’s for removing duplicate rows based on a selected column.

Generic Formula: =sortn(step_2_formula,9^9,2,1,TRUE)

Generic Formula Explanation:

9^9 – As per the present data, there are 4 rows in the result.

We can use 4 instead of 9^9 in the formula. But when we are uncertain about the number of rows in the output, it is not safe to use a fixed number like 4.

So, using 9^9 (an arbitrarily large number) is safe to return all the rows in the result.

2 – Tie mode to delete duplicates.

1 – The position of the column, based on which, we want to unique the table.

TRUE – To sort column 1 in ascending order.

Here is the formula based on the above step 3 generic formula.

Final_Formula:

=sortn(sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE),9^9,2,1,TRUE)

It will extract month-end rows from the daily data table in Google Sheets. Please see image # 1 above for the result.

There are three columns in the result. They are a month and year (end of the month) column, an end-of-the-month date column, and the amount column.

You can limit the number of columns in the result by using QUERY as below.

Generic Formula: =query(Final_Formula,"Select Col1,Col2,Col3")

In the SELECT clause, include only the required columns. For example, use the below formula to return the 2nd and 3rd columns.

=query(sortn(sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE),9^9,2,1,TRUE),"Select Col2,Col3")

How Can I Adapt the Formula to an Open Range

There are two steps.

1. Make all the ranges open in the Final_formula.

2. Replace eomonth(A2:A,0) with iferror(eomonth(datevalue(A2:A),0))

So the formula for an open range will be;

=sortn(sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,TRUE,row(A2:A),FALSE),9^9,2,1,TRUE)

Note:- Here also you can wrap the formula with QUERY to select only the columns that you want.

Vlookup to Return Month-End Rows from Daily Data Table

It is an alternative to the above SORTN formula.

Up to step # 2, there are no changes. That means we require the Step_2_formula here.

We will use the step_2 formula as the VLOOKUP range to return month-end rows from a daily data table in Google Sheets.

Let me introduce you to the Generic formula first.

Generic Formula: =arrayformula(ifna(vlookup(search_keys,step_2_formula,3,0)))

We only require the search keys to use in the VLOOKUP. The search keys are just the unique month and year (end of the month) values.

Search_Key:

=unique(ArrayFormula(iferror(eomonth(datevalue(A2:A),0))))

Now let’s write the formula by following the generic formula.

=arrayformula(ifna(vlookup(D2:D,sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,TRUE,row(A2:A),FALSE),3,0)))

That’s all.

Thanks for the stay. Enjoy!

Sample_Sheet_31121