Returning Indexed Values of Each Row for Latest Date

Iva

Hello, I am having trouble trying to return Current Value for each row corresponding to each name and for the latest date.

Attached is a simplified table:

Instead of manually inputting the 'Current Value', I would like to create a formula to return the latest cumulative value for each name row. I have tried using INDEX with MATCH and MAX but could not get it working for these two criteria (match name and max date). Please help and let me know whether this is doable. Thanks!

PS: To derive Cumulative Value I used the formula [Transaction Value]@row + SUM (ANCESTORS())

Iva

Update: I managed to get this solved by using SUMIFS instead 😀

Bassam Khalil

Hi @Iva

Hope you are fine, you need to add a helper column call it "Max Value", the following is the formulas:

1- Max Value = MAX([Transaction Value]@row:[Cumulative Value]@row) convert it to column formula

2- Current Value =MAX(COLLECT([Max Value]:[Max Value], Name:Name, Name@row)) convert it to column formula

the following screenshot shows the result

Iva

Hello @Bassam.M Khalil

Thank you for your reply. I tried it on my actual worksheet, by using helper column and MAX with COLLECT the value to be picked up would be the biggest one (instead of the value with the latest date). When there is a negative value unfortunately this method does not work.

Bassam Khalil

@Iva

As i understand from the screenshot in your post that you need the max Value of Transaction Value & Cumulative Value. check the screenshot in your post, and please post a sample show the value you need manually and i will try to write the formula for you

Iva

@Bassam.M Khalil

Please refer to my original post again, the highlighted part in my screenshot is the one that I asked (Current Value, not Cumulative Value); my aim is to show current value for each row corresponding to each name.

Iva

Update: I managed to get this solved by using SUMIFS instead 😀

Bassam Khalil

@Iva

Perfect 👍️