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))}
Explanation
Note: the lookup_value of 2 is deliberately larger than any values in the lookup_vector, following the concept of bignum .
Working from the inside out, the expression:
(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy"))
generates strings like “0117” using the values in column B and E, which are then compared to each other. The result is an array like this:
{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
where TRUE represents dates in the same month and year. The number 1 is then divided by this array. The result is an array of either 1’s or divide by zero errors (#DIV/0!):
{1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
which goes into LOOKUP as the lookup array. LOOKUP assumes data is sorted in ascending order and always does an approximate match. When the lookup value of 2 can’t be found, LOOKUP will match the previous value, so lookup will match the last 1 in the array.
Finally, LOOKUP returns the corresponding value in result_vector, which contains the amounts in C5:C13.
