Formula for pulling Month End Balances

[bullwinkle55423]

I have one table with a list of daily balances. Column A is a list of all dates, Column B & C have balances.

I have another table with a list of month end dates (1/31/11, 2/28/11, 3/31/11, etc..)

To set up a formula to pull in the month-end balance, the obvious answer would be a VLookup formula. However, the first table does not have any balances for weekend dates or holidays. So, if the last day of January is a Saturday, I'll want to pull in the 1/30 balance instead of the 1/31 balance.

Also, for the current month, I'll want to pull in the last day that has balances. So, if today is 11/22 and the last day that data was entered was for 11/21, I'll want to see 11/21 data instead of 11/30.

Is there an option for poulling the data without manually changing the dates in the 2nd table?

Thanks.

[Richard Buttrey]

Hi,

Use an =INDEX(MATCH()) compound formula. e.g.

Please Login or Register  to view this content.

Regards

[royUK]

A PivotTable might be better, group by months