Using the SORTN or VLOOKUP functions, you can write a formula in Google Sheets to return month-end rows from a daily data table. This means you can easily extract the rows that contain the last day of each month without the need for extra columns or manual calculations.
In our example, we have a range A1:B that contains daily entries, and we want to extract only the month-end rows into the range D2:F. If there are multiple entries on the last day of the month, the formula will return the last entry based on the row position.
To achieve this, follow these step-by-step instructions:
SORTN to Return Month-End Rows from Daily Data Table
Before we start, make sure the data is in chronological order based on the date column.
- Adding a Date and Year Column with the Data
- Use the following formula:
=ArrayFormula({eomonth(A2:A20,0),A2:B20}) - Select column D and go to the menu Format > Number > Custom number format, and enter “mmmm-yyyy” to display the month and year.
- Sort by Month and Year (End of the Month Column) and Then by Row
- Use the following formula:
=sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE)
- SORTN to Extract Month-End Rows from Daily Data in Google Sheets
- Use the following formula:
=sortn(sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE),9^9,2,1,TRUE)
You will now have the month-end rows from the daily data table in Google Sheets. You can limit the number of columns in the result by using the QUERY function.
If you prefer to use VLOOKUP instead of SORTN, follow these steps:
VLOOKUP to Return Month-End Rows from Daily Data Table
-
Follow steps 1 and 2 from the previous method to create the Step_2_formula.
-
Use the following generic formula to return the month-end rows:
=arrayformula(ifna(vlookup(D2:D,step_2_formula,3,0)))
That’s it! You can now extract the month-end rows from your daily data table using either SORTN or VLOOKUP in Google Sheets.
For more Google Sheets tips and tricks, visit Crawlan.com. Enjoy!
