Find max date with three conditions met

[avk]

I using 2013 excel.
I have two sheets
In one sheet our data record more than 15000 rows
In sheet2 i required max date with three conditions met
Conditions : Part number, Branch, Type
In sheet2 total record more than 5000 rows, hence i required faster formula.
File attach : Yellow cell required output.

[Pete_UK]

You can use this array* formula:

=MAX(IF((A2=Sheet1!$D$2:$D$15)*(B2=Sheet1!$A$2:$A$15)*(C2=Sheet1!$C$2:$C$15),Sheet1!$B$2:$B$15))

*Note that an array formula needs to be confirmed using the key combination Ctr-Shift-Enter rather than the usual Enter.

If you had a later version of Excel you could use the faster MAXIFS function.

Hope this helps.

Pete

[MarvinP]

Hi avk,

This is an array (CSE) answer/problem. See the attached for the answer.
Find Max Date using CSE Answer.xlsx

Edit: I don't think the "IF" is needed in the answer above mine.

[kvsrinivasamurthy]

In D2 copy down

Please Login or Register  to view this content.

[avk]

Thanks to all. Its working.
But calculating processor (4) taken more time, Running very slow.
Hence we go with Pivot. File attached.
Once again thanks to every one.

[Czeslaw]

Power Query

Please Login or Register  to view this content.

[CARACALLA]

Sheet2

D2=AGGREGATE(14,6,Table1[Transaction Date]/(Sheet1!D2:D16=Sheet2!A2)/(Table1[Branch Code]=Sheet2!B2),1)

[kvsrinivasamurthy]

If dates are in ascending order for for each part number you can try. This is faster than Aggregate formula.

Please Login or Register  to view this content.