Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested If with Lookup
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),D 17)) 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nested If with Lookup
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,Ph ases!B:B,Phases!E:E)="S",C17,V),V if E17<""),V if D17<"")) -- Biff Microsoft Excel MVP "veggies27" wrote in message ... 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),D 17)) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested If statement or lookup? | Excel Worksheet Functions | |||
Nested if's and lookup | Excel Worksheet Functions | |||
NESTED IF or LOOKUP formula? | Excel Discussion (Misc queries) | |||
can you create nested formulas within v-lookup; if so how | Excel Worksheet Functions | |||
Nested Lookup !! ? | Excel Worksheet Functions |