Exceljet

Quick, clean, and to the point

Get last entry by month and year

Excel formula: Get last entry by month and year
Generic formula 
=LOOKUP(2,1/(TEXT(dates,"mmyy")=TEXT(A1,"mmyy")),values)
Explanation 

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:

=LOOKUP(2,1/(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy")),$C$5:$C$13)

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:

(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. 

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.