Finding the Largest Value from Two Different Lists, Subject to Specified Criteria

 

Problem:

Columns A & B contain two lists of numbers.
We want to create a formula that will return the maximum value from either list, providing that the following two conditions are satisfied:
Numbers from List1 must be larger than 3.
Numbers from List2 must be larger than 20.
If 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:
Use the MAX functions in the following Array Formula:
{=MAX(MAX((A2:A5>C2)*A2:A5),MAX((B2:B5>C3)*B2:B5))}
Screenshot // Finding the Largest Value from Two Different Lists, Subject to Specified Criteria
Finding the Largest Value from Two Different Lists, Subject to Specified Criteria



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>