Return last entry by month and year

This tutorial shows how to get the last entry by month and year using the LOOKUP and TEXT functions

Excel

Return last entry by month and year using LOOKUP and TEXT functions

EXCEL FORMULA 1. Return last entry by month and year with LOOKUP and TEXT functions

EXCEL

Hard coded formula

Cell reference formula

=LOOKUP(2,1/(TEXT($B$5:$B$9,"mmmm yyyy")=TEXT("January 2020","mmmm yyyy")),$C$5:$C$9)

=LOOKUP(2,1/(TEXT($B$5:$B$9,"mmmm yyyy")=TEXT(E5,"mmmm yyyy")),$C$5:$C$9)

GENERIC FORMULA

=LOOKUP(2,1/(TEXT(date_range,"mmmm yyyy")=TEXT("date","mmmm yyyy")),amount_range)

ARGUMENTS
amount_range: A range that contains the amount from which to return the amount associated with the last entry by month and year.
date_range: A range that contains dates.
date: The month and year, in date format, which is used to identify the month and year.

GENERIC FORMULA

=LOOKUP(2,1/(TEXT(date_range,"mmmm yyyy")=TEXT(date,"mmmm yyyy")),amount_range)

EXPLANATION

This is an array formula that uses the LOOKUP and TEXT functions to return the last entry from a list by month and year.

The TEXT function is used to convert an array of string in date format to “mmmm yyyy” format. These values are then compared to the date parameter, which represents the month and year in the “mmmm yyyy” format. In this example the date is captured in cell E5 (cell reference) or directly entered into the formula as a specific date as January 2020 (hard coded). The result is an array of TRUE and FALSE values.

These values are then inserted into the LOOKUP function as the lookup vector, with the lookup value set to 2 and a result vector as the amount range. This will now return the amount that is associated with the last date by month and year.

Click on either the Hard Coded or Cell Reference button to view the formula that has the month and year directly entered into the formula or referenced to a specific cell.