Return max value from list, that ensures separate value does not exceed given amount

I've attached an example.

The values calculated in column H are a multiplication of column F and column G, and a value from the list in column A.

I need to find a way to return the largest value from column A, that would ensure that the value returned by the formula in column H does not exceed 1 (or any value in cell C3).

Does this require the solver add-in, or is there a formula that can do it?

Thanks

Originally Posted by Glenn Kennedy

Assuming that you're still using Excel 2007, this array formula will do the job:

=MAX(IF(F3*G3*$A$3:$A$12<1,$A$3:$A$12,""))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Many thanks! - that works :-)
I've found array formulas to be quite slow when working with lots of rows - is there a way to do this without an array, using Excel 2016?

Originally Posted by Bo_Ry

Maybe try at H3

=LOOKUP(C$3,F3*G3*$A$3:$A$12)

That returns the max value, but doesn't tell you which value from Column A achieved it.

Originally Posted by Glenn Kennedy

Yes:

=AGGREGATE(14,6,$A$3:$A$12/(F3*G3*$A$3:$A$12<=$C$3),1)

Please amend your profile to show your correct Excel version.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Many thanks!