How to get the newest value from a column with conditions

I have a table in Excel which has the columns:

1. Date
2. Person Name
3. Amount (£)

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开发者_StackOverflow中文版 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.

---

Here's one way to do it based on an answer I gave in a previous SO post.

=INDEX($C$1:$C$19,MATCH(MAX(IF($B$1:$B$19="PersonNameHere",$A$1:$A$19,0)),IF($B$1:$B$19="PersonNameHere",$A$1:$A$19,"")))

Where A is the Date column, B is the Person Name column, and C is the Amount column. You must enter this as an array formula by pressing Ctrl+Shift+Enter.

---

I can't see right here an easy way to do it in only one formula.

If you have your data from A2 to C11.
You can add this formula on the 4^th column (let say in cell D2):

{=MAX(IF($B$2:$B$11=B2,$A$2:$A$11,0))}

This is an array formula you have to validate with Ctrl-Shift-Enter
This will tell you what is the last date for the current person.

And then find the last amount with another column (let say in cell E2) and use this formula:

=INDEX($C$2:$C$11,MATCH(D2,$A$2:$A$11,0))

[EDIT] I've just tried to combine the formulas into one and that simply works:

{=INDEX($C$2:$C$11,MATCH(MAX(IF($B$2:$B$11=B2,$A$2:$A$11,0)),$A$2:$A$11,0))}

and this is still an array formula.

Alas, @Excellll was smarter (and faster) and gave the solution at first shot