Searching strings by vlookup within another string.

[vbanoguru]

Gurus,
I need help using vlookup to look for a string within a longer string to match external and internal data. I have tried the usual, =VLOOKUP("*"&B2&"*", $A$2:$F$3,5,FALSE) and it did not work for the string.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Rownum[/TD]
[TD]Pub Number[/TD]
[TD]Type[/TD]
[TD]Family[/TD]
[TD]date[/TD]
[/TR]
[TR]
[TD]32
[/TD]
[TD]TW201247839[/TD]
[TD]Application[/TD]
[TD]US20120241681, TW201247839, US2011467412P, US2011557933P, US2016-15060363, US20160190480[/TD]
[TD]3/25/2011[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]US7989644[/TD]
[TD]Another One[/TD]
[TD]US7989644, CN101184822A, EP1885818, EP2005104599, EP2005107908, JP2008545729, KR2008018218, US20090131673, WO2006128800[/TD]
[TD]5/30/2005[/TD]
[/TR]
</tbody>[/TABLE]

So, what am I doing wrong? It should work. I tried the vlookup with Rownum column and it works fine. Anyone has suggestion to make it work?

 

[oldbrewer]

[TABLE="width: 710"]
<colgroup><col><col><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Rownum[/TD]
[TD]Pub Number[/TD]
[TD]Type[/TD]
[TD]Family[/TD]
[TD]date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]TW201247839[/TD]
[TD]Application[/TD]
[TD]US20120241681, TW201247839, US2011467412P, US2011557933P, US2016-15060363, US20160190480[/TD]
[TD]3/25/2011[/TD]
[TD]present[/TD]
[TD]#####[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD]US7989644[/TD]
[TD]Another One[/TD]
[TD]US7989999, CN101184822A, EP1885818, EP2005104599, EP2005107908, JP2008545729, KR2008018218, US20090131673, WO2006128800[/TD]
[TD] [/TD]
[TD="colspan: 2"]not present[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=IF(ISERROR(SEARCH(B2,D2)),"not present","present")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[vbanoguru]

present/not present wont work. Based on vlookup I need to add additional data into each row. So, I really want Excel to spit out exact Pub Number in result. I am working on essentially two pieces of data. The internal and external data doesn't match row by row. using vlookup, I am trying to incorporate additional data from external source into internal document.

 

[oldbrewer]

I am not clear what you want - find a pub number within a string in another cell - it is there or not - if there, what do you want returned ?

 

[vbanoguru]

Find the pub number in Family column, if it is there, return the first member from family from that cell into a new call. In most cases pub number is not the first member of the family.

 

[oldbrewer]

[TABLE="width: 999"]
<colgroup><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Pub Number[/TD]
[TD]Type[/TD]
[TD]Family[/TD]
[TD]date[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]TW201247839[/TD]
[TD]Application[/TD]
[TD]US20120241681, TW201247839, US2011467412P, US2011557933P, US2016-15060363, US20160190480[/TD]
[TD]3/25/2011[/TD]
[TD]US20120241681[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]US7989644[/TD]
[TD]Another One[/TD]
[TD]US7989999, CN101184822A, EP1885818, EP2005104599, EP2005107908, JP2008545729, KR2008018218, US20090131673, WO2006128800[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]col A[/TD]
[TD]col B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]formula giving first member of family[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]=IF(ISERROR(SEARCH(B2,D2)),"not present",LEFT(D2,SEARCH(",",D2)-1))[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]