Find if a word (from a list) is contained in a text

Wethepeople

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.

Lucas Rayala

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)

Wethepeople

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,

Paul Newcome

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.

Wethepeople

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,

Paul Newcome

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.

Wethepeople

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,

Paul Newcome

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.

Wethepeople

https://community.smartsheet.com/discussion/comment/378424#Comment_378424

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,

Wethepeople

https://community.smartsheet.com/discussion/comment/378635#Comment_378635

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 ?

Paul Newcome

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.