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.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Age[/TD]
[TD]Grade[/TD]
[TD]Favorite Color[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]17[/TD]
[TD]99[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Stefan[/TD]
[TD]26[/TD]
[TD]99[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Jake[/TD]
[TD]17[/TD]
[TD]78[/TD]
[TD]Pink[/TD]
[/TR]
[TR]
[TD]Donna[/TD]
[TD]28[/TD]
[TD]98[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]36[/TD]
[TD]85[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Victoria[/TD]
[TD]15[/TD]
[TD]88[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Brenda[/TD]
[TD]22[/TD]
[TD]89[/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]


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.