I have two spread sheets, one with certain data (A) and codes it matches (B).
In another spread sheet I have the same data (B) with information for that data (C).
What I want to achieve is for the data (C) to be copied across to align with (A), the link being (B).
Any excel experts on the forum that could help?
You need to use the VLOOKUP function. This will lookup data in another sheet/workbook, and return the data you require based on a link.
For example in spreadsheet 1 you have the following:
A B
001 BBB
002 MM
003 TAF
004 GNR
005 TAF
and in spreadsheet 2 you have the following:
A B (column B, but the data you're calling "C" in your example)
GNR Gun's n' Roses
MM Medieval Madness
BBB Big Bang Bar
TAF The Addams Family
To copy the data (C) into spreadsheet 1 to align with A, linking on B you need to add a formula to spreadsheet 1.
=VLOOKUP($B1,'C:\excel\[spreadsheet2.xls]Sheet1'!$A$1:$B$4,2,0)
$B1 in this case is BBB, GNR etc. It is searching the table array specified (the range A1 - B4 of Sheet1 in the file c:\excel\spreadsheet2.xls) and returning the data from column 2 in this array, which is "Big Bang Bar", Guns n' Roses etc.
If the data is all in same excel file but on different 'sheets', you could just use =VLOOKUP($B1,Sheet2!$A$1:$B$4,2,0)
By copying this formula into a column on spreadsheet 1, you will get the following:
A B C
001 BBB Big Bang Bar
002 MM Medieval Madness
003 TAF The Addams Family
004 GNR Guns n' Roses
005 TAF The Addams Family
This allows for duplicates and does not require the data in spreadsheet 2 to be in the same order as the data in spreadsheet 1.
Hopefully I understood the question correctly!
