Forum Discussion

Brass Contributor

Oct 20, 2021

Solved

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

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

Decedent Log.1.xlsm29 KB

excel

Formulas and Functions

mtarler

Oct 20, 2021

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

mtarler

Silver Contributor

Oct 20, 2021

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.

Michael1105
Brass Contributor
Oct 20, 2021
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.
- mtarler
  Silver Contributor
  Oct 20, 2021
  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
  - Michael1105
    Brass Contributor
    Oct 20, 2021
    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!