Find a Match withing a Join?
How can I use MATCH to find if an instance occurs within a group of values joined with JOIN?
For example, if I join the values in three columns (Red Yellow Blue), no commas just a space, and I want to MATCH the value in a cell (say, "Red"), how can I see if the joined values (Red Yellow Blue) contains Red?
Right now, the result is no match, because the cell is not a perfect match.
I thought about finding a way to match the value in a cell (say, "Red") across three columns (rather than adding a helper column that joins the three values into a single column, but then I increase my reference cells. So, I though joining the values into a single cell would help.
Comments
-
Use the FIND function and state that if the result of that is greater than zero (meaning it exists somewhere within the string) to display "whatever you want". You can also use the JOIN function within the FIND function to eliminate that helper column if you wanted to.
=IF(FIND("Red", JOIN([Column1]@row:[Column3]@row, " ")) > 0, "whatever you want")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
To use the MATCH function, you would eliminate the JOIN function altogether and just look across the range. This will also produce a numerical value, so wrapping it in an IF and stating to output "whatever you want" if the MATCH returns a value greater than zero.
=IF(MATCH("Red", [Column1]@row:[Column3]@row, 0) > 0, "whatever you want")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
You can also use the new "Contains" without the joining or matching -
=IF(CONTAINS("Red", [Column 1]1: Column 3]1), "true", "false")
-
Hi Kara,
Great tip!
Thanks!
Have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Definitely a good one. I haven't fiddled with the CONTAINS function much yet, so I had forgotten about it.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!