modified vlookup for partial match

[jlax34]

I have 2 spreadsheets with movie titles and other information in them. One spreadsheet is basically my "Master" list with 120K SKUs. The other spreadsheet has about 200 movie titles that need to be looked up in the master sheet so I can copy the UPC. Unfortunately while the titles are close, they aren't exact so I can't do a vlookup.


I want to be able to do a vlookup to find any titles in the master spreadsheet that contain the title from the small list. This would help me shrink the amount of data I have to search through to find the correct version (way easier than Ctrl-F over and over).

Here is a better explanation with an example:

Lets say I'm looking for the title "Buried" in the master sheet. I want the vlookup to tag all these as possibles (while excluding the titles that aren't close):

BURIED (WS)
BURIED ALIVE
BURIED ALIVE
BURIED ALIVE
BURIED ALIVE (WS)
BURIED ALIVE (WS)
BURIED IN THE SAND DECEPTION
BURIED SECRET OF M NIGHT SHYAM
BURIED TREASURES

Any way to do a "partial" vlookup? A vlookup that finds any cells that CONTAIN the referenced data?

[dilipandey]

Hi jlax34


I believe you can play with match of "BURIED" and the length + few characters to find out the nearest match.. suggest you to upload a sample workbook with your expected results. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[jlax34]

Not perfect, but I did get something worked out with Match and Len.

[dilipandey]

thats great...

Regards,
DILIPandey

<click on below 'star' if this helps>