» Finding the Largest Value from Two Different Lists, Subject to Specified Criteria
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
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))}
Book Store:
Recommended Books:
- The One Page Business Plan: Start With a Vision, Build a Company!
- The 11 Immutable Laws of Internet Branding
- Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
- Now, Discover Your Strengths
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
should you avoid array formulas if non-array formulas will do the job?
dnickelson
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.
