Combine two formulas

I need help combining the below formulas.

=IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "N", "Yes-Not Discretionary", ""), "Not on List").

=IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "A", "Yes-Discretionary", "").

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭

    Hi @C. Perry

    Without being able to test this, its a freehand attempt but I placed the second formula where I would when stacking IF formulas.

    Removed the IFERROR simply to clear it up initially which you can add back in if the attempt works...

    =IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "N", "Yes-Not Discretionary", IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "A", "Yes-Discretionary", "")

    Hope this helps

    Thanks

    Paul

  • C. Perry
    C. Perry ✭✭✭

    When I add back the IFERROR I get

    =IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "N", "Yes-Not Discretionary", IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "Y", "Yes-Discretionary", ""))))

    Your formula below works but if Account@row column is blank space I get "Yes-Discretionary" for a blank space. =IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "N", "Yes-Not Discretionary", IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "Y", "Yes-Discretionary", "")). Can the formula be enhanced to return a blank if Account@row is a blank cell?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Give this a try:

    =IFERROR(IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "N", "Yes-Not Discretionary", IF(INDEX({Option Exception Master List Range 1}, MATCH(Account@row, {Option Exception Master List}, 0)) = "A", "Yes-Discretionary")), "Not on List")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!