populate cells with data from another sheet

[MarkoBrit]

Hi. I have a sheet that has company names and a code. I want to match that code next to the company name in a different sheet, the problem is the company name in each sheet may differ for example " Coca Cola Ltd in one sheet and just "Coca Cola in the other. Is there any way I can automate it other than doing a VLOOKUP

 

[Z51]

You can do partial matches with VLOOKUP by adding a wildcard character, e.g.

=VLOOKUP(A1&"*",Sheet2!A:B,2,0)

In your example, if A1 was "Coca Cola", it would find a match to either "Coca Cola" or "Coca Cola Ltd" or "Coca Cola Inc". You couldn't do the reverse, though, meaning if A1 was "Coca Cola Ltd", it could not find just "Coca Cola" in the search list. Using fuzzy logic for matching is generally quite a bit more complex than this.