Hello, I am having troubles figuring out a way to search and copy data from one workbook into another. I have 2 spreadsheets that have 1 common column (ID #) and I want the one spreadsheet to the read the ID# from column A and search the second workbook for the same number in column R. Then copy the data in columns 6-8 in the same row as the ID# into the first work in the same row as the ID#... Hopefully this is clear. Can anybody help me with any ideas.

I have contemplated getting excel to copy the complete other workbook into the first one and then deleting them in the end, but I would prefer to avoid it if necessary.

THANKS!

If you are willing (and able) to duplicate Column R to the left of the data you want to extract, then VLOOKUP should work for you. e.g. If you duplicate Column R and place it as Column E, this should work to return what is in column F:

=VLOOKUP(A1,[YourOtherBook.xls]Sheet1!$E$1:$H$6,2,0)

If not, then you need to use the "left lookup" technique found here:

http://www.ozgrid.com/Excel/left-lo...

e.g.

=INDEX([YourOtherBook.xls]Sheet1!$F$1:$F$6,

MATCH(A1,[YourOtherBook.xls]Sheet1!$R$1:$R$6,0),1)

Ask Your Question

Weekly Poll