Formula issue with #NO MATCH (Using IF and IFERROR)

Options

Hi I have the following formula:

=IF(AND([Attended over 6 months]@row = "Green", Interview@row = "Green", [Referee Check]@row = "Green", [Blue Card Linked]@row = "Yes"), "Yes", IF(OR([Attended over 6 months]@row = "Yellow", Interview@row = "Yellow", [Referee Check]@row = "Yellow", [Blue Card Linked]@row = "Hold"), "Hold", IF(OR([Attended over 6 months]@row = "", Interview@row = "", [Referee Check]@row = "", [Blue Card Linked]@row = ""), "Hold", IF(OR([Attended over 6 months]@row = "Red", Interview@row = "Red", [Referee Check]@row = "Red", [Blue Card Linked]@row = "No"), "No", IF(SOURCE@row = "RF", "Yes")))))

But when the Blue Card Linked column has #NO MATCH my result in my formula column says the same.

i wanted to add the IFERROR to it: =IFERROR([Blue Card Linked]@row, "Hold") but i cant get it to work....the IFERROR works on its own tho...

Is this formula possible?

Tags:

Best Answer

  • David Jasven
    David Jasven ✭✭✭✭
    Answer ✓
    Options

    You can add the IFERROR function to your formula by replacing:

    [Blue Card Linked]@row with: IFERROR([Blue Card Linked]@row, "Hold")
    

    This will cause the formula to return "Hold" if an error occurs when trying to retrieve the value of the [Blue Card Linked] cell. If there is no error, it will return the value of the [Blue Card Linked] cell as usual.

    Here is the updated formula with the IFERROR function added:

    =IF(AND([Attended over 6 months]@row = "Green", Interview@row = "Green", [Referee Check]@row = "Green", IFERROR([Blue Card Linked]@row, "Hold") = "Yes"), "Yes", IF(OR([Attended over 6 months]@row = "Yellow", Interview@row = "Yellow", [Referee Check]@row = "Yellow", IFERROR([Blue Card Linked]@row, "Hold") = "Hold"), "Hold", IF(OR([Attended over 6 months]@row = "", Interview@row = "", [Referee Check]@row = "", IFERROR([Blue Card Linked]@row, "Hold") = ""), "Hold", IF(OR([Attended over 6 months]@row = "Red", Interview@row = "Red", [Referee Check]@row = "Red", IFERROR([Blue Card Linked]@row, "Hold") = "No"), "No", IF(SOURCE@row = "RF", "Yes")))))

Answers

  • David Jasven
    David Jasven ✭✭✭✭
    Answer ✓
    Options

    You can add the IFERROR function to your formula by replacing:

    [Blue Card Linked]@row with: IFERROR([Blue Card Linked]@row, "Hold")
    

    This will cause the formula to return "Hold" if an error occurs when trying to retrieve the value of the [Blue Card Linked] cell. If there is no error, it will return the value of the [Blue Card Linked] cell as usual.

    Here is the updated formula with the IFERROR function added:

    =IF(AND([Attended over 6 months]@row = "Green", Interview@row = "Green", [Referee Check]@row = "Green", IFERROR([Blue Card Linked]@row, "Hold") = "Yes"), "Yes", IF(OR([Attended over 6 months]@row = "Yellow", Interview@row = "Yellow", [Referee Check]@row = "Yellow", IFERROR([Blue Card Linked]@row, "Hold") = "Hold"), "Hold", IF(OR([Attended over 6 months]@row = "", Interview@row = "", [Referee Check]@row = "", IFERROR([Blue Card Linked]@row, "Hold") = ""), "Hold", IF(OR([Attended over 6 months]@row = "Red", Interview@row = "Red", [Referee Check]@row = "Red", IFERROR([Blue Card Linked]@row, "Hold") = "No"), "No", IF(SOURCE@row = "RF", "Yes")))))

  • ChristinaP
    ChristinaP ✭✭✭
    Options

    Perfect thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!