Keyword search in column on another sheet (tab)

[JTapp]

I've made a spreadsheet (database?) of my digital movie collection. I've got about 750 files over six different portable hard drives. Some of those are duplicates, as happens over usage and multiple people in my family using them.

The spreadsheet is done in Excel 2010, and it consists of several tabs/sheets (Movies, TV Shows, Web Videos, etc.). On the movies tab, each line starts with the Title (Col. A), Hard Drive (B), and so forth that includes the filepath, file name, year, genre, rating, short description, etc.

I've made another sheet that is just a search. I'm trying to figure out the best way to do a keyword search in the Title column on the Movies tab. I'm not wanting an exact match, because someone may be typing it wrong, or only typing part of the title (such as instead of typing "The Aristocats", they only type "Aristocat").

I've looked at the LOOKUP and VLOOKUP functions, but the results of non-exact searches isn't getting me what I want.

I have this:

=VLOOKUP($C$2,Movies!$A:$A,1,TRUE)

C2 is the search box that the user can type into.

But, if it isn't an exact match, then it returns the movie title of the line above.

Also, I'd like that if it exists in multiple locations, I want the results shown to be all matching results.

I'd like to steer away from VB right now, and see what Excel can do.

Thanks!

 

[JTapp]

Alright, I've been doing some research and trial and error.

Here is what seems to be working for me on the search part:

=VLOOKUP(CONCATENATE("*",$C$2,"*"),Movies!$A:$A,1,FALSE)

I can do a wildcard search using VLOOKUP looking for an exact match (sounds strange doing a wildcard search for an exact match). I haven't been able to get any version of index-match working for me. However, I'd like to make it work for me so that I can do the second part of my question, which is returning multiple matching answers if possible. Is there a way for Excel to dynamically adjust the available cells to display the results?

Also, is there a way to modify what I have above to be able to accept multiple results?

 

[JTapp]

Alright, I've got Index-Match working now.

=INDEX(Movies!A:A,B4,1,1)
where B4 has this: =MATCH((VLOOKUP(CONCATENATE("*",$D$2,"*"),Movies!$A:$A,1,FALSE)),Movies!$A:$A,0)

Still looking to make dynamic list of possible matches.

 

[JTapp]

Okay, talking to myself here, but maybe this can help someone else.

I'm still working on it reporting multiple instances of a keyword.

Now, I've got an additional column which I plan on hiding, with the formula:

{=SMALL(IF(Movies!$A:$A=VLOOKUP(CONCATENATE("*",$D$2,"*"),Movies!$A:$A,1,FALSE),ROW(Movies!$A:$A)),ROW(Movies!1:1))}

And in the visible cell, it says:

{=INDEX(Movies!$A:$A,B4,1)} (B4 is the cell with the formula above)

I'll work error trapping into it later. For now, this is reporting multiple instances, as I copy&pasted into the next 15 cells below.

It's working, partially.

My first issue now is that, say for the movie "Brother Bear", it reports 4 instances of movie titles with "Bear" in it. That catches all the titles "Brother Bear" (rows 97-100) but there are other places where there is "Brother Bear 2: The Moose On the Loose" (rows 101-102). What's different? Why are the other two not showing up?

My second issue is that the title column is working fine, is it possible to reference the cell this retrieves the information from to use in other information reported back? Or do I need to have such a complicated formula for each bit of information I want to retrieve?