Find if a word (from a list) is contained in a text
I have my table1 with a list
Column A : Client (example : EDF)
Column B : Salesperson associated to the client
I have my table2 on another sheet. The sales person types in the name of the client everyday, but it is sometimes surrounded with other words (example : "Groupe EDF", or "EDF LYON"). I would like a formula to identify that it has a word from table1 in it : "EDF" in it. And have the associated salesperson.
In other words, I would to search if the name of the client of table number 2, has a word from table number 1 in it.
The final goal is to collect the salesperson associated to the client, on table1
I have tried with countif and contains but I can't make it work with a list.
Answers
-
hi @Wethepeople, you can put a formula in Column B of table 1 to collect the salesperson. I'm going to assume the salesperson enters their name somewhere in table2 so you can collect their name:
=INDEX(COLLECT({Table2 Salesperson}, {Table2 Client Column}, CONTAINS(Client@row, @cell)),1)
-
Hi,
Thanks for your help, It wasn't clear sorry : no I don't have the name of the sales person on table 2, that's why I am trying to find.
On table1, I have which client (colum A) is associated to which salesperson (column B)
: example
Client Salesperson
EDF Adam
Ieseg Lydia
On table 2, I have the name of the client (column A') (but sometimes they add the name of the service or the city like : "EDF Services France" or "Groupe EDF Services"
On column 2', I would like it to find that it corresponds to EDF on table1 and find the matching salesperson : Adam.
Thanks a lot,
-
You are going to have to have your users enter just the client name. Maybe give them an additional field to fill out the title they 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!
-
Thanks for your answer but I can't do that unfortunately :(
To sum up, I want to see if the client they type in in table2, contains a word from my list in table1, if it does then return the name of the client.
table1 : a preset list of who is in charge of each client
Client Salesperson
EDF Adam
etc
table2 : find the matching salesperson from table1, IF "Client" contains a word from table1
Client Salesperson
EDF Services x
Groupe EDF Lille x
As they contain "EDF", I would like in both cases, x = Adam. As EDF is matched to Adam on table1
Thanks a lot,
-
I understand what you are wanting to accomplish, but without some kind of consistency, there is no way to accomplish it. Even if that consistency is always using spaces and never using more than a certain number of "words", you have to have some kind of consistency. If they will never go more than (for example) 4 words and always use spaces in between each, or they always put the client first (or last or second or whatever), or they uses spaces for everything except use dashes or quotes on either side of the client.
But without having something that allows us to parse the string or characters that can be indicators to strip it out, its just not going to be possible.
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!
-
Thank you for answer, I'm not sure I understand the concept of consistency but there won't be more than 20 words for "Client" in table2 (just to be safe).
I would like to be able to collect at least the name of the matching client from table1.
Example : in table1, someone types in : GROUPE EDF SERVICES
It recognizes that it has "EDF" from the list in table1, and returns "EDF". And then I just have to do a vlookup to find the matching salesperson.
I have this formula for the moment in my table 2
=IF(COUNTIFS({Table1-Client}; CONTAINS(Client@row; @cell); {Table1-Client}; "Adam") = 1; "Adam";... And so on for each sales person
But it only works if it's exactly the same.
Thanks for your help,
-
Right. So if the client is always the second word then we can make it work. If it will never be more than 20 words, then that is doable, but it requires 20 helper columns each with unique formulas to parse each word out. That also assumes that the delimiter between words is always the same. So "GROUPE EDF SERVICES" or "GROUPE-EDF-SERVICES" would work. But if it could be either "GROUPE EDF SERVICES" or "GROUPE-EDF-SERVICES" or "GROUPE EDF: SERVICES" or any other variation, then it is not consistent and will not work.
I definitely understand what you are wanting to do, and I understand that your existing formula only works if it is an exact match. But there is nothing that can be done without some kind of consistency in how the strings are entered.
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!
-
Hi,
Do you think there would be the solution if the sales person doesn't enter their name in table2 ? On table 2, I want to collect "EDF".
I want to see if the client they type in in table2, contains a word from my list in table1, if it does then return the name of the client.
table1 : a preset list of who is in charge of each client
Client Salesperson
EDF Adam
etc
table2 : find the matching salesperson from table1, IF "Client" contains a word from table1
Client Salesperson
EDF Services x
Groupe EDF Lille x
As they contain "EDF", I would like in both cases, x = Adam. As EDF is matched to Adam on table1
Thanks a lot,
-
Hi,
What would be the formula if I want to look for the word from my list in table1 in the first 5 words of my entry in table2 ?
-
You would need to create 5 helper columns and use a parsing solution (there are a few already out here in the Community) to pull each of the 5 words into their own columns. Then you would nest a series of IFERRORs and INDEX/MATCHes pulling from the base list for each of the 5 columns.
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
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!