Nested If Formulae

**NBVC** · 07-15-2009, 12:17 PM

What is O9 and E9?

What should you expect...

Also, noticed one reference to O8 in the formula should be O9

=IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4),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),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),"")),"")),""))

otherwise post a workbook example...

**stanleds** · 07-15-2009, 12:30 PM

Hi again,

I noticed the reference to row 8, changed that and it made no difference i am afraid. I would expect to see a number which would be returned by the lookup formulae. It would be difficult to upload an example as I would need to include a big chunk of the spreadsheet which is 8 meg.

I might be able to replicate a small amount of data on each worksheet to reduce the file size. How do you upload a file? Can you help me with this? Sorry!!
Stan

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

After you condensed it sufficiently (you can also zip it), then click Post Reply and then click the paperclip icon in the reply box.. click Browse to find the file and then click Upload to upload it.. then Close.

**stanleds** · 07-15-2009, 01:43 PM

Hi again,

Thanks for the speedy reply buddy..... Here is a condensed version of the file wchich is also zipped up to reduce it's size. Please look at column T on attached file and follow steps to observe my logic!!

Any help that you can give would be gratefully recieved. It's 18:40 in the UK and I need to try and get this sorted before tomorrow if I can.

Thanks again,

Stan

**NBVC** · 07-15-2009, 02:08 PM

Ok the lookup value, when the first character is a "B" is numeric... ie. Mid(O9,2,4) is supposed to be numeric...

You show that you accounted for that in your spreadsheet by adding the VALUE() function... but didn't show that in your originally posted formula...

anyways... try:

=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),"")),"")),""))

does that work or are we still missing something..

I don't really get the 190 option though... when exactly is that supposed to display>?

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

Hi again,

It looks as though you have resolved most of the problem. Thanks for all your help. However, the first part of the original formulae that started "=IF(O8="","",IF(O8>"DBC",150" took care of the first condition. This has now been excluded...... I am sorry to be a pain but if i understood the construction of the very helpful formulae that you posted back, I would incorporate this in to the correct version. Is it possible to incorporate this in to your solution please?

Many thanks,

Stan