Pull data from one column based on the company names in 2 others

Hi everyone!

I've been mulling over how to handle this problem for a bit and can't quite figure it out. Here is what I am trying to accomplish:


Capture.PNG
I need to compare the first and third columns to see which companies are present in both, so I highlighted the duplicates, no problem. However, the end goal is cross-referencing the companies in Column A with those in Column. The quirk here is that the two lists of companies are very different lengths because some have assets and some do not. If a company has an asset -- like Company 1, in the example -- each asset is listed on a different row with all the rest of the same company info (name, etc.) populated in that row. Thus, some companies in Column A will have one row and others may have 5, while each company in Column C is only listed one time.
Goal: To cross-reference Columns A and C, and pull the Company Size from Column D into a new column. The company size should populate on each of the rows where a matching company name is found from Column C. The Company Size in Column D, as shown, corresponds with the Company Name in Column C and NOT with the Column A list. So for instance, Column E's New Company Size should say LESS_50 on both rows 2 and 3 because Company 1 has fewer than 50 employees.

Any ideas on how to get this done would be greatly appreciated! The company lists have between 1700-2100 entries, so going by hand is no bueno.

Thanks a ton!

Hi TMS,
That's exactly what I needed! Definitely going to hop onto a VLOOKUP tutorial so I learn its ins and outs. Thank you!