I have a table in Excel which has the columns:
The table is used to record when people pay me money. Typically, I can get more than one person paying me on the same day. Also, the same person will pay me on many days over the course of time.
Records are added to the bottom of the table so the ordering will be on date but no further ordering on name or amount.
Using formulas, is there a way I can retrieve the most recent amount for a specific person?
If this is too complicated or not possible then I can settle for the following work around: Add a 4th column to the table called "Last". This will display TRUE if it is the last entry for a specific person, FALSE if it is not.
It felt like there should be a fairly straight-forward answer to this but I found it quite a head scratcher so was interested to see an answer.
Having done some googling I came across a solution posted on a dedicated excel site (view here). [NB - look under the header 'Arbitrary Lookups']
Applying it to your example, suppose your data is in A1:C10 and in cell D2 you want to type a name and return the most recent payment in cell D3:
1 Date Name Amt EnterName
2 20 Jul Bob 50 <enter name here>
3 13 Sep Susan 20 = enter formula here (see below)
4 06 Jan Xavier 100
In cell D3 enter the following as an array formula (i.e. type in formula and then press CTRL + SHIFT + ENTER
=INDEX($B$2:$C$10,SMALL(IF(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1)=$D$2, ROW(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1))-ROW(OFFSET($B$2:$C$10,0,0,1,1) )+1, ROW(OFFSET($B$2:$C$10,ROWS($B$2:$C$10)-1,0,1,1))+1),COUNTIF(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1),$D$2)),2)
It would recommend checking out the link I provided if you want more detail. For clarity, I have merely adapted the formula (changed cell references) from the link provided.