Lookup latest date based on criteria (no arrays)

In the attached workbook, I would like to return the latest date for Fruit Deliveries from the Fruit Deliveries worksheet onto the Fruit Order tab based on the Sales Order criteria.

Right now, if I had a working formula, D2 would read "01/2020" as that is the latest date that we are receiving apples that are allocated to Sales Order 10.

The date format is what our ERP provides.

This is someone else's sheet and for some reason I cannot use array formulas (other options like Conditional Formatting are also greyed out), so I need to avoid arrays.

Right now I am sorting the entire dataset by date descending so the index/match picks up the first result which is the latest, but my team reads these sheets earliest to latest so that's not a longterm solution.

Originally Posted by CARACALLA

Put real dates in your example

Trying to avoid this as the given format is what the ERP provides, and the person using the sheet will struggle with remembering to run the text to columns function.

Originally Posted by FlameRetired

Do you wish to avoid array formula to avoid Cntrl + Shift + Enter or is there another reason?

Banking on the answer is "Yes" try this

Formula:

Please Login or Register  to view this content.

For some reason, this sheet has some type of protection or rule which is stopping me from using an array (no change after control+shift+enter). I use them often in my own work but this is someone else's shared sheet so I don't know why it's locked down.

I will give that formula a try today, thanks! If all is good, I will let you know and bump your rep.

	A	B	C	D
1	Material	Sales Order	Fruit Delivery	Date Delivery
2	Apples	10	49/2019	2/18/2019
3	Apples	10	50/2019	2/19/2019
4	Apples	10	01/2020	1/1/2020
5	Apples	10	51/2019	2/20/2019

	A	B	C	D
1	Name	Material	Sales Order	Fruit Delivery
2	John's Market	Apples	10	1/1/2020
3	John's Market	Pears	20
4	John's Market	Apples	30
5	John's Market	Cherries	40

Fruit Deliveries

D2=IF(C2<>"",DATE(RIGHT(C2,4),1,LEFT(C2,2)),"") copy down change Erp date in Excel date


Fruit Orders



D2=IF('Fruit Orders'!$B2<>"",IFERROR(AGGREGATE(14,6,('Fruit Deliveries'!$D$2:$D$100)/('Fruit Deliveries'!$A$2:$A$100='Fruit Orders'!$B2)/('Fruit Deliveries'!$B$2:$B$100='Fruit Orders'!$C2),1),""),"")

copy down