Compare unique values with multiple columns and return the most recent

[Rodri]

	A	B	C	D
1	Fruit	List	Date	Condition
2	Banana	Banana	02/05/2010	Good
3	Tomato	Banana	02/05/2011	Good
4	Orange	Banana	02/05/2012	Bad
5	Lemon	Tomato	02/05/2014	Excellent
	(always unique)	Tomato	02/05/2010	Good
		Banana	02/05/2014	Available

<tbody>
</tbody>

I would like to compare one-by-one the items in column A with column B, then return what's in column D for the most recent date in column C for that item.

E.g.: For "Banana" (A2) - Result = Available

For "Tomato" (A3) - Result = Excellent

I tried some INDEX with MATCH, but I can't get the correspondent MAX value.


Thanks

 

[Marcílio_Lobão]

Rodri, Good evening.

Try to use:

=INDIRECT("D"&SUMPRODUCT(($B$2:$B$7=$A$2)* ($C$2:$C$7= MAX($C$2:$C$7,1)) * ROW($D$2:$D$7)),1)

Is it what you want?
I hope it helps.

Have a nice weekend.