Index and match from two files
Posted by Teri Fields on August 31, 2001 12:42 PM
Hi,
I'd like to index a column in a file and have it find a match and transfer data from one file to the other.
(ex) File 1 - A1: Area, A2: 002, A3: 004, A4: 005, col B - B1: Area Desc., B2: #1 Scrubber, B3: Bridle Unit, B4: Conveyor. File 2 - A1: Area, A2: 004, A3: 002, A4:005, col B on file 2 is blank and titled Description. I'd like the formula or macro to index and match so that the data in File 1 col B is added to file 2 col. B. by matching the info in columns A of both files.
Posted by IML on August 31, 2001 1:03 PM
try
=VLOOKUP(A2,'C:\[file 1.xls]Sheet1'!$A$2:$B$4,2,FALSE)
where "file 1.xls" is saved to your C drive and the sheet your data is on is called Sheet1
It may be quicker to have both worksheets open when you do this and just highlight the source data in Vlookups second argument.
good luck
Posted by Russell Hauf on August 31, 2001 1:04 PM
Teri, you don't need a macro, just use VLOOKUP.
In B2 of File 2, just type:
=VLOOKUP($A2, [WorkbookName.xls]Sheet1!$A$1:$B$4, 2, False)
(then copy the formula down as necessary)
Where WorkbookName.xls is the name of the file where your data is, and File1 is the name of the sheet in WorkbookName.xls where your data is. Make sure that both workbooks are open when you do this so you don't have to type the path. $A$1:$B$4 is where the data in File1 is. You will probably go past row 4, so adjust this accordingly. If you decide that you want to keep this data permanently, you can copy the description column in File 2 and then Paste Special - Values, and the descriptions will now be permanent.
Hope this helps,
Russell
