Is there an easy way to do this?

Currently, I look for a match between a cell in Sheet 1 and cells in Sheet 2. If it finds a match, it uses the value in Sheet 2, otherwise it shows "No". This works fine.

However, I'd like to add another IF so that if the value in Sheet 2 that results from the match is "Canceled", I want it to display "No" as well, not "Canceled". The only way I can think to do this is to evaluate the match twice.

IF (result of match is "Canceled"), then "No", otherwise do the match.

"result of match" and "do the match" is the same long formula. I'd prefer to not have to repeat it (or create a second column), if possible.

Here is an example


=IFERROR(INDEX formula, "No")


=IF RESULT IS "Canceled", "No", otherwise IFERROR(INDEX formula, "No") without having to do something like this:

=IF IFERROR(INDEX formula, "No") = "Canceled", "No", otherwise IFERROR(INDEX formula, "No").


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!