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.

Generic formula



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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.