the LOOKUP function with the TEXT function. In the example shown, the formula in F5 is: 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. Working from the inside out, the expression: 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 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:
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.
