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.
Best Answer
-
Perhaps only have one helper column. This column is then a concatenation of the two columns you need to validate, as follows:
[Helper Column] = [Column1]+[Column2]
Your match formula then becomes
match([Column1]+[Colum2];[Helper Column]:[Helper Column];0)
This solution assumes that the result of [Column1]+[Column2] is unique, to ensure only one row matches.
===========================================
"Nothing is impossible. The word itself says 'I'm possible!'"
================================================
"Nothing is impossible. The word itself says 'I'm possible!'"
Answers
-
Perhaps only have one helper column. This column is then a concatenation of the two columns you need to validate, as follows:
[Helper Column] = [Column1]+[Column2]
Your match formula then becomes
match([Column1]+[Colum2];[Helper Column]:[Helper Column];0)
This solution assumes that the result of [Column1]+[Column2] is unique, to ensure only one row matches.
===========================================
"Nothing is impossible. The word itself says 'I'm possible!'"
================================================
"Nothing is impossible. The word itself says 'I'm possible!'"
-
Not quite, but you gave me the idea to think backwards and just create a helper status column To only show if the status is DELAYED or IN PROGRESS.
=IF(OR(Status@row = "In Progress", Status@row = "Delayed"), "IPD", "")
Then I just run the match of the helper column to look for IPD.
Thanks !
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!