Thread: Finding the maximum value meeting criteria

Problem:

\"Columns D:E contain two lists of numbers.
How could we create a formula that will return the maximum value, providing that it meets the following two conditions:
-If the maximum value is in List 1, it must be larger than 3.
-If the maximum value is in List2, it must be larger than 25.
In case the maximum value found does not meet the above criteria, the formula should look for the next largest number until finding the largest number matching criteria.

Solution:

Using the MAX and IF functions in the following Array Formula:
{=MAX(MAX(IF(A2:A5>C2,A2:A5)),MAX(IF(B2:B5>C3,B2:B5)))}

Example:

List1____List2
3________20_____3
5________22_____25
1________14
2________4

Result___5

This could be done with =MAX(IF(MAX(A:A)>C3,MAX(A:A),0),IF(MAX(B:B,)>C4,MAX(B:B),0)) without the need for using an array formula.