Return most recent value with 1 criteria

[thematrix05]

Hi I want my formula to return the most recent value, based on 1 criteria I am using a MAXIF formula but it seems to not be working.

My formula is =MAXIFS(Table1[Amount],Table1[Category],F2,Table1[Date],MAX(Table1[Date]))

F2 = criteria

In cell H2 the answer should be "844"

Thanks

[HansDouwe]

To get 844, please try:

=MAXIFS(Table1[Amount],Table1[Category],F2,Table1[Date],G2)

[thematrix05]

Is there a way to get 884 without targeting G2?

[AliGW]

Try this:

=LET(d,MAXIFS(Table1[Date],Table1[Category],F2),MAXIFS(Table1[Amount],Table1[Category],F2,Table1[Date],d))

[thematrix05]

Worked perfect! Thank you!

[HansDouwe]

It is also possible to retrieve both values (date and amount) in one formula in many different ways:

One way

Please empty H2 and try in G2:

=LET(t,MAXIFS(Table1[Date],Table1[Category],F2),CHOOSE({1,2},t,XLOOKUP(t,Table1[Date],Table1[Amount])))

[AliGW]

You're welcome.