Exceljet

Quick, clean, and to the point

Get first entry by month and year

Excel formula: Get first entry by month and year
Generic formula 
{=INDEX(entry,MATCH(TRUE,TEXT(date,"mmyy")=TEXT(A1,"mmyy"),0))}
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))}
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.