Array Formula: Can anybody help?

[thingssocomplex]

Hi All - I have an issue with an array formula that I am not sure how to resolve; I am hoping somebody on here could assist or point me in the right direction. I have a table that stores

'PaymentDate', 'UserName', 'PaymentAmount'.
31/10/2010 XYZ £100
31/05/2011 XYZ £200
31/10/2010 ABC £150

Using the below formula if I select 'XYZ' from the dropdown it will correctly return £200 (the value for the MAX Date) however if I select 'ABC' it returns £0... I think this is occuring because I am looking at the MAX Date and it can't identify the username against it...

Can anybody help?

=SUM(IF((PaymentHistory!B2:B16=E7)*(PaymentHistory!A2:A16=MAX(PaymentHistory!A2:A16)),PaymentHistory!C2:C16))

[zbor]

try this array (ctrl+shift+enter)

=INDEX(PaymentHistory!C2:C16,MATCH(MAX(IF(PaymentHistory!B2:B16=E7,PaymentHistory!A2:A16))&E7,PaymentHistory!A2:A16&PaymentHistory!B2:B16,0))

[thingssocomplex]

Amazing, how on earth did you know how to do that?

[zbor]

Lucky guess

Note that this will bring first value (for multiply values of same payment date).

I would use Pivot Table if I were you.