Get last entry by month and year
To lookup the last entry in a table by month and year, you can use the LOOKUP function with the TEXT function. In the example shown, the formula in F5 is:
where B5:B13 and E5:E7 contain valid dates, and C5:C13 contains amounts.
How this formula works
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:
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:
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!):
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.