Can't figure out a max if

[szita2000]

Hi guys.
For the love of my life I can't work out a Max if formula.

What I am trying to achieve here is to get the correspondent value from C to the latest date in column A.
My idea was to get an array of the same fruits, then with the max get the largest date.
Then try to match index match it.

Book1.xlsx
	A	B	C	D	E	F
1	12/06/2020	Apple	12		Apple	11
2	12/06/2020	Pears	22		Pears	11
3	12/06/2020	Oranges	32		Oranges	11
4	13/06/2020	Apple	13
5	13/06/2020	Pears	23
6	13/06/2020	Oranges	33
7	14/06/2020	Apple	11
8	14/06/2020	Pears	24
9	14/06/2020	Oranges	34
Sheet1

Cell Formulas
Range	Formula
F1:F3	F1	=INDEX($C:$C,MATCH(MAX(IF($B:$B=$E1,$A:$A,"")),$A:$A,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Phuoc]

Change to:

=INDEX($C:$C,MATCH(MAX(IF($B:$B=$E1,$A:$A,"")),IF($B:$B=$E1,$A:$A,""),0))

 

[szita2000]

Thanks! This is working.
I was close.

 

[Peter_SSs]

@szita2000
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 with the FILTER & SORT functions, you could also try something like below - column F.
I have also included another option in column G, not requiring Excel 365) but assuming your data is sorted by date as per your sample..

If you are sticking with INDEX/MATCH/MAX formula I would recommend not using whole column references. I doubt you are using anything like 1,000,000+ rows and asking Excel to compare that many values in column B with the value in column E (twice) is consuming a lot more resources that most likely required. Even when I copy that formula down just the 3 rows I can see a noticeable lag before the results appear.

20 07 07.xlsm
	A	B	C	D	E	F	G
1	12/06/2020	Apple	12		Apple	11	11
2	12/06/2020	Pears	22		Pears	24	24
3	12/06/2020	Oranges	32		Oranges	34	34
4	13/06/2020	Apple	13
5	13/06/2020	Pears	23
6	13/06/2020	Oranges	33
7	14/06/2020	Apple	11
8	14/06/2020	Pears	24
9	14/06/2020	Oranges	34
10
Latest

Cell Formulas
Range	Formula
F1:F3	F1	=INDEX(SORT(FILTER(A$1:C$20,B$1:B$20=E1),1,-1),1,3)
G1:G3	G1	=INDEX(C$1:C$20,AGGREGATE(14,6,(ROW(C$1:C$20)-ROW(C$1)+1)/(B$1:B$20=E1),1))

 

[szita2000]

Thanks Peter. Added the missing info. ANd the 365 solution is Brilliant!
Many thanks again!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

And thanks for updating your profile.