up vote 1 down vote favorite
share [g+] share [fb]

Is there a formula or script that will match sub-strings from one column in another column and return the full value of the sub-string match? e.g.

Input

COL_a            COL_b
name             201284_blah_year.ext
address          407284_blah_name.xml
journal          501283_blah_journal_entries.ott
journal_entries  701284_blah_type.dat
year             401184_blah_journal.ext
type             301287_blah_address.txt

Desired output

COL_a            COL_b
name             407284_blah_name.xml 
address          301287_blah_address.txt
journal          401184_blah_journal.ext 
journal_entries  501283_blah_journal_entries.ott 
year             201284_blah_year.ext 
type             701284_blah_type.dat
link|improve this question
67% accept rate
Trying to figure out a macro or formula, but you can always just use text filters one by one depending on how many records there are – Raystafarian Feb 4 at 1:08
ideally it could support 100s.1000s of value – toop Feb 4 at 1:17
=IF(COUNTIF(B:B,"*"&A1&"*")>0, TRUE,FALSE) will give you a true or false depending on if the sub string is found in the array, just need to return the found value for true – Raystafarian Feb 4 at 1:20
@Raystafarian - would you suggest rolling it in VBA instead? – toop Feb 4 at 3:29
feedback

1 Answer

up vote 4 down vote accepted

On Column C, write the below formula:-

=VLOOKUP("*_"&A2&".*",B:B,1,FALSE)

and then drag down this till end.

For your quick reference, below is my screenshot

enter image description here

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.