Using MAX function with nested AND statement within an IF statement

Posted on 2011-04-21
Last Modified: 2012-05-11
I am trying to write a formula in my Excel spreadsheet to perfrom the following:

For a value in column A on Sheet 1, I am trying to look up the corresponding value in a named range (column A on sheet 2) named 'NAME' and return the highest value from named range (column D on sheet 2) named 'DATE' where the parameter in named range (column B on sheet 2) named 'TYPE' is equal to ACTIVE.

Here is what I have so far:

This returns the highest value fromt he DATE range regardless of type.

The change I tried to make is as follows:


This returns a value of zero rather than the highest value int he DATE range where NAME = A2 and TYPE = ACTIVE.

Can someone help me with what I am doing wrong?
Question by:Hobart007
    LVL 81

    Accepted Solution


    LVL 81

    Assisted Solution

    by:zorvek (Kevin Jones)
    The OR and AND functions do not work inside array formulas unless they reference only single cells and constants. They can be simulated as illustrated below.

    To simulate an OR, instead of:




    To simulate an AND, instead of:





    Author Comment

    Thank you both for your answers.  The formula worked.  My issue now is that when I add the formula to a few hundred cells it crashes my pc every time I update something or try to filter my pivot table.

    Is there a less system intensive way to do this?
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    Array formulas are more CPU intensive than other formulas, but a few hundred should not affect the performance of your system. And they should most definitely not cause Excel or Windows to crash. Might be time to rebuild.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now