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?
Hobart007Asked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
=MAX(IF((NAME=A2)*(TYPE="ACTIVE")>0,DATE,0))

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
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:

   =AVERAGE(IF(OR(A1:A100>1,B1:B100="A"),C1:C100))

use:

   =AVERAGE(IF((A1:A100>1)+(B1:B100="A")>0,C1:C100))

To simulate an AND, instead of:

   =AVERAGE(IF(AND((A1:A100>1),(B1:B100="A")),C1:C100))

use:

   =AVERAGE(IF((A1:A100>1)*(B1:B100="A")>0,C1:C100))

Kevin
0
 
Hobart007Author Commented:
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?
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.