Using two criteria on two array in Index()

[weeble33]

Hello,

I've become stuck on a recent project and need some help. I have the following formula:

Please Login or Register  to view this content.

Quick summary; It looks at Sheet1, column F and as long as there isn't a 1 in there, it returns the corresponding value in column A, this happens for as far down as I pull the equation. The change to this that I'd like to make is look at column F, return true if the value isn't 1 AND look at column M and return true if Len()>3. I've tried the following equations:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I believe the problem is, in the original formula, the IF(Sheet1!$F$2:$F$4000<>1, returns an array into the If statement. By adding AND() or Sumproduct() they return on a single value into the IF. Is there a way to force an array return, or maybe a better way of going about it in general.

[tigeravatar]

weeble33,

Give this a try:

Please Login or Register  to view this content.

[weeble33]

Wow, nested ifs... i feel dumb. Thank you for the help.