Find last occurrence in an array

[Abisay]

Hello!

I have a list of dates and account balances. I want to retrieve the balance of the last day of each month. The last day on the list is not always the last day of the month because we don't work every single day, and I don't want to add rows to the spreadsheet. I have added helper columns B, C, D, and H, that could be hidden or deleted if not needed. I was not able to match the years and months with the last day of the month on the list and return the balance, using combinations of index, match, lookup, etc.

What formula do I write in J2:L13 to return the last balance on each month?

Thank you!

[JeteMc]

This solution adds one more helper column, although it doesn't use column B. F2 and down is populated with the formula:

Please Login or Register  to view this content.

Paste the following array formula in J2, press the F2 key then press Ctrl, Shift and Enter simultaneously:

Please Login or Register  to view this content.

Pull the fill handle across and then down.
Let me know if you have any questions.

[Bernie Deitrick]

Try this - the formula is array entered (entered using Ctrl-Shift-Enter) - I have removed all of your helper columns since they are not needed, and used this in cell G2, copied to all the other cells in the table.

=INDEX($B:$B,MAX(IF(YEAR($A2:$A2000)=G$1,IF(MONTH($A2:$A2000)=$E2,ROW($A2:$A2000)))))

[AlKey]

Hi Abisay,

Enter this formula in J2, copy across and down

Please Login or Register  to view this content.

[Bernie Deitrick]

Your formula would return the largest balance for the month and year rather than the balance on the last recorded day for that period.

[AlKey]

I guess, I missed this part

array formula in J2 and copy across and down

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[AlKey]

Or this regular formula

Please Login or Register  to view this content.

[Bernie Deitrick]

Nice formula - and without the helper columns, it could be this: columns B,C, and D can then be deleted:

=IFERROR(LOOKUP(2,1/(MONTH($A$2:$A$561)&YEAR($A$2:$A$561)=$H2&J$1),$E$2:$E$561),"")

[AlKey]

Nice formula - and without the helper columns, it could be this: columns B,C, and D can then be deleted:

=IFERROR(LOOKUP(2,1/(MONTH($A$2:$A$561)&YEAR($A$2:$A$561)=$H2&J$1),$E$2:$E$561),"")

I just used the OP's original sheet.