Justincut

asked on

# If Statement

Hi Guys, is it possible to put another IF statement in this formula? If the Vlookup on B11 is "NA",then if J11 is empty and IF Vlookup on C11, then put "0" as I am still getting "VALUE"?

Current Formula:

=IF(ISNA(VLOOKUP($B11,Rec!$B:$AL,23,FALSE)),IF($J11="","0",IF(ISNA(VLOOKUP($C11,Rec!$C:$AL,22,FALSE)),"0.00",VLOOKUP($C11,Rec!$C:$AL,22,FALSE))),VLOOKUP($B11,Rec!$B:$AL,23,FALSE))

After that I want to add "VLOOKUP(J11, Rec!$C:$AL,14,False)""

Current Formula:

=IF(ISNA(VLOOKUP($B11,Rec!

After that I want to add "VLOOKUP(J11, Rec!$C:$AL,14,False)""

=IF(ISNA(VLOOKUP($B11,Rec!$B:$AL,23,FALSE)),

IF($J11="","0",

IF(ISNA(VLOOKUP($C11,Rec!$C:$AL,22,FALSE)),

IF(ISNA(VLOOKUP($J11, Rec!$C:$AL,14,False)),"0.00"))))

IF($J11="","0",

IF(ISNA(VLOOKUP($C11,Rec!$

IF(ISNA(VLOOKUP($J11, Rec!$C:$AL,14,False)),"0.0

ASKER

Hi, my formula is in Column AD on the following attachment. I am getting a "VALUE#" error message so I want to add another IF, once all the other IFS have failed.

DummyRec6.xlsx

DummyRec6.xlsx

THIS IS MY FORMULA

=IFERROR(VLOOKUP($B5,'Prev Day'!$B:$AL,29,FALSE),

IFERROR(VLOOKUP($C5,'Prev Day'!$C:$AL,28,FALSE),

IFERROR(VLOOKUP(J11, Rec!$C:$AL,14,FALSE),"NEW")))

=IFERROR(VLOOKUP($B5,'Prev

IFERROR(VLOOKUP($C5,'Prev Day'!$C:$AL,28,FALSE),

IFERROR(VLOOKUP(J11, Rec!$C:$AL,14,FALSE),"NEW"

ATTACHED YOUR FILES.

DummyRec6.xlsx

DummyRec6.xlsx

ASKER

Sorry chaps, I got the formula wrong. Its =IF(ISNA(VLOOKUP($B5,'Prev Day'!$B:$AL,29,FALSE)),IF($J5="","NEW",IF(ISNA(VLOOKUP($C5,'Prev Day'!$C:$AL,28,FALSE)),"NEW",VLOOKUP($C5,'Prev Day'!$C:$AL,29,FALSE))),VLOOKUP($B5,'Prev Day'!$B:$AL,28,FALSE))> I do not want to use ISERROR, only ISNA as I want when there is a genuine error as sometimes the sequence of the Vlookup Cell is the same, but changes order. eg.

ALMG7GCSECURITAGBP-58430103445203.28, 3409946.28, 3437513.28, 3440618.28LANMAST54DEC00, LANG54DEC000.

to ALMG7GCSECURITAGBP-58430103409946.28, 3437513.28, 3440618.28, 3445203.28LANG54DEC000., LANMAST54DEC00

ALMG7GCSECURITAGBP-5843010

to ALMG7GCSECURITAGBP-5843010

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

I already use a notepad to develop the formula, like the about style!

Better to Teach a Man Fishing Than to Give Him a Fish!

Keep in touch!

Better to Teach a Man Fishing Than to Give Him a Fish!

Keep in touch!

should be

=IF(ISNA(VLOOKUP($B5,'Prev Day'!$B:$AL,29,FALSE)),

IF(ISNA(VLOOKUP($C5,'Prev Day'!$C:$AL,28,FALSE)),

IF(ISNA(VLOOKUP($J5, Rec!$C:$AL,14,FALSE)),"NEW",

VLOOKUP($J5, Rec!$C:$AL,14,FALSE)),

VLOOKUP($C5,'Prev Day'!$C:$AL,28,FALSE)),

VLOOKUP($B5,'Prev Day'!$B:$AL,29,FALSE))

=IF(ISNA(VLOOKUP($B5,'Prev

IF(ISNA(VLOOKUP($C5,'Prev Day'!$C:$AL,28,FALSE)),

IF(ISNA(VLOOKUP($J5, Rec!$C:$AL,14,FALSE)),"NEW

VLOOKUP($J5, Rec!$C:$AL,14,FALSE)),

VLOOKUP($C5,'Prev Day'!$C:$AL,28,FALSE)),

VLOOKUP($B5,'Prev Day'!$B:$AL,29,FALSE))

regards, barry