vendredi 30 août 2019

Search for specific words inside one cell and write retrieved searched result in other cell

Need help for following use case:

- I have three columns, one named "Issue", second "Client" and the last one "Tags"

- Columns "Issue" and "Tags" are filled automatically after csv. file import, where each cell of Tags column may contain multiple tags, but none of Tag cells can contain multiple client tags

- Column "Clients" needs to be determined based on "Tags" column, and this is where I suck :(

enter image description here


Basically, I want to insert formula into "Clients" column that will do following:

1) Search for all possible client tags (Client A, Client B, Client C, Client D and Client E) in "Tags" column.

2) Retrieve if client tag is found and write that tag in corresponding cell inside Client column.

Wished output:

enter image description here

In my opinion, formula has to be generic to suits all Client cells, meaning formula in my opinion should contain all clients at once (A,B,C,D,E) and check if one of the client is found and write that client. What is searched and found should be written as it is.

I tried smth like this:

=iferror(if(search({"Client A","Client B", "Client C", "Client D", "Client E"},J7,1)>0,"?????","no client"))

Aucun commentaire:

Enregistrer un commentaire