Return first entry by month

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

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

EXCEL

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

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

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

GENERIC FORMULA

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

ARGUMENTS
amount_range: A range that contains the amount from which to return the amount associated with the first entry by month.
date_range: A range that contains dates.
month: The month 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 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 date is captured in cell E5 (cell reference) or directly entered into the formula as a specific date as January (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.
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 month directly entered into the formula or reference 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

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