Nested If Formulae

**stanleds** · 07-15-2009, 04:31 PM

I'm afraid that comes down to my ignorance again. What I am trying to say is if cell O9 starts with "DBC" then make cell Q9 150. I didnt think that you ould use wildcards with if statements so I attempted this as an alternative. I understand that this may not be correct. Can you suggest an alternative? I do need to account for the possibility that O9 may be blank as well!!

Many thanks again,

Stan

**NBVC** · 07-15-2009, 04:35 PM

Then try:

=IF(LEFT(O9,3)="DBC",150,IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),"")),"",IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),"")))

**stanleds** · 07-15-2009, 04:45 PM

For some reason. it does nnot like that formulae. Have you tried pasting this in the sample workbook that I uploaded?

thanks again,

Stan

**NBVC** · 07-15-2009, 05:01 PM

Okay try:

=IF(LEFT(O9,3)="DBC",150,IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE)),"")),"")),"",IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE)),"")),"")))

or shorter and probably more efficient:

=IF(LEFT(O9,3)="DBC",150,LOOKUP(9.999999999E+307,CHOOSE({1,2},0,IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE)),"")),""))))

this last formula will return 0, if the Vlookup() results are #N/A... so you can format that cell as 0;-0;;@ to blank out the 0.