select a cell from 1 row by matching another cell in row

[dreyfy]

hello guys. i have a small problem and looks like i'm stuck. i have a workbook with two columns, one with names and one with birthdays. i need to select the youngest person from this list (name and age), and print them in another sheet.
it doesn't seem difficult, but i can't figure out how to do it.
thanks in advance

 

[Aladin Akyurek]

hello guys. i have a small problem and looks like i'm stuck. i have a workbook with two columns, one with names and one with birthdays. i need to select the youngest person from this list (name and age), and print them in another sheet.
it doesn't seem difficult, but i can't figure out how to do it.
thanks in advance

Would you provide a 5-row sample along with the desired result?

 

[dreyfy]

OK, here an example:

person1 | 20.11.1979
person2 | 10.04.1980
person3 | 25.11.1975
person4 | 06.08.1975
person5 | 14.02.1983

From this two columns I need the youngest person (that would be "person5", as his birthday is 14.02.1983). I selected the most recent date using the MAX() function, but I don't know how to select (and use) the corresponding name - "person5" (which is on another column...).
I even got the row's number using MATCH(MAX()), but again I don't know how to use a value (integer) in a cell as a row number...

 

[Aladin Akyurek]

OK, here an example:

person1 | 20.11.1979
person2 | 10.04.1980
person3 | 25.11.1975
person4 | 06.08.1975
person5 | 14.02.1983

From this two columns I need the youngest person (that would be "person5", as his birthday is 14.02.1983). I selected the most recent date using the MAX() function, but I don't know how to select (and use) the corresponding name - "person5" (which is on another column...).
I even got the row's number using MATCH(MAX()), but again I don't know how to use a value (integer) in a cell as a row number...

What you actually want is a Top N list with N set to 1.

Keywords. Extract a Top N list with N = 1, max-value based. [ COUNTIF, IF, INDEX, MATCH, MAX, RANK, ROW ]

Book1
	A	B	C	D	E
1				Max	14.02.1983
2				TopN	1
3				Ties	1
4	Name	DOB	Rank		TopN
5	person1	21.11.1979	5		person5
6	person2	10.04.1980	4		person7
7	person3	25.11.1975	6
8	person4	06.08.1975	7
9	person5	14.02.1983	1
10	person6	01.03.1982	3
11	person7	14.02.1983	2
12
Sheet1

Formulas...

C5, copied down:

=IF(B5<>"",RANK(B5,$B$5:$B$11)+COUNTIF($B$5:B5,B5)-1,"")

E1:

=MAX(B5:B11)

E2: 1

which is the Top N value, with N = 1.

E3:

=MAX(IF(INDEX(B5:B11,MATCH(E2,C5:C11,0))=B5:B11,C5:C11))-E2

which must be confirmed with control+shift+enter instead of the usual enter.

This formula calculates the ties of the Nth value itself.

E5, copied down:

=IF(ROW()-ROW(E$5)+1<=$E$2+$E$3,INDEX($A$5:$A$11,MATCH(ROW()-ROW(E$5)+1,$C$5:$C$11,0)),"")

 

[chiello]

I think this suits your request

Cartel1
	A	B	C	D	E	F	G
1	person1	20/11/1979		14/02/1983	person5
2	person2	10/04/1980
3	person3	25/11/1975				ReplaceINDICEwithINDEX
4	person4	06/08/1975				ReplaceCONFRONTAwithMATCHwithINDEX
5	person5	14/02/1983				Replace";"with","
6
Foglio1

 

[Aladin Akyurek]

I think this suits your request

...
=MAX(B1:B5)>D1

=INDICE($A$1:$A$5;CONFRONTA(D1;$B$1:$B$5;0))

...

Really? Why don't you try it on the sample the exhibit I posted shows?

 

[chiello]

Really? Why don't you try it on the sample the exhibit I posted shows?

Hi Aladin, I'm perfectly conscious my solution returns just 1 result, and in case 2 or more persons have the same date, it just returns the first occurrence.

My solution is simpler than yours, but not complete. dreyfy knows if it is enough and is free to choose!!

:wink: :wink:

Ciao

 

[Aladin Akyurek]

Really? Why don't you try it on the sample the exhibit I posted shows?

Hi Aladin, I'm perfectly conscious my solution returns just 1 result, and in case 2 or more persons have the same date, it just returns the first occurrence.

My solution is simpler than yours, but not complete. dreyfy knows if it is enough and is free to choose!!

:wink: :wink:

Ciao

That's unsatisfactory on two counts:

1] The OP would be seduced to opt for an "incorrect" solution. Note that "Ties will never happen in my data" would not constitute a credible justification.

2] Invoking Index/Match in combination with MAX/MIN and LARGE/SMALL has been a significant mistake (something that I also commited before I devised the current formula system). Why do you continue the standard approach while something invalidating that has been offered?