Multiple column query to return value of third column

[mattfj]

I am trying to write a formula that matches criteria in one column, that corresponds with a maximum date less than today in a second column, and returns the corresponding value from the third column. I've tried various combinations of VLOOKUP, MAX, INDEX, MATCH, SUMPRODUCT, AND, IF and I've gotten fairly close but I am stumped. Please help. Thanks.

 

[Peter_SSs]

Note for the future: You will generally get faster/better responses in the forum if you provide your sample data with XL2BB and include the expected results.

See if this does what you want.
You mentioned that the data must be less than today but did not include any examples where the date is >= today
If the dates were all earlier than today like your samples then the formula could be simpler.
If you do have any follow-up questions it would also be useful to know if the data is always grouped by column A and with increasing dates for each group in column B like your samples.

20 12 12.xlsm
	A	B	C	D	E	F	G
1	Fruit	Date	Description		Fruit		Answer
2	Apple	1-Dec-20	Good		Apple		Crunchy
3	Apple	3-Dec-20	Sweet		Orange		Citric
4	Apple	8-Dec-20	Crunchy		banana		Soft
5	Orange	1-Dec-20	Sour
6	Orange	3-Dec-20	Citric
7	Banana	8-Dec-20	Soft
8	Banana	5-May-21	Unknown
Most Recent

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX(C:C,AGGREGATE(14,6,ROW(C$2:C$8)/((A$2:A$8=E2)*(B$2:B$8=AGGREGATE(14,6,B$2:B$8/((A$2:A$8=E2)*(B$2:B$8<TODAY())),1))),1))