Vlookup, Match (Search or Find) partial string within string in a Cell

Hello Excel Gurus/Experts,

Below is the situation that I'm hoping you guys could help me out.

String		To Search String	Result	Original 'String'
05DHAT123BLCA; 05DHCT234BLTH		TC*	05QHDQ345	05QHDQ345TCOM; 05FIXX456BCLU
05QHDQ345TCOM; 05FIXX456BCLU		BL*	05DHAT123	05DHAT123BLCA; 05DHCT234BYTH

Given the data, what formula should I use in order to give me the result shown?

i.e. Look for TC* (where * is wild card) and then SEARCH the column in 'Striing' (in this case, it's 05QHDQ345 - NOTE that anything from TC onwards is deleted) give me the row of the data that has the string shown.

What if you get two matches?

Hi,

Could do with a bit more information. In your example, the Result to be returned corresponding to a Search String of TC* is 05QHDQxxxTCOM; 05FIXXxxxBCLU.

Is this to say that this is the only string in the list which contains that particular Search String? And does this apply to all of your strings, i.e. for each Search String there is precisely one String which contains that Search String?

Regards

Hi dluhut

Perhaps something along these lines, assuming your data is in A1:B3. In C2 and copy down.

=LOOKUP(20^20,SEARCH($B$2:$B$3,A2),$A$2:$A$3)

Edit: I have removed the asterisks in B2:B3.

The following approach works without asterisks, i.e. search string = TC:
=INDEX($A:$A,MATCH(9E+99,FIND(C2,$A:$A)))
Where C2 is the search string and A:A contains the first string. It will return the first result it finds.

Enter with Ctrl + Shift + Enter. Done correctly, you get:
{=INDEX($A:$A,MATCH(9E+99,FIND(C2,$A:$A)))}