Comparison of two sheets. Result needs to return value of a 3rd column.

Hi guys, first time visiting this forum but I need some help.

I have 2 excel sheets, for reference lets call them S1 & S2. I need to compare Column A of S1 and Column A of S2. Based on a positive result, I need to return the value of S2 Column B in S1 Column B.

Column A of S1 and Column A of S2 don't match up 1 for 1 as there are many more rows in S1 than S2. Because of this I know I need an array of some sort. The problem I'm having is with the return value, how do I write the formula so that the return value = the corresponding value that matches in the array?

The actual data is two sheets of Customer information. Column A is the customer name on each sheet. Sheet 1 has about 2900 rows, sheet 2 has about 700 rows. For every match I need to return the email address (Column B) listed in sheet 2, into sheet 1 for the correct customer.

Is there an easy way to do this? Thanks in advance!

Wow! That's basically perfect!

Thank you