Return last entry by year

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

Excel

Return last entry by year using LOOKUP and TEXT functions

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

EXCEL

Hard coded formula

Cell reference formula

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

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

GENERIC FORMULA

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

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

GENERIC FORMULA

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

EXPLANATION

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

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

Click on either the Hard Coded or Cell Reference button to view the formula that has the year 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
Return first entry by year	How to get the first entry by year