cluo21
New member
Hi guys, I've looked through the other feeds in this forum about wildcard vlookups and wildcard index(match) but was unable to find a solution to my problem.
I'm trying to cross-reference companies from a larger database, where currently the company's name is longer than the name in the database (i.e.: looking for 'Travis Perkins Group' from 'Travis Perkins'). I understand that one requirement has to be that, within vlookup at least, it needs to be able to find the whole string in order to correctly process it. Below are the formulas I've tried without success:
=IFERROR(VLOOKUP("*"&A5&"*",'Advertiser Report'!$A$1:$Q$773,1,0),"")
=IFERROR(INDEX('Advertiser Report'!$A$2:$A$773,MATCH("*"&A5&"*",'Advertiser Report'!$A$2:$A$773,0)),"")
=IFERROR(LOOKUP(2^15,SEARCH(A5,'Advertiser Report'!$A$2:$A$773),'Advertiser Report'!$A$2:$A$773),"")
Then I thought about trimming extra letters off of the right side, so to take off 'Group' so I applied the following formula:
=IFERROR(INDEX('Advertiser Report'!$A$2:$A$773,MATCH("*"&LEFT(A5,LEN(A5)-6)&"*",'Advertiser Report'!$A$2:$A$773,0)),"")
But the problem I run into then is that I have companies such as "Shell" for which this formula would erase the cell content completely.
Is there a formula out there that would enable me to return data when the thing I'm looking up (i.e.: Travis Perkins Group) is longer than where I'm looking it up in (i.e.: Travis Perkins)? Without using VBA or the add-in?
Thanks!
I'm trying to cross-reference companies from a larger database, where currently the company's name is longer than the name in the database (i.e.: looking for 'Travis Perkins Group' from 'Travis Perkins'). I understand that one requirement has to be that, within vlookup at least, it needs to be able to find the whole string in order to correctly process it. Below are the formulas I've tried without success:
=IFERROR(VLOOKUP("*"&A5&"*",'Advertiser Report'!$A$1:$Q$773,1,0),"")
=IFERROR(INDEX('Advertiser Report'!$A$2:$A$773,MATCH("*"&A5&"*",'Advertiser Report'!$A$2:$A$773,0)),"")
=IFERROR(LOOKUP(2^15,SEARCH(A5,'Advertiser Report'!$A$2:$A$773),'Advertiser Report'!$A$2:$A$773),"")
Then I thought about trimming extra letters off of the right side, so to take off 'Group' so I applied the following formula:
=IFERROR(INDEX('Advertiser Report'!$A$2:$A$773,MATCH("*"&LEFT(A5,LEN(A5)-6)&"*",'Advertiser Report'!$A$2:$A$773,0)),"")
But the problem I run into then is that I have companies such as "Shell" for which this formula would erase the cell content completely.
Is there a formula out there that would enable me to return data when the thing I'm looking up (i.e.: Travis Perkins Group) is longer than where I'm looking it up in (i.e.: Travis Perkins)? Without using VBA or the add-in?
Thanks!