I can't seem to figure out the VLOOKUP magic needed to make this work as I want it to.
See, what I've got is a column B containing filenames, like this:
[COLUMN B] ./11001 Boogie Oogie Oogie (A Taste Of Honey).wav ./11001 Rescue Me (A Taste Of Honey).wav ./11001 Sukiyaki (A Taste Of Honey).wav ./11002 Memory (Acker Bilk).wav ./11002 Stuck On You (Acker Bilk).wav ./11002 Could I Have This Dance (Acker Bilk).wav ./11002 Do That To Me One More Time (Acker Bilk).wav ./11002 This Masquerade (Acker Bilk).wav ./11002 Just Once (Acker Bilk).wav
And so on for 6220 entries.
I have another column, Column E, which contains a TRACK NAME which is present within the filename. Looks like this:
American Patrol Artistry In Rhythm Begin The Beguine Big John's Special Cherokee
For example. So what I want to do is, in another column I want to search through Column B using the strings from Column E and then returning the matched string from Column B.
So if we imagine I put this formula in the C Column starting in the same row as the American Patrol track name, it would search through the range in Column B and return this:
./11249 American Patrol (BBC Big Band).wav ./11249 Artistry In Rhythm (BBC Big Band).wav ./11249 Begin The Beguine (BBC Big Band).wav
And so on.
I tried doing this formula
So, this returns a file name, but it seems to have matched all the filenames and are just returning whichever result I specify in the col_index variable, so now it returns the second match (basically, just the second row in Column B) and if I put a 3 instead, it would just return the third hit, again having matched all the file names, it seems..
I'm not that familiar with Excel functions, so I'm not sure where to look for the solution beyond this.
You should not be using
TRUE as a VLOOKUP function's range_lookup parameter on unsorted data. You can, however, wrap your track title in wildcards to achieve the search you are looking for.
The formula in C1 is,
=INDEX(B:B, MATCH("*"&E1&"*",B:B, 0))
=VLOOKUP("*"&E1&"*",B:B, 1, FALSE)
They accomplish the same thing.