Data manipulation between 2 workbooks

ryalmokas

New Member
Joined
Jun 8, 2003
Messages
31
I have a "master" worksheet with 11 columns of data, including a column of symbols in col. A (up to 100 symbols).

I have another workbook, whose col. A contains my "master" symbols and many more (up to 500 symbols).

I want to use the symbols from "master" 1 at a time, find the matching symbol in the bigger workbook, take the value in col. J for the matching symbol, and put that value col. K of "master".

Also, in doing this, do both workbooks have to be open, or can the bigger one be closed?

Thanks for any help with the above.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Vlookup should work for that..and the other book can be closed

=VLOOKUP(A2,'C:\Path\[BookName.xls]SheetName'!$A:$J,10,FALSE)

Hope This Helps...
 
Upvote 0
And though this may seem a longer more complicated formula, it should be more efficient.

Code:
=INDEX('C:\Path\[BookName.xls]SheetName'!$J:$J,MATCH(A2,'C:\Path\[BookName.xls]SheetName'!$A:$A,0))
 
Upvote 0
Thank you for the helpful suggestions. However, I guess I wasn't clear enough:
The "master" worksheet I want to return the values to has a fixed order of symbols in col. A (so that the value in A2 will always be the same).

However, the bigger worksheet has up to 500 symbols in col. A, but they can be in any order.

For simplicity, call the master worksheet "small", and the other one "big".

A2 in "small" will always be "bread" for example. I want to go into "big", locate the "price" for "bread", and return that value back to "small". So, "bread" can be anywhere in col. A in "big".

Hopefully, this makes it clearer what I'm trying to do.

Thanks again for the help.
 
Upvote 0
It's in col. J in "Big", and I'd like to return the value to col J. in the other one.

Thanks again for all your help.
 
Upvote 0
So did you try my suggested formulas? You'll have to adjust the Path BookName and SheetName according to the book and sheet that holds the data in "big"
 
Upvote 0
I did - the one with the index and the match. I didn't use the vlookup, because I thought I was to only use one or the other???

Using the one with index/match, what happens is it starts at A2 in "little" and goes to A2 in "big". It then send the value over from "big."

I need to start at A2 in "little" and find the row in col. A in "big" that matches; and then send the value in col. J of that row back to col. J in "little".
 
Upvote 0
That's what I did. Here's what I put in J2 in "little":
=INDEX('C:\Documents and Settings\****\Desktop\Inv_Intell.xls\[Sheet1]Sheet1'!$J:$J,MATCH(#REF!,'C:\Documents and Settings\****\Desktop\My_Turtle.xls\[Sheet1]Sheet1'!$A:$A,0))

"Big" is the 1st one; "little' is the last.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top