#Value error with Nested If/And function-HELP!!!

[Michael1105]

Hello All,

I am hoping someone can assist with this.  I've evaluated this formula inside and out and I can't figure out why this error is being returned.  I've checked the values within the cells to see if there are any spaces or special characters and there are none.  I've even resorted to changing the return response from relating to a cell (which I find easier) to placing the response in quotes.  

Here is the original formula:

=IF(AND(D8='Data Sheet'!A4,Log!E8='Data Sheet'!B4),"",""),
IF(AND(Log!D8='Data Sheet'!A5,Log!E8='Data Sheet'!B5),'Data Sheet'!C4,""),
IF(AND(Log!D8='Data Sheet'!$A$6,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C6,""),
IF(AND(Log!D8='Data Sheet'!$A$7,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C7,"")

I modified it to this thinking the return response values in the cells were causing the error but this still results in the #Value error:

=IF(AND(D8='Data Sheet'!A10,Log!E8='Data Sheet'!B10),"",""),
IF(AND(Log!D8='Data Sheet'!A11,Log!E8='Data Sheet'!B11),"EnterContactDate",""),
IF(AND(Log!D8='Data Sheet'!$A$12,Log!E8='Data Sheet'!$B$11),"EnterFollowUpDate",""),
IF(AND(Log!D8='Data Sheet'!$A$13,Log!E8='Data Sheet'!$B$11),"ChooseNextMethod","")

I've included the file for reference which is very basic because the log is in it's beginning production stage.

Can someone please let me know what is wrong here?  I would greatly appreciate it.

[#2]

@Michael1105 

Your issue is that each line is a complete IF() statement meaning you have multiple INDEPENDANT formulas in a single cell.  The cell must contain ONE (albeit it can be complicated) formula.  Another words the formula can have cascading IF statements that each get checked if the prior one is false.  OR this is a good use of the IFS() statement:

=IFS(AND(D8='Data Sheet'!A4,Log!E8='Data Sheet'!B4),"",
  AND(Log!D8='Data Sheet'!A5,Log!E8='Data Sheet'!B5),'Data Sheet'!C4,
  AND(Log!D8='Data Sheet'!$A$6,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C6,
  AND(Log!D8='Data Sheet'!$A$7,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C7,
  TRUE,"")

notice how in each case I have REMOVED the     ,""   at the end of each original IF statement because that would be the result in the FALSE case but instead you want it to do another IF case.  Using the IFS() statement I could also remove the IF() for each if the successive conditions.

[#3]

Thank you for your reply, mtarler. My original thought was to use the IFS function, however, I'm running Excel 2016 on the PC platform. Why Microsoft didn't add this function to the 2016 desktop version is beyond me.

[#4 · Accepted solution]

@Michael1105  It was added in 2019 version.  here is the same thing with cascading IF()

=IF(AND(D8='Data Sheet'!A4,Log!E8='Data Sheet'!B4),"",
  IF(AND(Log!D8='Data Sheet'!A5,Log!E8='Data Sheet'!B5),'Data Sheet'!C4,
  IF(AND(Log!D8='Data Sheet'!$A$6,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C6,
  IF(AND(Log!D8='Data Sheet'!$A$7,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C7,
  ,""))))

notice I just added IF( at the start of those lines and all of the closing ) at the end

[#5]

This worked!
Thank you very much for your assistance.
I actually have to add more to this nested function.
I'm going to follow through with your suggested format and don't believe I should have any more issues.
Again, I truly appreciate your time. You've saved me countless hours. :)
Have a wonderful day!