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

 

[XOR LX]

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

I appreciate that, Gerry, and it's a very valid point that you make. We should always try (and especially when posting on public forums) to make our formulas not just as efficient, but also as understandable to the average Excel user as possible.

And so I sympathise with your sentiments when you say that you would actually prefer a slightly longer, slightly less efficient formula over a more concise (and potentially more efficient) one, particularly if the latter is, as you say, not exactly easy to decipher. It's you that will potentially need to amend/error-check the formula should something in your dataset change, so it makes perfect sense that you should want to understand in full how each part of that formula works.

But I hope you can also understand that I did not simply post my solution for the sake of it, or because I was "trying to be clever". Despite its looking, at first glance, quite complex, it is in fact a reasonably straightforward construction, at least once you begin to understand how the functions which it employs operate.

And remember that you're seeing that formula for the first time: I recall that the first time I saw an array formula - most likely similar to the MAX/IF one here - I had to spend literally days trying to work out how it worked! So there's nothing to say that the same won't also apply to an understanding of the construction I've used here.

Anyway, if you do one day feel like an explanation as to how it works, just reply in this thread and I'll be happy to go through it.

Regards

 

[GerryZ]

WoooW!
Ok would be nice learn this concept of formula
I know you are a good teacher but at first approch seems to be difficult formula..
maybe as you said the first time to learn a new concept is difficilt but then....
so if you are so generous to teach me thi formula a woud really apprecite
thnk you in advance
PS let's see if I've to buy the 2 beer when you arrive in Milan!!

 

[XOR LX]

Actually, I've been so inspired by this conversation that I've decided to write an article on it at my site.

So how about I post a link here to that article when I'm finished? It might be better that way, as there's only a certain amount of space I feel I should use when posting on this forum, and it will in any case make a nice post!

PS let's see if I've to buy the 2 beer when you arrive in Milan!!

Won't say no to that!

Cheers!

 

[XOR LX]

In the meantime, if you really don't like the look of my solution using LOOKUP, and you're insistent on not using CSE, it is possible to tweak your AGGREGATE set-up, viz:

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

though it's arguable that this is no less complex than my previous solution - perhaps even more so!

Regards

 

[XOR LX]

Actually, I've been so inspired by this conversation that I've decided to write an article on it at my site.

So how about I post a link here to that article when I'm finished? It might be better that way, as there's only a certain amount of space I feel I should use when posting on this forum, and it will in any case make a nice post!

@GerryZ As promised, here:

Return Entry Corresponding to Maximum Value Based on Conditions « EXCELXOR

Regards

 

[GerryZ]

Woow!! You maintained you promises. I asked you to explain me the formula that I posted and here we are, you made excellent explanation in your website...
�� Mr.XOR LX. You are top one excel expert… Waiting for next formula thank you
PS. Anyway today I red already your answer, because I got an alert e mail anytime you post new formula in your website
Thank you ��

 

[XOR LX]

You're very welcome, sir!

 

[Marcelo Branco]

@GerryZ As promised, here:

Return Entry Corresponding to Maximum Value Based on Conditions « EXCELXOR

Regards

XOR

Excellent!!!

M.

 

[XOR LX]

XOR

Excellent!!!

M.

Cheers, Marcelo!

 

[Domenic]

Is your data sorted by Column A (Criterio), in ascending order, as per your example? If so, here's a different approach that uses a single defined name to refer to each respective range.

A1:G14

criterio	student	datea		criterio	student	oldest data
aaa	student1	1/15/15		aaa	student3	1/16/15
aaa	student2	1/17/12		bbb	student5	1/22/16
aaa	student3	1/16/15		ccc	student11	4/12/16
bbb	student4	1/15/14
bbb	student5	1/22/16
bbb	student6	1/18/15
bbb	student7	1/18/14
bbb	student8	1/21/14
ccc	student9	6/07/15
ccc	student10	7/14/15
ccc	student11	4/12/16
ccc	student12	2/13/15
ccc	student13	3/11/15

<tbody>
</tbody>

First, select cell F2. Then define the following name (Ribbon > Formulas > Defined Names > Define Name)...

Name:  MyRange

Refers to:  =INDEX($B$2:$C$14,MATCH($E2,$A$2:$A$14,0),1):INDEX($B$2:$C$14,MATCH($E2,$A$2:$A$14,1),2)

Click OK

Then try...

F2, copied down:

=INDEX(MyRange,MATCH(G2,INDEX(MyRange,0,2),0),1)

G2, copied down:

=MAX(INDEX(MyRange,0,2))

Or, if you prefer, using a single formula...

F2, copied down and over to the next column:

=INDEX(MyRange,MATCH(MAX(INDEX(MyRange,0,2)),INDEX(MyRange,0,2),0),COLUMNS($F2:F2))

Hope this helps!