Nested If with Lookup

[veggies27]

I currently have the following in cell F17:

=IF($A17="","",IF(D17="",IF(E17="",(IF(LOOKUP($A17,Phases!$B:$B,Phases!$E:$E)="S",$C17)),E17*$C17),D17))

What I am trying to do in words is the following:
If A17 is blank, leave F17 blank. If not, I want to check a seperate
spreadsheet with the lookup function to see if the variable (currently in
Phases sheet colum E) relating to A17 (which is repeated in Phases sheet
column B) matches the required variable (in this case "S"). If it matches, I
want to show one of 3 values. If cell D17 contains a number, F17 should =
D17, if not, I want to check E17. If E17 contains a value (it will be a
percentage), I want F17 to be E17*C17. At last resort, if the other sheet
variable matches with the lookup function, and there in nothing in wither D17
or E17, F17 should equal C17.

I'm wondering if I have nested these in ther wrong order. It seems to work
OK unless the LOOKUP function comes out FALSE and D17 and E17 are both blank.

Any help would be great!!!

Jeff

 

[T. Valko]

Can't really tell from your description what you want but here's what's
happening with the formula:

The second group of IFs is the same as an AND function:

IF(D17="",IF(E17="",(IF(LOOKUP

That could also be written like:

IF(AND(D17="",E17="",LOOKUP(...)=something)

So, you don't have a defined value_if_false argument for this particular
expression:

IF(LOOKUP($A17,Phases!$B:$B,Phases!$E:$E)="S",$C17)

It should be something like this:

Where V = value_if_false argument.

=IF(A17="","",IF(D17="",IF(E17="",IF(LOOKUP(A17,Phases!B:B,Phases!E:E)="S",C17,V),V
if E17<>""),V if D17<>""))