Combining Data from Multiple Workbooks

[Chris McGreevy]

I have two workbooks I can export from a system we use.  One gives me people's names and ID numbers, the second gives me the ID numbers and schedules those people are associated with.

I am trying to match the name and ID data from one workbook to the ID number and associated schedules in the other.  I was trying to do a VLookup which worked, but when I try to copy the formula down, it adjusts the formula, making it no longer work.  My spreadsheet has about 52000 rows so that is not going to work.

Any suggestions would be greatly appreciated!

[#2 · Accepted solution]

Hello,

sounds like you use only relative references in your Vlookup formula. Starting with a reference like

=A1

When copied down, it will change to A2 and when copied to the right, A1 will change to B1

You can anchor and lock the reference to row 1 by placing a $ sign in front of the row number. If you copy a formula with A$1 down, it will stay A$1 in the next row, and if you copy A$1 to the right, it will become B$1.

Likewise, you can anchor and lock the column letter with a $ sign in front of it. If you start with $A1 and copy down, it will become $A2 (the row is not locked with a $ sign), but if you copy $A1 to the right, it will still be $A1 because the column reference is absolute.

And, finally, $A$1 will stay like this, wherever you copy it. 

With Vlookup, you probably have something like

=Vlookup(A1,Sheet2!B1:F100,3,0)

And if you copy that down, it will change to 

=Vlookup(A2,Sheet2!B2:F101,3,0)

You probably WANT the reference for the lookup value to adjust to the next row, but you want the lookup range to remain the same, so you need to add $ signs to the references for the lookup table. 

=Vlookup(A1,Sheet2!$B$1:$F$100,3,0)

When you copy this formula down, A1 will change to A2, but the reference to the range on Sheet2 will remain exactly the same.

Let me know if that helps.

[#3]

That was it Ingeborg, thank you for the help!

[#4]

Thanks for the feedback. Glad you got it to work.