Excel 2007 : Matching critera from two different spreadsheets

I have two spreadsheets and I'm needing a little help!

In the first spreadsheet, I have a listing of companies in column a, and a listing of materials in column b.

In the second spreadsheet I have a listing of various bits of information which includes a listing of materials in column c.

I'm wanting to see if there is a formula that can be entered that will compare the materials on both spreadsheets and if any of the materials match within the range it populate the company name on the second spreadsheet.

Example;

Spreadsheet 1:
A1: Motion B1: 500111111
A2: Motion B2: 500222222
A3: SafetyNet B3: 500333333

Spreadsheet 2:
A1: Misc B1: Misc C1: 500222222
A1: Misc B1: Misc C1: 500333333
A1: Misc B1: Misc C1: 500111111

Needing the formula to populate column D on the second spreadsheet with the corresponding company in column A from the first spreadsheet.

Meaning if a material in columnC on spreadsheet2 matches a material in columnB on spreadsheet1 that it populate the company in columnD on the second spreadsheet from the information in columnA on spreadsheet1.

Any ideas?

Try

=IF(ISNUMBER(MATCH(C1,'Spreadsheet 1'!B:B,0)),INDEX('Spreadsheet 1'!A:A,MATCH(C1,'Spreadsheet 1'!B:B,0)),"")

copied down after changing sheetname and references to suit.

Great, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save