Link to home
Start Free TrialLog in
Avatar of Hobart007
Hobart007

asked on

Using MAX function with nested AND statement within an IF statement

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:
{=MAX(IF(NAME=A2,DATE,0))}

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

The change I tried to make is as follows:

{=MAX(IF(AND(NAME=A2,TYPE="ACTIVE"),DATE,0))}

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?
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Hobart007
Hobart007

ASKER

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?
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.

Kevin