Return first entry by month and year

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

Excel

Return first entry by month and year using INDEX, MATCH and TEXT functions

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

EXCEL

Hard coded formula

Cell reference formula

{=INDEX($C$5:$C$9,MATCH(TRUE,TEXT($B$5:$B$9,"mmmm yyyy")=TEXT("1 January 2020","mmmm yyyy"),0))}

{=INDEX($C$5:$C$9,MATCH(TRUE,TEXT($B$5:$B$9,"mmmm yyyy")=TEXT(E5,"mmmm yyyy"),0))}

GENERIC FORMULA

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

ARGUMENTS
amount_range: A range that contains the amount from which to return the amount associated with the first 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

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

EXPLANATION

This is an array formula that uses the INDEX, MATCH and TEXT functions to return the first 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 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 month and 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 formula in { }.

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 reference to a specific cell.

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