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

dreyfy

New Member
Joined
Jan 22, 2005
Messages
2
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
dreyfy said:
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

New Member
Joined
Jan 22, 2005
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
dreyfy said:
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
ABCDE
1Max14.02.1983
2TopN1
3Ties1
4NameDOBRankTopN
5person121.11.19795person5
6person210.04.19804person7
7person325.11.19756 
8person406.08.19757
9person514.02.19831
10person601.03.19823
11person714.02.19832
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

Well-known Member
Joined
Jan 18, 2005
Messages
848

ADVERTISEMENT

I think this suits your request
Cartel1
ABCDEFG
1person120/11/197914/02/1983person5
2person210/04/1980
3person325/11/1975ReplaceINDICEwithINDEX
4person406/08/1975ReplaceCONFRONTAwithMATCHwithINDEX
5person514/02/1983Replace";"with","
6
Foglio1


(y)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
chiello said:
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

Well-known Member
Joined
Jan 18, 2005
Messages
848
Aladin Akyurek said:
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
chiello said:
Aladin Akyurek said:
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?
 

Forum statistics

Threads
1,148,269
Messages
5,745,781
Members
423,972
Latest member
franklins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top