Get first entry by month and year

Generic formula

{=INDEX(entry,MATCH(TRUE,TEXT(date,"mmyy")=TEXT(A1,"mmyy"),0))}

Related formulas

Get last entry by month and year

Get value of last non-empty cell

Get date associated with last entry

Get first non-blank value in a list

Lookup latest price

Last row in numeric data

Last row in text data

Explanation

To lookup the first entry in a table by month and year, you can use and array formula based on the INDEX, MATCH, and TEXT functions.

the LOOKUP function with the TEXT function. In the example shown, the formula in F5 is:

=INDEX(entry,MATCH(TRUE,TEXT(date,"mmyy")=TEXT(E5,"mmyy"),0))

where "entry" is the named range C5:C13, "date" is the named range B5:B13, and E5 contains a valid date.

This is an array formula, and must be entered with control + shift + enter.

How this formula works

Note: the values in E5:E7 are actual dates, formatted with the custom number format "mmyy".

Working from the inside out, the expression:

MATCH(TRUE,TEXT(date,"mmyy")=TEXT(E5,"mmyy")

uses the TEXT function to generate an array of strings in the format "mmyy":

{"0117";"0117";"0117";"0217";"0217";"0217";"0317";"0317";"0317"}

which are compared a single string based on the value in E5, "0117". The result is an array of TRUE / FALSE values:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

which are fed into the MATCH function as the lookup array, with a lookup value of TRUE, and a match type of zero for exact match. In exact match mode, the MATCH function returns the position of the first TRUE in the array, which is 1 in the formula in F5. This position goes into INDEX as the row number, with an array based on the named range "entry":

=INDEX(entry,1)

Index returns the item inside entry at the specified position.

Note: if an entry isn't found for a given month and year, this formula will return #N/A.

First entry based on today's date

To get the first entry for a given month and year based on today's date, you can adapt the formula to use the TODAY function instead of the value in E5:

{=INDEX(entry,MATCH(TRUE,TEXT(date,"mmyy")=TEXT(TODAY(),"mmyy"),0))}