Maximum value by distinctive year

Try this

In G7

Please Login or Register  to view this content.

Confirmed by pressing Ctrl+Shift+Enter

Drag/Fill Down

and for Minimum

Please Login or Register  to view this content.

Confirmed by pressing Ctrl+Shift+Enter

Hope this helps

Have you considered using a Pivot Table ?

On an aside - re: the Array, though:

=MAX(($A$3:$A$14=$F7)*($B$3:$B$14))

will work without issue I confess I often prefer to split the conditional test from the resulting values, ie:

=MAX(IF($A$3:$A$14=$F7),$B$3:$B$14))
CSE

I think this is cleaner but also it has the added advantage of being more "flexible" should you decide latterly you wish to revert to a MIN test - ie all you need do is adjust the MAX to MIN using the above method (resetting the array).

In the first example you can not adjust to MIN in this manner as the MIN would always be 0 (unless all values met the pre-emptive condition)

(same holds true for AVERAGE also)

"Better" is subjective of course but IMO, yes - when conducting MIN or AVERAGE calcs the approach is invariably a necessity rather than a choice so for me it makes sense to apply the same logic to MAX even though "technically" it can be avoided.

There is one other benefit of separating test from associated values and that is you're removing the explicit coercion of associated values.

Consider A1:B5

a 1
b 2
a 3
a b
b 2

The initial MAX approach would generate an Error (#VALUE!) given the explicit coercion of B1:B5

=MAX((A1:A5="a")*B1:B5)
CSE

whereas the split approach avoids the coercion and thus does not generate an error

=MAX(IF(A1:A5="a",B1:B5))
CSE

The Nulls added to the MIN/MAX/AVERAGE Arrays aren't technically required though they do no harm and perhaps makes the formula more intuitive.

Where the conditional test fails a Boolean FALSE will be added to the resulting Array of values (by default), these Logical results will be ignored in the subsequent MIN/MAX/AVERAGE calculation.