Index / Match ?

[Mike]

In sheet 3, A1 enter =MATCH(Sheet2!O1,Sheet1!C:C,0), in B1
enter =INDEX(Sheet1!A:A,A1), in C1 enter =INDEX(Sheet1!
B:B,A1), in D1 enter =INDEX(Sheet1!F:F,A1), in E1 enter
=Sheet2!I1 then fill down as many rows as needed. Of
course if your data doesn't begin on row 1, then adjust as
needed.

If the data in Sheet2 is not found in sheet1 you'll
receive #N/A errors in the MATCH function. You can adjust
the formulas to deal with this, depending on your
situation to return zeros or empty strings. For example
in B1 use this instead:
=IF(ISERROR(A1),"",INDEX(Sheet1!A:A,A1))

Or if you don't want to see the #N/A errors, you could use
in A1:
=IF(ISERROR(MATCH(Sheet2!O1,Sheet1!C:C,0)),"",MATCH(Sheet2!
O1,Sheet1!C:C,0))
then in B1 use:
=IF(A1="","",INDEX(Sheet1!A:A,A1))