Return first entry by year

This tutorial shows how to get the first entry by year using the INDEX, MATCH and TEXT functions

EXCEL FORMULA 1. Return first entry by year with INDEX, MATCH and TEXT functions

EXCEL

Hard coded formula
Return first entry by year
Cell reference formula
Return first entry by year
{=INDEX($C$5:$C$9,MATCH(TRUE,TEXT($B$5:$B$9,"yyyy")=TEXT("January 2020","yyyy"),0))}
{=INDEX($C$5:$C$9,MATCH(TRUE,TEXT($B$5:$B$9,"yyyy")=TEXT(E5,"yyyy"),0))}
GENERIC FORMULA

{=INDEX(amount_range,MATCH(TRUE,TEXT(date_range,"yyyy")=TEXT("year","yyyy"),0))}

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

GENERIC FORMULA

{=INDEX(amount_range,MATCH(TRUE,TEXT(date_range,"yyyy")=TEXT(year,"yyyy"),0))}

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

EXPLANATION

This is an array formula that uses the INDEX, MATCH and TEXT functions to return the first 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 date is captured in cell E5 (cell reference) or directly entered into the formula as a specific date as January 2020 (hard coded). In reference to the hard coded example the month doesn’t on the formula as long as you have identified the correct year. The result is an array of TRUE and FALSE values.
These values are then inserted into the MATCH function as the lookup array, with the lookup value set to TRUE and a match type of 0 (representing an exact match). The MATCH function will then return the position of the first TRUE value from the array of dates. This position is inserted into the INDEX function as the row number with the array of associated values to the date range to return the amount associated with this position which in this example represents the first entry by year.
Please note that this is an array formula and once you have entered the formula you will need to press the control + shift + enter keys simultaneously, which will convert the formula into an array formula and enclose the it in { }.

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

RELATED TOPICS

Related Topic Description Related Topic and Description
Sum values by month and year How to sum values by month and year
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

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
INDEX Function The Excel INDEX function returns a value that is referenced from a specified range
MATCH Function The Excel MATCH function searches for a specified value in a range and returns its position relative to the range
TEXT Function The Excel TEXT function returns a numeric value as text, in a specified format