Forum Discussion
Finding Most Recent Amount Per Person
I have a set of data with unique identifiers for each person, the dates and amounts of each amount given during a time frame. Some people have given more than once in the time frame. I want to obta...
Hello,
you could build a pivot table with the person ID in the rows and the date in the values. Then set the value field to show Max. That will give you the person ID next to the last date against their name.
Now you can use an Index/Match with a combined match of name and date to pull the amount.
=INDEX($C$2:$C$15,MATCH(E4&F4,INDEX($A$2:$A$15&$B$2:$B$15,0),0))
Ingeborg, that's not as a comment to your solution, just started my exercise before seen your post.
This one is with array formulas.
In E2 we find distinct ID
=IFERROR(INDEX($A$2:$A$8,MATCH(0,COUNTIF($E$1:E1,$A$2:$A$8),0)),"")
In F2 max for it date
=IFERROR(LARGE(IF($A$2:$A$8=E2,$B$2:$B$8),1),"")
In G2 related to this combination amount
=IFERROR(INDEX($A$2:$C$8,MATCH(1,($A$2:A$8=$E2)*($B$2:$B$8=$F2),0),3),"")
(all above formulas are arrays one with Ctrl+Shift+Enter)
after that copy them down till spaces appear.
