Searching column with string for wildcard match

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.

-------------Problems Reply------------

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.

Searching column with string for wildcard match

The formula in C1 is,

=INDEX(B:B, MATCH("*"&E1&"*",B:B, 0))

... or,

=VLOOKUP("*"&E1&"*",B:B, 1, FALSE)

They accomplish the same thing.

Category:regex Views:1153 Time:2015-03-17
Tags: regex excel

Related post

Copyright (C), All Rights Reserved.

processed in 0.094 (s). 11 q(s)