Partial data match

[maryflower]

Dear excel experts,

I will be thankful if someone can help me achieve results using formulas.

I will have an user entering list of movies in the sheet1 - these need not be the complete name of the movie (for example: Movie "The Americanization of Emily" can be provided as Americanization or Emily or both in one cell)

I have source data listed in sheet 2 where I have a list of all the movies with year of release - again either complete or incomplete and these are delimited. (for example: The movie name can be listed as "Americanization of Emily" with each work in seperate cell and year of release 1964 in another cell)

I am looking for an output where the information in sheet1 is looked up in sheet 2 and pull the year of release. (here the answer is 1964)

Vlookup will not work as its not an exact match.

Any help is appreciated. I have attached the sample file for your reference.
Thanks a lot as always

[Richard Buttrey]

Why have you chosen to split the source data by delimiting with the space character? You would stand more chance if they were all in a single cell per row.

[maryflower]

Hi Richard,

Thanks for the reply. I delimited because I will get words in separate cell which will help me to match back to what user entered. That was the thought behind delimiting.

[maryflower]

Dear Experts
Any help on this please? I am okay if the information does not have to be delimited. I am attaching the revised file.

Thanks in advance