Compare two excel sheets and display the common values on the third sheet

Hi All,

With the following data on two excel sheets ,I wanted to Compare sheet1 and sheet2 column(empl no) and display common column values on sheet 3 with the status.Can anyone help on this?


Sheet1
------ ------
Emp No Status
------ ------
2 open
3 open
5 close
6 open


Sheet2
------ ------
Emp No Status
------ ------
2 open
3 open
5 open
6 open
7 open



output:

Sheet3
---------------------------------------------------
sheet1Emp No Status sheet2Emp No Status
---------------------------------------------------
2 open 2 open
3 open 3 open
5 close 5 open
6 open 6 open

Hi, You can try this formula in sheet 3

Formula:

Please Login or Register  to view this content.

Thanks but I want the values to be displayed on 4 seperate columns.. Can you assist on this?

Ya remove the & symbol in the formula and paste each part in a different columns
Sheet3 A1:

Formula:

Please Login or Register  to view this content.

Sheet3 B1:

Formula:

Please Login or Register  to view this content.

Sheet3 C1:

Formula:

Please Login or Register  to view this content.

Sheet3 D1:

Formula:

Please Login or Register  to view this content.