Searching for any of the words in a cell from another cell

1
votes

So I have a question: Let's say I have 3 columns: Artists, Tracks, Sublabel in 2 sheets.

Here's the example sheet

What I need is a function that searches for the value in the cell "tracks" in Sheet2 from Sheet1,

Once find the value we are looking for, I want it to check for any words that the cell "Artists" in sheet2 may match the words in the cell "Artists" from sheet1, if one of the words is a match then go ahead and get the value from cell "sublabel" in sheet1 to write in cell "sublabel" in sheet2.

(We do not wanna check for the exact string as the one in sheet2 as they may be slightly different having words in a different order and some missing, but usually they are separated either by a comma, fullstop, space or a short word like "ft.". "Tracks" cells will always have the same values/strings in both sheet tabs, but the "Tracks" may be by a different "Artist" that being why the "Artists" cell must contain at least one of the words from the other sheet.)

I've tried to use the following function but it's not doing exactly what we need though as we need a way to check if artist is correct but can't use exactly the same values to search:

=dget(Sheet1!A:C,"Sublabel",{"Artist","Track";A7:B7})

Hope someone can help us! Thanks!

1
google-sheetsgoogle-sheets-formula
What is the function dget? If it wasn't because of the random words like "ft" or the commas, it would be very easy to do with functions. – Jescanellas
From what I know its something like a database function... not quite sure of it too but I just learned it from examples I saw online. :( sadly it has a lot of random words that I can't do anything about :( how to do it with functions if not for the random words? Which function would work ? – Cheryl
Check on Tools > Script editor if there is a function named dget. If so, please edit your original question with the code in it. Otherwise we can work on a script to get rid of the random characters :) – Jescanellas
=DGET() definitely exists @Jescanellas, just look here for the documentation: support.google.com/docs/answer/3094148?hl=en – a-burge
Can you provide examples of your expected answer @Cheryl? – a-burge

1 Answers

0
votes

I can't get the exact result you want, a table filled in as you proposed. But the following might be useful depending on what you want to do with the data.

This provides a dynamic listing of your data, where you can select a track title, or part of a title, and any part of an artist's name, to filter the results.

I added a sample tab to your sheet: Sheet2-GK. The formula, which is build dynamically, based on the filter values you select, looks something like this (sample criteria being used):

=QUERY(Sheet1!A2:C19,
   "select *  where A contains 'Sam' and A contains 'Kygo' 
    and  upper(B) contains 'GIVE' order by A,B",0)}

It could be enhanced, to permit "AND/OR" selection of two artists' "names" for example. "Names" here refers to any one word found in the Artist field. Eg. Diana Ross would show as two unique names, "Diana" and "Ross".

Let me know if this is helpful, or if you really need that initial full report you described in your question.