Match any part of the string to lookup

[rrocker1405]

Hi team,

I'm trying to lookup values based on partial search but because it is partial search it does not lookup the complete list. for example:

[Lookup field]Column A: 12345

[Data field]Column E: 45678, 98765, 12345, 109876,456312
[Data field]Column F: Testing

the formula i've been using to get the partial lookup is
=VLOOKUP("*"&A2&"*";E:G;3;0)

The output should be testing as lookup value in column a '12345' is part of E.

Not sure, if i have explained enough to understand. Looking forward for suggestions...,

Thanks in advance...A!

 

[Deepak]

Can we have the xl for the same.

 

[rrocker1405]

Hi Deepak,

Here is the sample file for your reference as requested.

Thanks in advance.

A!

 

[Deepak]

=LOOKUP(2,1/SEARCH(D2,$A$2:$A$7),$B$2:$B$7)

 

[rrocker1405]

Thank you deepak, it worked like magic.

A!

 

[Deepak]

Welcome!!