I have a workbook (Excel 2007) with two sheets. Sheet1 has more than 8,000 employees and their associated unit numbers, which is a 6-digit ID. I am trying to populate Sheet1!C:C with a corresponding Department number with data from Sheet2.

Sheet1!A:A is the Employee name. Sheet1!B:B is the 6-digit unit ID. These numbers are not sequential and are generated by another data source, thus the match is specific and not related to a number range. Sheet1!C:C is blank and I want to populate this column with matched values from Sheet2 to fill in the associated Department ID.

Note: I have removed duplicates from another data source and created a list of unit numbers and the corresponding Department numbers on Sheet2. There are 13 Departments with just over 1,000 subordinate unit numbers. Sheet2!A:A is the de-duped unit numbers (which correspond to Sheet1!B:B), and Sheet2!B:B is the associated Department ID.

I'm totally lost on how to match values and populate another column between two different sheets and with so many specific values. Here's the kicker, I cannot use VBA because I am importing this table into a data visualization tool and it will not read any VBA or macros.

Oy vey! Many thanks in advance to any guru who can answer this.


Correct me if i am wrong. From what i understand you have two sheets with connecting data (a common value in both sheets). If thats the case you could you VLOOKUP . i have used to over workbook but make sure the data updating remarks when you use this (Automatic, manual etc).

Let me know if you need more information or if this worked.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.