Nested If Formulae

Hi All,



Please help if you can. I am sure that this can be solved. I am so close I can smell it!! This is the problem;

I have a column E which contains the values "Old" or "New"

I have column O which may be empty or contain an alpha numeric value that begins with "DBC", begin with "C" or begin with "B"

I have three additional worksheets that contain different data these are;

COMPSW-NS

COMPSW-OS

COMPSB

I have a column Q that contains this formulae which does not work.

=IF(O9="","",IF(O9>"DBC",150,IF(AND(O9>0,E9="NEW"),VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(AND(O9>0,E9="OLD"),VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),IF(O9="B*",VLOOKUP(MID(O8,2,4),COMPSB!A:AP,42,FALSE))))))

This bit of the formulae (=IF(O9="","",IF(O9>"DBC",150,) seems to look after the first condition OK.

What I am trying to achieve is this;

If column O contains a value and column E contains "New", then lookup a value in the "COMPSW-NS" worksheet.

If column O contains a value and column E contains "Old", then lookup a value in the "COMPSW-OS" worksheet.

If the result of either of these conditions returns a #N/A, or if the value in column O begins with B, ignore the preceeding "B" and lookup the remainder of the cell contents which is 4 characters (numbers) in the "COMPSB" worksheet.

I think that I should be trying to incorporate 'ISNA' & Or to achieve this result but I can't work out the correct syntax.

I would be really grateful if you can help. I have been trying to resolve this now for a number of hours I am really lost!!



Hope somebody can help.



Regards,

Stan

Based on this:

If column O contains a value and column E contains "New", then lookup a value in the "COMPSW-NS" worksheet.

If column O contains a value and column E contains "Old", then lookup a value in the "COMPSW-OS" worksheet.

If the result of either of these conditions returns a #N/A, or if the value in column O begins with B, ignore the preceeding "B" and lookup the remainder of the cell contents which is 4 characters (numbers) in the "COMPSB" worksheet.

I think that I should be trying to incorporate 'ISNA' & Or to achieve this result but I can't work out the correct syntax.

I would be really grateful if you can help. I have been trying to resolve this now for a number of hours I am really lost!!

I wonder if this would work:

Please Login or Register  to view this content.

What is O9 and E9?

What should you expect...

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

Please Login or Register  to view this content.

otherwise post a workbook example...

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.

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:

Please Login or Register  to view this content.

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>?

What are you really trying to say with that, though.. you can't really have something be greater than a text string....

IF(O8>"DBC",150"

Then try:

Please Login or Register  to view this content.

I think it is the dreaded to many nested functions... just hold a few moments...

Okay try:

Please Login or Register  to view this content.

or shorter and probably more efficient:

Please Login or Register  to view this content.

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.