Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# If Statement

Posted on 2013-12-04
Medium Priority
190 Views
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)""
0
Question by:Justincut
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 2

LVL 50

Expert Comment

ID: 39695955
I'm not clear what you want to do - when would you want to do the VLOOKUP looking up J11?

regards, barry
0

LVL 4

Expert Comment

ID: 39695969
=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"))))
0

Author Comment

ID: 39696046
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
0

LVL 4

Expert Comment

ID: 39696067
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")))
0

LVL 4

Expert Comment

ID: 39696150
DummyRec6.xlsx
0

Author Comment

ID: 39698026
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
0

LVL 4

Accepted Solution

andrew_man earned 2000 total points
ID: 39698039
=IF(ISNA(VLOOKUP(\$B5,'Prev Day'!\$B:\$AL,29,FALSE)),
IF(ISNA(VLOOKUP(\$C5,'Prev Day'!\$C:\$AL,28,FALSE)),
IF(ISNA(VLOOKUP(J11, Rec!\$C:\$AL,14,FALSE)),"NEW",
VLOOKUP(J11, Rec!\$C:\$AL,14,FALSE)),
VLOOKUP(\$C5,'Prev Day'!\$C:\$AL,28,FALSE)),
VLOOKUP(\$B5,'Prev Day'!\$B:\$AL,29,FALSE))
0

LVL 4

Expert Comment

ID: 39698067

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

Keep in touch!
0

LVL 4

Expert Comment

ID: 39698072
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))
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month6 days, 13 hours left to enroll