I am looking to return the value for the most recent/latest date for each type (Type A, Type B). I have tried several formulas and combination of formulas.

Formulas I have tried (Using Type A as an example)

• DMAX : =IF(A2="Type A",DMAX(A:C,"Value",B:B),0) returns 814
• VLOOKUP : =IF(A2="Type A", VLOOKUP(MAX(B:B),C:C,1,0),0) returns #N/A
• MAX IF : {=MAX(IF(A:A="Type A",C:C,""))} returns 703

The values that should be returned are:

• Type A: 219, the value for 01/05/21 which is the most recent date
• Type B: 505, the value for 01/05/21 which is the most recent date

This is the data I'm working with:

Dates are in U.S. format MM/DD/YY for reference.

Thank you

CodePudding user response：

If you have the newest version of Excel you can use FILTER. First get the unique Types with =UNIQUE($A$2:$A$11), then use something like:

=FILTER($C$2:$C$11,
(($B$2:$B$11=MAX(FILTER($B$2:$B$11,($A$2:$A$11=E1))))*
($A$2:$A$11=E1)))

For example:

CodePudding user response：

I'm sure there are other ways, but I managed to get a similar result by splitting up my formula a little (Note, I randomized my dates some to provide a better test).

I have a column for each type, and one that finds the max date. Then I use a SUMPRODUCT formula for the final look up. This is what it looks like.

And then the final result

It's still not perfect but it works using reasonably standard formulas and doesn't rely on anything specific to Excel (which is useful for me).

So my spreadsheet looks like this: