## Summary

To look up the first entry in a table by month and year, you can use a formula based on the INDEX, MATCH, and TEXT functions. 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:C16, "date" is the named range B5:B16, and cell E5 contains a valid date formatted with the custom format "mmm".

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

## Generic formula

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

## Explanation

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

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 to 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}``

This array is 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)
``````

Finally, INDEX returns the item inside entry as a final result.

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

### Get the 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

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.