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

#### dreyfy

##### New Member
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.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

##### MrExcel MVP
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.

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

#### dreyfy

##### New Member
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...

##### MrExcel MVP
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
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

##### MrExcel MVP
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
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

##### MrExcel MVP
chiello 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?

Replies
18
Views
365
Replies
10
Views
776
Replies
7
Views
334
Replies
7
Views
370
Replies
3
Views
149

1,182,192
Messages
5,934,184
Members
436,935
Latest member
Fergjosh97

### 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.

### Which adblocker are you using?

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

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