CODEWITHSUNDEEP
excelexcel-formulavlookupworksheet-function
Landers
Landers

Reputation: 89

Combine two worksheets into a third

I have two worksheets I want to combine and display in a third. The first worksheet is a floating Employee List I update weekly. The second is the Hours worked itemized by each employee. The lists share the Employee_Id as a Unique Identifier.

So If Emp_ID = Emp_ID then display Emp_ID Location Terminal (both from the first sheet) and display Hours PTO Overtime and Total Hours (from the second sheet).

Sample View of Data Sample View of Data

Upvotes: 0

Views: 118

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

Use this formula, it does not require copying and pasting between sheets:

=IFERROR(VLOOKUP($B2,Sheet1!$A:$D,MATCH(C$1,Sheet1!$A$1:$D$1,0),FALSE),VLOOKUP($B2,Sheet2!$A:$H,MATCH(C$1,Sheet2!$A$1:$H$1,0),FALSE))

The headers need to be an exact match to the headers on the other two sheets.

Upvotes: 0

pnuts
pnuts

Reputation: 59495

Assuming Emp_ID is in A1 for both the first two sheets, copy A:F of Sheet2 into A1 of Sheet3. Once there add Department into G1, delete ColumnB and insert a new ColumnA. In G2 and copied down to suit: 

=VLOOKUP(B2,Sheet1!A:D,4,0)

Upvotes: 1

Related Questions