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).

Officer	Date	System	Category	Amount
Adam	5/1/19	CL	Total Open Loans	$51,000,000
Adam	6/1/19	CL	Total Open Loans	$52,000,000
John	5/1/19	CL	Total Open Loans	$12,000,000
John	7/1/19	CL	Total Open Loans	$11,000,000

<tbody>
</tbody>

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)