MAX Lookup Help

[Golfpro1286]

I am working on a scorecard and for a portfolio section of it I want to either calculate or lookup the most recent dollar amount of total open loans of the officer in cell C7. The table below (Located in a separate sheet) is named "Port". The expected result is $52,000,000 (Adam, most recent date 6/1/19).

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Officer[/TD]
[TD]Date[/TD]
[TD]System[/TD]
[TD]Category[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]5/1/19[/TD]
[TD]CL[/TD]
[TD]Total Open Loans[/TD]
[TD]$51,000,000[/TD]
[/TR]
[TR]
[TD]Adam[/TD]
[TD]6/1/19[/TD]
[TD]CL[/TD]
[TD]Total Open Loans[/TD]
[TD]$52,000,000[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]5/1/19[/TD]
[TD]CL[/TD]
[TD]Total Open Loans[/TD]
[TD]$12,000,000[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]7/1/19[/TD]
[TD]CL[/TD]
[TD]Total Open Loans[/TD]
[TD]$11,000,000[/TD]
[/TR]
</tbody>[/TABLE]

C7: Adam



I first tried a SUMIFS formula but the MAX of the date did not work because it just gave the most recent date in the entire date column.

=SUMIFS(Port[Amount],Port[Officer],$C$7,Port[System],"CL",Port[Category],"Total Open Loans",Port[Date],MAX(Port[Date])

Is there an adjustment to this to make it work without being an array formula that requires ctrl+shift+enter to use or possibly an index formula to look it up?

Thanks!

 

[DanteAmor]

Try this

=SUMPRODUCT((Port[Officer]=C7)*(Port[System]="CL")*(Port[Category]="Total Open Loans")*(Port[Date]=LOOKUP(2,1/((Port[Officer]=C7)*(Port[System]="CL")*(Port[Category]="Total Open Loans")),Port[Date]))*(Port[Amount]))

 

[Eric W]

Just to show the many options of Excel, here are a few more options:

Use this one if the loans are in date order by officer. It essentially finds the last entry by an officer.

=LOOKUP(2,1/(Port[Officer]=C7)/(Port[System]="CL"),Port[Amount])

If you have the MAXIFS function, you can adapt your existing formula as follows:

=SUMIFS(Port[Amount],Port[Officer],$C$7,Port[System],"CL",Port[Category],"Total Open Loans",Port[Date],[COLOR=#ff0000]MAXIFS(Port[Date],Port[Officer],C7,Port[System],"CL")[/COLOR])

Here's another option, but requires that your amounts are under $10 billion dollars:

=MOD(AGGREGATE(14,6,(Port[Date]*10^10+Port[Amount])/(Port[Officer]=C7)/(Port[System]="CL"),1),10^10)