Forum Discussion

Claudia Hausmann's avatar
Claudia Hausmann
Copper Contributor
Sep 12, 2017

Need help with formula to match data and copy data over

Help! I tried to figure this out today at work with no avail.

I have two tabs in a spreadsheet, Sheet1 and Sheet2.

Each sheet has one column for Employee ID#. Sheet2 also has information in a column to the right of the Employee ID# that I want to include in Sheet1, any time the employee ID# matches. I have a blank column in Sheet1 for this data to be copied into.

Each sheet has about 25k lines of data.


Thank you!

excel

1 Reply

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Sep 13, 2017

    @Claudia

     

    Pulling info from a 25k row database with formulas is not ideal from a calc and perf. perspective. Power Query should be considered for this kind of task. That being said,

     

    Assuming Sheet2 info are in range A2:B25001 with the Empl ID in column A and the info you want to pull are in column B. In Sheet 1, 1st row of your empty column:

    =INDEX(Sheet2!A$2:B$25001, MATCH(A2,Sheet2!A$2:A$25001,0), 2)

     

    NB: A2 represents the 1st Employee ID to match in Sheet2. So adjust according to how your Sheet1 is setup