How to use index and arrays for a nested query?

[annechez]

Hi,

I have a table as follows:

I am trying to return the latest date for a given Fruit and Type - can anyone help.

i.e. Pear with a Type of AI_646 should return a date of 10/10/2020.

Can anyone help.

I have to use my work version of Excel which is Office 2010, so I can't use the latest features.

Any guidance would be amazing, I'm going round in circles and never coming out with the correct answer!

Fruit	Type	Date
Apple	AI_840	10/03/2020​
Pear	AI_646	01/03/2020​
Pear	AI_840	12/12/2020​
Apple	AI_121	05/05/2020​
Orange	AI_123	06/06/2020​
Pear	AI_646	10/10/2020​
Pear	AI_646	07/09/2020​

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D	E	F	G
1	Fruit	Type	Date		Fruit	Type	Latest Date
2	Apple	AI_840	2020/3/10		Pear	AI_646	2020/10/10
3	Pear	AI_646	2020/3/1
4	Pear	AI_840	2020/12/12
5	Apple	AI_121	2020/5/5
6	Orange	AI_123	2020/6/6
7	Pear	AI_646	2020/10/10
8	Pear	AI_646	2020/9/7
Sheet6

Cell Formulas
Range	Formula
G2	G2	=MAX((A2:A8=E2)*(B2:B8=F2)*C2:C8)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[annechez]

That's fantastic, thank you.

If I wanted to extend the above nd return the value in the "Notes" column for a latest date of a Pear, of Type AI_646 (i.e. return fff), what would I do?

Fruit	Type	Date	Notes
Apple	AI_840	10/03/2020​	aaa
Pear	AI_646	01/03/2020​	bbb
Pear	AI_840	12/12/2020​	ccc
Apple	AI_121	05/05/2020​	ddd
Orange	AI_123	06/06/2020​	eee
Pear	AI_646	10/10/2020​	fff
Pear	AI_646	07/09/2020​	ggg

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D
1	Fruit	Type	Date	Notes
2	Apple	AI_840	2020/3/10	2020/3/10
3	Pear	AI_646	2020/3/1	2020/10/10
4	Pear	AI_840	2020/12/12	2020/12/12
5	Apple	AI_121	2020/5/5	2020/5/5
6	Orange	AI_123	2020/6/6	2020/6/6
7	Pear	AI_646	2020/10/10	2020/10/10
8	Pear	AI_646	2020/9/7	2020/10/10
Sheet6

Cell Formulas
Range	Formula
D2:D8	D2	=MAX((A$2:A$8=A2)*(B$2:B$8=B2)*C$2:C$8)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[annechez]

Apologies - I don't think I was very clear in my ask above.

What I'm looking for is return the contents of cell D7 (fff in my example above) If I have the conditions latest date, Pear and AI_646

??

 

[shaowu459]

I should say sorry, please see if below formula works:

MrExcel.xlsx
	A	B	C	D	E	F	G	H
1	Fruit	Type	Date	Notes		Fruit	Type	Notes
2	Apple	AI_840	2020/3/10	A		Pear	AI_646	F
3	Pear	AI_646	2020/3/1	B
4	Pear	AI_840	2020/12/12	C
5	Apple	AI_121	2020/5/5	D
6	Orange	AI_123	2020/6/6	E
7	Pear	AI_646	2020/10/10	F
8	Pear	AI_646	2020/9/7	G
Sheet6

Cell Formulas
Range	Formula
H2	H2	=INDEX(D:D,MOD(MAX((A$2:A$8=F2)*(B$2:B$8=G2)*(C$2:C$8*10^4+ROW(2:8))),10^4))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[annechez]

Does that mean I can't do it in a single formula and I have to break it out into 2 steps?

 

[shaowu459]

Does that mean I can't do it in a single formula and I have to break it out into 2 steps?

please refer to post #6, I combined into one formula.

 

[annechez]

Totally - amazing and it really helps me out. Thank you.

Although I must say, I'm not entirely show how it does it... my mind has been blown

 

[shaowu459]

Totally - amazing and it really helps me out. Thank you.

Although I must say, I'm not entirely show how it does it... my mind has been blown

You are welcom