Formula to find last entry in a list based on some criteria

Hi, Really need help on this as I've been scratching my head for 2 days so far, and seem to have tried almost every solution that's appeared on Google. I've attached an example workbook.

I have a spreadsheet that tracks some company performance in the form of the metric name, some date fields, the actual result from the previous weeks, and the forward looking forecast. The issue I have is that the forecast line needs to automatically find the last weekly entry, and project the forecast from that point. Note, i already have a vlookup system that pulls me the values for the forecast line that determine the increases or decreased in the forecast, what i can't do is marry up the end of the actuals, with the start of the forecast to add the last actual number to the forecast figures. To avoid issues with people mixing up the row orders if they do filters or sorts, the formula has to be able to find the last entered item in the last for that specific metric, regardless of whether its jumbled or in order. Best to see the workbook to see what I mean.

Would greatly appreciate help on this as I'm stuck having to use Excel for this currently and not a database.

Thanks in advance. Matt.