# Data manipulation between 2 workbooks

#### ryalmokas

##### New Member
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Jonmo1

##### MrExcel MVP
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...

#### Jonmo1

##### MrExcel MVP
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))

#### ryalmokas

##### New Member
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.

#### Jonmo1

##### MrExcel MVP
and what column is the price located in in big?

#### ryalmokas

##### New Member
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.

#### Jonmo1

##### MrExcel MVP
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"

#### ryalmokas

##### New Member
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".

#### Jonmo1

##### MrExcel MVP
That's exactly what it does. Put the formula in little, J2

#### ryalmokas

##### New Member
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.

Replies
2
Views
335
Replies
3
Views
1K
Replies
5
Views
415
Replies
1
Views
226
Replies
1
Views
282

1,191,171
Messages
5,985,067
Members
439,938
Latest member
MAlhash

### 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.

### Which adblocker are you using?

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

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