Using Match to look for multiple strings and how to evaluate a #NO MATCH

Ok, I am trying to find a way to use Match to look for two values in a column and return the first one found. I know you cannot look for multiple strings in the same formula (at least I haven't been able to write one), so I created two helper columns. The first helper column looks for string A and the second helper column looks for string B.

So one helper column will return the Match index value like "4" and the other column will return the "#NO MATCH" since its always one or the other.

So then I was going to create the final column to look at both helper columns and pull the one with the number.

So the question I have, is how can you read a #NO MATCH in a formula? Its not an ISERROR or anything. And if I write something like =if([HelperA]#1="#NO MATCH",[HelperB}#1,[HelperA]#1)

And you cant use it through an IF statement with the MATCH formula cause the match formula always returns a value or #NO MATCH.

Tags:

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!