MAX IF array function with multiple criteria

[crisrock]

Hi there,

I am looking to extract max and min values from an array containing several thousands of records in the following format:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]Year[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2019[/TD]
[/TR]
</tbody>[/TABLE]

On a different sheet I would like to report max and min value based on Year and Column Header which are selected via a box list.
For example, if user selects Year=2018 and Column Header B max value should be 1 and min value should be 0.
I have been trying to achieve this with a MAX IF array formula without any luck.
Below is my latest attempt:

{=MAX(IF($E$1:$E$9=$H$3,IF($A$1:$D$1=$H$2,$A$2:$D$9,0)))}

Where E1:E9 is the column with the reference years and A1:D1 is the Header row. User selected Year and Header are in H3 and H2 respectively.

This formula seems to search the array only based on the Column Header criteria, while the Year is totally ignored.
Any pointers?
I am also open to suggestions if there is a different approach that might be less computational intensive given the large amount of data in my array.
In advance, thank you very much!

Cheers,

C

 

[Tetra201]

Try the following array formula:

=MAX(IF($E$2:$E$9=$H$3,INDEX($A$2:$D$9,0,MATCH($H$2,$A$1:$D$1,0))))