VLOOKUP Partial matches when searched text is longer than actual text

I am trying to design a search bar in Excel. I do not want to use an add-in, such as the Fuzzy Lookup Add-in.

Cell AU1 is my designated search bar. Say the search bar is designed to be used to search movie titles.

Columns A to Z contain a relational database of movies, such as movie titles (in column A) and additional movie information, such as year of release, director, studio, etc. (columns B to Z). This table is titled "Movies".

Say I want to be able to search a movie title in AU1--for example, I want to be able to type "GoodFellas" into AU1 and automatically populate AU2-AU27 with information on the movie GoodFellas from the "Movies" table.

I want AU2 to show the movie title ("GoodFellas") if I search "GoodFellas" in AU1 (additionally, the subsequent cells will populate with "Scorsese" and "1990" etc., information from the "Movies" table). In addition, I want to be able to type in "Goodf" in AU1 and have the cells automatically populate with relational information on "GoodFellas".

So effectively, I have populated AU2 with the following formula (the leading conditional merely ensures that when AU1 is empty, the other cells are also empty):

Please Login or Register  to view this content.

However, this code does not work if I want to search "Good Fellas" or "GoodFellasss" (i.e., AU2 is automatically populated with "#N/A" instead of "GoodFellas").

Some guidance would be appreciated!

The INDEX/MATCH formula appear to be doing the same thing my aforementioned VLOOKUP formula is doing when I type in an longer incorrect match (e.g., Goodfellassss or Good Fellas) but seems to be handle shorter incorrect matches (e.g., Goodf) , but instead of showing a "#N/A", there is a blank, which I suppose is caused by the fact that there is an error.

Thanks for the response. However, I would like to know if there is a way to look up a longer or misspelled entry.

I've never seen one that I recall. Nesting some kind of very involved MID function combinations into MATCH might work, but I would scarcely know where to begin. Perhaps someone else will join in and correct me on all of this. Sorry I couldn't help you with that part.

you could try to error catch and have it look up one less letter everytime until it gets what it is looking for.

=iferror(vlookup,AU1,table range,2),iferror(vlookup(Left(AU1,10),table range,2),iferror(vlookup(Left(AU1,9),table range,2),iferror(vlookup(Left(AU1,8),table range,2),iferror(vlookup(Left(AU1,7),table range,2),iferror(vlookup(Left(AU1,6),table range,2)....ect and then if that still comes up with an error you can have a second column in your table range that has key words and have on of the error catching lookup that


it probably isnt what you want it to do but it might work