Excel: How to match 2 columns with search keyword

I have 2 different spreadsheets That in one of them i have a title and an ID number. In the second one i have filenames. Most of them for e开发者_如何学Goxample looks like this: Movie_Title_Name.mpg

Now in the first sheet i have the title looks like this: Movie Title Name

My goal is to add the ID to the filename sheet next to each filename using an script or if their is some other way to do this using excel. I cannot do this manually because i have more than 1000 items in my sheet.

Thanks in advance!

You can do this quickly with Excel formulas. This formula assumes two things:

1. The file names only contain a period at the start of the file extension; and
2. The IDs are to the right of the Movie Titles on the Title sheet.

To return the ID number for the filename in A1 on the filename sheet, use the following formula.

=VLOOKUP(LEFT(SUBSTITUTE(filenames_sheet!A1,"_"," "),FIND(".",filenames_sheet!A1)-1),titles_sheet!$A$1:$B$1000,2,FALSE)

titles_sheet!A1:B1000 is the table of Movie Titles in column A and ID numbers in column B.

If the IDs are to the left of the Movie Titles on the Title sheet, a different formula is required:

=INDEX(titles_sheet!$A$1:$A$1000,MATCH(LEFT(SUBSTITUTE(filenames_sheet!A1,"_"," "),FIND(".",filenames_sheet!A1)-1),titles_sheet!$B$1:$B$1000,0))

Either of these formulas can be filled down the entire column.