Return last entry by month

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

Excel

Return last entry by month using LOOKUP and TEXT functions

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

EXCEL

Hard coded formula

Cell reference formula

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

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

GENERIC FORMULA

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

ARGUMENTS
amount_range: A range that contains the amount from which to return the amount associated with the last entry by month.
date_range: A range that contains dates.
month: The month for which to return the last entry from a list.

GENERIC FORMULA

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

EXPLANATION

This formula uses the LOOKUP and TEXT functions to return the last entry from a list by month.

The TEXT function is used to convert an array of string in date format to “mmmm” format. These values are then compared to the date parameter, which represents the month in the “mmmm” format. In this example the month is captured in cell E5 (cell reference) or directly entered into the formula as January (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.

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

Related Topic	Description	Related Topic and Description
Return first entry by month and year	How to get the first entry by month and year
Return first entry by month	How to get the first entry by month
Return last entry by month and year	How to get the last entry by month and year