In this example we review a formula which finds maximal value by condition. In the standard set in Excel is only simple functions for searching for maximal (MAX) and minimal (MIN) values.
To write our own formula we need use two standard functions: MAX and IF. We will include IF() function into MAX() one and input them as a massive formula. For this input in cell D15:
=MAX(IF($B$2:$B$11=B15;$D$2:$D$11))
and press simultaneously Ctrl+Shift+Enter (accordingly input a massive formula). Then it remains stretch the array formula down.
We will have such result:
Also we can see that the formula enclosed in figure brackets ({ }), it is indication that we input an array formula.
Similarly we can construct the formula for MIN() function:
=MIN(IF($B$2:$B$11=B15;$D$2:$D$11))