Wildcard VLookup or Index(Match) Problem

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!

Hi,

I see you are from Austin, my favourite US city

The problem is that the full name is not in the lookup table but it is in the lookup fields, this will always cause difficulty.

This works for this particular instance, but if you have other instances such as Travis Perkins gmBH you might have too many combinations to cater for

=IFERROR(VLOOKUP(TRIM(SUBSTITUTE(A5," Group","")),'Advertiser Report'!$A$1:$Q$773,1,0),"")

Hi Bob - thanks for the reply and the suggestion. Like you correctly pointed out, there are several occasions where this will occur with 'Group', 'Inc', 'PLC', etc so the formula you posted is a bit limiting.

While I'm starting to understand that there is no "one size fits all" solution here, I just wanted to make sure that there's no other more general formula which will help for this inquiry - right?

Does every corporate in your lookup table have some sort of incorporation label on the end, or are some just brand names?

Mix of both, some will have Inc/PLC/Group/etc. attached, and some will just be brand names like Shell/PWC/etc.