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

[matthewpaver]

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.

[icestationzbra]

if i understand your requirement correctly, place the following formula in cell G2 and drag-fill down:

Please Login or Register  to view this content.

[Pete_UK]

Not sure why you have a lot of NA() in your sheet, but if I understand your requirements correctly, you could put this array* formula in G14:

Please Login or Register  to view this content.

and then copy it down.

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

Hope this helps.

Pete

[matthewpaver]

Thanks pete_uk, your solution seems to work when i mix the rows up in the test workbook, but when i bring it over to my spreadsheet the results are quite strange i.e. instead of the 7.9% i get in the attached workbook which is correct, i get a wildly different number over 3 million. attaching a snippet of the main spreadsheet i'm working in "Book2".

[matthewpaver]

Part of the problem solved. it was returning a number from elsewhere in the sheet. because the group of rows that contain the target metrics data started at row 900+ of the 14,000 rows, the formula was counting the number of entries that metric had, and was returning the number using that count from the very start of the 14,000 rows. so it was returning row 72 i think instead of 900+. The fix was to do a match first on the position of the metric to get around 900, then added this to the existing match formula to give me the exact row needed.

However, because of the fix above, the overall formula still can't detect the last known entry when you mix all the rows up. instead, it finds the first known entry of the target metric which could be row 2000, then adds on the number of entries the metric has in the whole mixed table, which could end up on 2070. But 2070 could be a completely different metric. The last known entry could actually be on line 10.

So the challange it seems is still to make the formula find the last known entry, maybe based on the week end date, in a mixed up table, not a static one. Sorry if it's all a bit confusing, it is for me too, and i used to think i was reasonable at Excel

[matthewpaver]

I think the original formula is only finding the most recent week end date in the entire column, but these sets of dates are repeated for each metric, and there's over a hundred metrics in the sheet. so, not sure if there's a way to make the formula only take the end date when the metric type is correct. so using the example above, it's returning the value adjacent to the most recent date that has a numerical value adjacent to it, which is row 72 in my sheet, where as it should only do that where the metric is the target one.

[Pete_UK]

I put this formula in P48:

Please Login or Register  to view this content.

(with CSE) and copied it down, and it gave the results I expected, though clearly I can't simulate your real sheet with many thousands of rows. All I can suggest is that you look carefully at your entries in column O, maybe with a formula like =MAX(O:O) and/or using filters on that column to see where the 3 million comes from. Note that you don't need IF(ISNA(O48)=TRUE, ...

The MAX(IF part of the formula is finding the latest date for each Measure where the Actual is not an error, and the MATCH function finds the row where that occurs, and the INDEX function is returning the corresponding Actual value for that largest date. However, it occurs to me that if you have 2 dates the same, and the first date is for a different Measure (with a different Actual), then the MATCH part of the formula will find the first of those dates, and so might be returning the wrong Actual value.

Hope this helps.

Pete

[Pete_UK]

Okay, I think you need to put this array* formula in P6:

Please Login or Register  to view this content.

(* entered with CSE, as before). You will need to change the ranges to suit your data, and then copy down. I've indicated the changes in red, so it now matches not only on the latest date, but also on the Measure.

Hope this helps.

Pete

[matthewpaver]

Well, the latter seems to work as needed. Thanks very much, you've saved my sanity and a few more head scratching days at work.

[Pete_UK]

Glad to hear it worked for you. Please mark the thread as Solved if you consider it to be so (click on Thread Tools just above your first post).

Also, you can pass on thanks more directly by clicking on the "star" icon in the bottom left corner of any post you have found to be helpful - not just on this thread.

Pete