Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

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)""
0
Justincut
Asked:
Justincut
  • 6
  • 2
1 Solution
 
barry houdiniCommented:
I'm not clear what you want to do - when would you want to do the VLOOKUP looking up J11?

regards, barry
0
 
andrew_manCommented:
=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
 
JustincutAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
andrew_manCommented:
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
 
andrew_manCommented:
ATTACHED YOUR FILES.
DummyRec6.xlsx
0
 
JustincutAuthor Commented:
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
 
andrew_manCommented:
=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
 
andrew_manCommented:
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!
0
 
andrew_manCommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now