Populate column if cell's text matches cell in another sheet

Hello!

I recently got a new job, and one of my tasks is to assign a big amount of suppliers into categories. For example "Company Inc." supplies cars, and I want to only type once that "Company Inc." supplies us with cars. Another company, say "Work Co." supplies parts, and I want to only fill in once that "Work Co." supplies us with parts. And so on. There's several workbooks with several hundred suppliers, but they all belong in 1 of 7 categories ("Cars", "Parts", and so on).

I have attached an example of how one of the workbook looks like. I have manually filled in one of the sheets with category data. The next sheet is another region, and I wonder if there's a formula that can make my work easier? If one of the companies in the sheet called "Target Sheet" matches one of the companies in "Data Source", I want the category column to fill in the correct category based on the sheet which is already categorized. For example, "Random company" exists in both sheets. Is there a formula that can check that "Random company" exists in the "Data Source" sheet, and input the adjacent category into the category column in the "Target Sheet" sheet?

I hope you can help me out, and that my explanation is sufficient.

Thank you for quick answers both of you. However, when I try out both benishiryo and TheCman81's formulas I get error messages saying the formula contains an error on both. I am by no means an expert Excel user, and I can't seem to figure out exactly what is wrong with the formulas. I have the 2010 version of Excel, if that matters at all.

Thank you Tony! Guess I should have been able to figure that one out on my own... benishiryo's formula works perfectly, gives me blank cells when there is no match. TheCman81, yours give me a #N/A when there is no match, but works exactly the same otherwise. Thank you all!

A follow-up question: Is it possible to have the same formula check several sheets for a match? In that case, what happens if there's a match in two or tree sheets?