formula to extract the records with oldest date and another criteriia

[GerryZ]

hello Excel gurus
I'm stuck
All I need a formula in F2 to copy across and down that choose
from the first criteria (AAA) the oldest date in (AAA) and than ritrieve the name of the student and the corrisponding date
I Made a formula with INDEX MAX IF but does't work

Book1
	A	B	C	D	E	F	G
1	criterio	student	datea		criterio	student	oldest data
2	aaa	student1	15/01/2015		aaa	student2	17/01/2018
3	aaa	student2	17/01/2018		bbb	student5	22/01/2016
4	aaa	student3	16/01/2015		ccc	student11	12/04/2016
5	bbb	student4	15/01/2014
6	bbb	student5	22/01/2016
7	bbb	student6	18/01/2015
8	bbb	student7	18/01/2014
9	bbb	student8	21/01/2014
10	ccc	student9	07/06/2015
11	ccc	student10	14/07/2015
12	ccc	student11	12/04/2016
13	ccc	student12	13/02/2015
14	ccc	student13	11/03/2015
Sheet5

 

[steve the fish]

Maybe this array formula:

=MAX(IF($A$2:$A$14=E2,IF($B$2:$B$14=F2,$C$2:$C$14)))

 

[GerryZ]

Maybe this array formula:

=MAX(IF($A$2:$A$14=E2,IF($B$2:$B$14=F2,$C$2:$C$14)))

hello Steve
I F2 I need the formula
to copy in G2
and down

 

[steve the fish]

That will copy down ok? Put it in G2 and pull down to G4. Is that not working for you?

 

[XOR LX]

Or, a touch convoluted maybe, but if you want a single, non-array formula to be placed in F2 and then copied down and across:

=LOOKUP(1,0/FREQUENCY(0,1/(1+($A$2:$A$14=$E2)*$C$2:$C$14)),CHOOSE(COLUMNS($A:A),$B$2:$B$14,$C$2:$C$14))

Regards

 

[GerryZ]

I make clear
I need to extract data making one formula
I need in this way
one single formula in F2 to copy across and down

Book1
	A	B	C	D	E	F	G
1	criterio	student	datea		criterio	student	oldest data
2	aaa	student1	15/01/2015		aaa
3	aaa	student2	17/01/2012		bbb
4	aaa	student3	16/01/2015		ccc
5	bbb	student4	15/01/2014
6	bbb	student5	22/01/2016
7	bbb	student6	18/01/2015
8	bbb	student7	18/01/2014
9	bbb	student8	21/01/2014
10	ccc	student9	07/06/2015
11	ccc	student10	14/07/2015
12	ccc	student11	12/04/2016
13	ccc	student12	13/02/2015
14	ccc	student13	11/03/2015
Sheet5

 

[steve the fish]

How about:

=INDEX(B$2:B$14,MATCH(MAX(IF($A$2:$A$14=$E2,$C$2:$C$14)),$C$2:$C$14,0))

Again CSE

 

[XOR LX]

Not sure what I was doing with that CHOOSE function! Simply (non-array):

=LOOKUP(1,0/FREQUENCY(0,1/(1+($A$2:$A$14=$E2)*$C$2:$C$14)),B$2:B$14)

will suffice.

Regards

 

[GerryZ]

Or, a touch convoluted maybe, but if you want a single, non-array formula to be placed in F2 and then copied down and across:

=LOOKUP(1,0/FREQUENCY(0,1/(1+($A$2:$A$14=$E2)*$C$2:$C$14)),CHOOSE(COLUMNS($A:A),$B$2:$B$14,$C$2:$C$14))

Regards

Hello XOR LX
I think I know You!!
Anyway the formula doesn't not work..probably I'm doing some mistakes
if is easier why don' t you make array formula?

Somenthig with INDEX and MAX or SMALL??

 

[XOR LX]

if is easier why don' t you make array formula?

Of course - that's a perfectly viable method. In fact, steve the fish's solution does just that. See my previous post - the version there is a bit simpler, and should work fine. It's an alternative to the standard CSE set-up which I happen to like.

Regards