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

 

[GerryZ]

Thank You Both
Both formula works fine
but I will use STEVE the Fish Formula because is conceptually easier to understand and I will make it simple Without CSE because i will use AGGREGATE LARGE....1 ect ...ect
Thank you Both you solved me a problem

 

[GerryZ]

this is how i will arrange steve the fish formula without CSE

=INDEX(B$2:B$14,MATCH(AGGREGATE(14,6,$C$2:$C$14/($A$2:$A$14=$E2),1),$C$2:$C$14,0))

thank you again

 

[XOR LX]

I will make it simple Without CSE because i will use AGGREGATE

Yes - that is a sensible and perfectly valid idea.

Let us know if you need help creating the equivalent AGGREGATE construction.

Regards

 

[XOR LX]

Ah, no! Wait! There is a flaw with these two solutions:

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

=INDEX(B$2:B$14,MATCH(AGGREGATE(14,6,$C$2:$C$14/($A$2:$A$14=$E2),1),$C$2:$C$14,0))

and that is, because the conditional statement is not also employed within MATCH's lookup_array, it is not guaranteed to give correct results.

For example, in your dataset, change the value in C3 from 17/01/2018 to 22/01/2016. What results do you get using these two formulas for the student name?

Whereas I believe my formula will still give correct results.

Regards

 

[XOR LX]

The point is that, with these constructions as they stand, there is nothing to say that the maximum value in column C which corresponds to a column A entry of e.g. "bbb" does not also occur within column C, but for a different column A entry, e.g. "aaa".

And if the case for "aaa" happens to occur in a row which precedes that for "bbb", then MATCH will evidently - and undesirably - return the date corresponding to that value instead.

In order to be certain of correct results, steve the fish's array formula should be:

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

i.e. we must repeat the conditional statement for the lookup_array also, to ensure that cases where the column A entry is different to that being sought are excluded.

Unfortunately, there is no simple means to convert this version into an AGGREGATE-based one. And so (and not to blow my own trumpet!), I would prefer my solution is this instance.

Regards

 

[Aladin Akyurek]

this is how i will arrange steve the fish formula without CSE

=INDEX(B$2:B$14,MATCH(AGGREGATE(14,6,$C$2:$C$14/($A$2:$A$14=$E2),1),$C$2:$C$14,0))

thank you again

Probably a slower alternative though...

 

[GerryZ]

Thank You XOR LX
Just last thing..
Your formula works fine but the problem is that your formula are very ADVANCED they are few steps above the regular formula (too cleaver)
so let's say for some people (like me) Excel is allready difficult and the best think to do when is possible is to find a formula with easier sintax and more understandable.
Your Excel level is very ADVANCED and all your formula works perfectly and I saw in your website the way you expalin the formula EXCELLENT!
but when i lookup for a formula I don't need just COPY and PASTE but i need to understand the formula
ciao GerryZ

 

[steve the fish]

The point is that, with these constructions as they stand, there is nothing to say that the maximum value in column C which corresponds to a column A entry of e.g. "bbb" does not also occur within column C, but for a different column A entry, e.g. "aaa".

And if the case for "aaa" happens to occur in a row which precedes that for "bbb", then MATCH will evidently - and undesirably - return the date corresponding to that value instead.

In order to be certain of correct results, steve the fish's array formula should be:

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

i.e. we must repeat the conditional statement for the lookup_array also, to ensure that cases where the column A entry is different to that being sought are excluded.

Unfortunately, there is no simple means to convert this version into an AGGREGATE-based one. And so (and not to blow my own trumpet!), I would prefer my solution is this instance.

Regards

I was looking what it would do with ties for date as well!

 

[GerryZ]

Probably a slower alternative though...

hello Aladin!
what d you mean? can you make faster?

 

[GerryZ]

I was looking what it would do with ties for date as well!

Steve I Belive when you have the ties you can make somenthing with LARGE.....