Looking up value and filling cells

[wicked.ludicrous]

I have a set of data I'm trying to go through, and my problem is that I can't figure out a quick way to do what I want.
So if you take a look at the attachment, there's the TRI Unique Parent Company Name on the first sheet. I'd like to look up those companys' names in the other sheets (naics 31-33) and if there is a match, then fill in the CUSIP and GVKEY columns.

So originally, I tried using vlookup. The formula I used in that first sheet was

Please Login or Register  to view this content.

but I realized my problem: the contents of cell A2 in the first sheet would have to be the EXACT SAME as the 'match' in the other sheets.

So is there another way I can look for the company name in 'naics 31' without an exact match?
If you can't tell, the reason for this is because most names have a period or 'INC' at the end of their names and so don't match up perfectly with what's on the other sheets.

Thanks

[Cutter]

Do ANY of the companies listed on Sheet1 actually appear on any of the 3 naics sheets?

You could do a search on the first x number of characters - like this:

=VLOOKUP(LEFT(A2,8)&"*", 'naics 31'!$B$3:$I$16,6, FALSE)

This would look for first 8 characters plus anything that followed.

[wicked.ludicrous]

Yeah, there actually are companies that match up. I just deleted 99% of the rows to make it more easy to understand.

[Charlie_Howell]

You could use "Find and Replace" to regulate all of the company names to the same criteria, then your formula would work just fine. There is no substitute for "clean" data.