Finding a MAX value based on multiple criteria Excel
<p>I have created a downloadable sheet in GoogleDrive to show my problem.</p>

<p><a href="https://docs.google.com/spreadsheets/d/1O8fnWQyTFcsqfHAsmpiY4o-n2_xbC-yue2QI4Ww4kSI/edit?usp=sharing" rel="nofollow">https://docs.google.com/spreadsheets/d/1O8fnWQyTFcsqfHAsmpiY4o-n2_xbC-yue2QI4Ww4kSI/edit?usp=sharing</a></p>

<p>I need to combine the following formula which finds the maximum value in H1:H1024 which is less than or equal to the value specified in B16 </p>

<pre><code>    {=MAX(IF(H1:H1024&lt;=B16,H1:H1024, 0))}
</code></pre>

<p>with a formula which adds the argument that the cell adjacent to the value must contain all the elements listed a range of cells D1:D10 - the following formula returns TRUE when that is the case</p>

<pre><code>    =SUMPRODUCT(--ISNUMBER(SEARCH($D$1:$D$10,F1)))=COUNTA($D$1:$D$10)
</code></pre>

<p>The following does not work but illustrates what I am trying to achieve</p>

<pre><code>    {=MAX(IF(AND(H1:H1023&lt;=B16,E1:E1023="TRUE"),H1:H1023, 0))}
</code></pre>

<p>where E1 contains the SUMPRODUCT formula dragged down to E1023.</p>

<p>In know a macro would be better for this (and would make dealing more items viable) but that is not an option for me.</p>

<p>Cheers in advance.</p>