Forum Discussion
Excel Compare data from two workbooks
Hi Team, I have two workbooks, one has a column with a list of numbers which I need to link to the account number in a column in another workbook. This second workbook has a column with the account number and a column with the matching number in the first workbook. I want to be able to transfer the account number to the first work book that matches the number in column b on the same row of the 2nd workbook.
It sounds complicated and long winded but I hope someone can help me with this. Not sure if it will need vlookup and how to do that or if there is a better way.
Wendy Clark
Wendy,
I recommend you try the Excel Community instead--you're much more likely to get an answer there than in the community discussion space.
- EricStarker
Community Manager
Thanks Matt - I have moved this conversation to the Excel community.
Hi Wendy,
Yes, this is the sort of thing that people would use a VLOOKUP type solution for. VLOOKUP isn't the ideal function though, especially if the matching column in the second workbook is to the right of the account number.
You can do this with INDEX and MATCH - you use the MATCH function to work out what row the account is in the second workbook:
"=MATCH(<lookup number in workbook 1>, <lookup column in workbook 2>, 0)"
(assuming you want an exact match - for an approximate match, if the data is ascending, use '1' at the end instead of '0')
and you use INDEX to find the result:
"=INDEX(<account number column>, MATCH(...))"
(where the MATCH() bit there is the MATCH function described above)
This will a) find what row the matching number is in, and then b) use INDEX to pull the account number over. It's very similar to what VLOOKUP does, but it's more flexible and doesn't rely on columns being in order from left to right.
Hope this helps!
Tim
I should also note - if you're taking data from two different workbooks, you might want to consider using Get and Transform (or Power Query) to do it instead, if it's a task that you'll want done on a frequent / regular basis.
