Search for text within a string, lookup and then return item

are the "keywords" on Sheet2 in separate columns or is it one long text string ?

p.s. not sure I like the idea of film # 2

They are in separate columns (there were in a comma separated list but I text-to-columns'd them).

EDIT: sorry, I was bored so made that up...we don't really have a film on Donkey fighting :-D

If we assume then that your film titles are located

Sheet1!A2:A4

and that your keyword table is located

Sheet2!A2:Z10

with keywords being located in columns C onwards and Channel in Col A

then... one possible yet hideously inefficient method to return Channel to Col B on Sheet 1 based on movie name might be:

Please Login or Register  to view this content.

The LOOKUP is used to handle those films that fail to return a Channel given no keywords can be located in the title.

Here is a working example based on your data with a few extra checks thrown in for good measure (remember it is an Array so you must commit the formula holding CTRL + SHIFT + ENTER not just Enter)

Could you break it down a little as to what the constituent parts do and how it functions as a whole?

Yikes... that could take a while in truth... if we look at it like this:

Please Login or Register  to view this content.

the sections in red we will ignore as essentially this is used purely to cater for when no keywords can be found in the movie title.... so we're left with:

Please Login or Register  to view this content.

The INDEX reflects our results range - our channels... what comes after that is used to determine the row_number in which a keyword is first found in the title, so this:

Please Login or Register  to view this content.

Basically says if the keyword is not blank

Please Login or Register  to view this content.

and (by means of *) the keyword can be found within the movie title

Please Login or Register  to view this content.

then return the ROW number that contains the found keyword

Please Login or Register  to view this content.

else if you can't find it return the ROW above the last row in our table

Please Login or Register  to view this content.

This will generate an array of numerical values, one for each keyword in our keyword table... against which the MIN is applied

Please Login or Register  to view this content.

If none of the keywords have been found the MIN will return 11 as the entire array will be full of value 11 (not found output), however, if any keywords have been found they will return a lower number than 11... the MIN will thus pull the lowest number which if < 11 means a keyword was located.

The INDEX uses the above output to establish which Channel to be returned

Please Login or Register  to view this content.

If no values were found and MIN is 11 then the INDEX will generate a #REF! error as there is no Row 11 in the range A1:A10 ... this is where the LOOKUP comes in as it handles those errors and returns a Null (this can be altered).

If you post up your test file in which you're failing to get the desired results I will try and take a look at some point later today.

Care to post an example of what you want to do ?

Not entirely clear... can you outline the purpose of each column ?

As is stands...

Col D appears to be a search with keywords matched against Film Keywords rather than Title (as implied)

Col E appears to be a search of both tag & keywords against Film Title

Col F is as before - ie a search of keywords against Film Title.

You can concatenate B2 & C2 to create one string into which the keywords search (ie concatenation of film keyword becomes akin to film title)

Please Login or Register  to view this content.

is that what you meant ?

there may be better methods now you're essentially splitting the film title in effect into separate cells with one word in each ... I would need to have a think - if that is indeed the intention ?

If this is along the right lines I would suggest perhaps another sample file with more films etc which reflect different scenarios such that we can conduct some tougher testing.