Multiple if statements -- my conditions are being ignored

[PortoMarco]

Hi

I have an IF statement that is either not behaving properly or my syntax is wrong.

=LARGE(IF(Return!$C$2:$C$600=$X$3,Return!$P$2:$P$600,IF(Return!$D$2:$D$600=$X$6,Return!$P$2:$P$600)),AA6)

The conditions are:

X3 -- the strategy
X6 -- in or out
AA6 - AA15 - the ranking 1-10

The Return tab:

C = Strategy
D = in/out
P = Data I am calling

So, call the largest value if the strategy in C is the same as the value in X3 & is listed as "in" in X6.

Whenever I input this formula, it doesn't bother filtering the data at all. It just provides me with the top 10 for the whole data set; ignoring both conditions in X3 and X6.

Thanks

 

[Peter_SSs]

... or my syntax is wrong.

I think that is the case.

Try this instead. Remember to confirm with Ctrl+Shift+Enter

=LARGE(IF(Return!$C$2:$C$600=$X$3,IF(Return!$D$2:$D$600=$X$6,Return!$P$2:$P$600)),AA6)


If you are using Excel 2010 or later, you could also try this, which doesn't require the CSE entry

=AGGREGATE(14,6,Return!$P$2:$P$600/((Return!$C$2:$C$600=$X$3)*(Return!$D$2:$D$600=$X$6)),AA6)

 

[PortoMarco]

Thanks. I used the LARGE function, looks like me syntax was wrong.