Excel Compare data from two workbooks

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