Formula that return Name according to Max Grade and Age

[Israelinas]

Hi all,

I need to create a formula that will return the Name and the Favorite Color of a person according to the following:
1) Age = 17
2) Highest grade.

Name	Age	Grade	Favorite Color
John	17	99	Blue
Stefan	26	99	Red
Jake	17	78	Pink
Donna	28	98	Green
Sam	36	85	Blue
Victoria	15	88	Yellow
Brenda	22	89	Orange

<tbody>
</tbody>

Many thanks,
Israelinas

 

[DanteAmor]

Use the following array formulas

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	Age	Grade	Favorite Color		Age	Name	Favorite Color
2	John	17	99	Blue		15	Victoria	Yellow
3	Stefan	26	99	Red
4	Jake	15	78	Pink
5	Donna	26	98	Green
6	Sam	17	85	Blue
7	Victoria	15	88	Yellow
8	Brenda	26	89	Orange
April

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Scott T]

Try

If there is a tie for highest grade then this will return the first match

Book1
	A	B	C	D
1	Name	Age	Grade	Favorite Color
2	John	17	99	Blue
3	Stefan	26	99	Red
4	Jake	17	78	Pink
5	Donna	28	98	Green
6	Sam	36	85	Blue
7	Victoria	15	88	Yellow
8	Brenda	22	89	Orange
9
10
11	age	17
12
13	name	favorite color
14	John	Blue
Sheet2

Cell Formulas
Range	Formula
A14	{=INDEX(A2:A8,SMALL(IF(B2:B8=B11,IF(C2:C8=MAXIFS(C2:C8,B2:B8,B11),ROW(B2:B8)-ROW(B2)+1)),1))}
B14	{=INDEX(D2:D8,SMALL(IF(B2:B8=B11,IF(C2:C8=MAXIFS(C2:C8,B2:B8,B11),ROW(B2:B8)-ROW(B2)+1)),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Israelinas]

Many thanks to both of you! you are the best!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.