# Linking variable values between 2 excel files

#### VoipDepot

##### New Member
Hi guys

I have a though question and my search hasn't paid anything off, so this is my last resort let's say.

I have 2 excel files.

Excel A : excel file from our supplier
Excel B : excel file containing our products we sell

The point is having values where our purchase price from our supplier is located in Excel A, linked to a certain fixed column/row in our Excel B.

This would give us a very clear overview since our supplier provides us with daily .csv files. However this .csv mentions thousands of products not relevant for us.

I managed to do this, but I used absolute formulas. Now products are being added into that excel A from our supplier, so my formule is wrong and shows other purchase prices since a product has been added and the location is wrong now.

An example : ='[voipdepot-supplier.xlsx]voipdepot-supplier'!\$J\$15928

Concrete :

How can I link the CORRECT purchase price to my Excel B eventhough the row changes?

In the above example the correct row now is J15936 since products are added.

Column will always remain the same, it's only for the row this is the problem..

Suggestion :

Every product has a UNIQUE code in its row.

I would think I can do the following in Excel B: search for that unique code through a formula in which the result is the row number, in this case 15936.

Then add another formula that tells my Excel B to search for that value in column J and row 15936.

I hope this is somewhat clear and someone can provide me with some useful information.

Thank you sooo very much for helping me out on this one, because this would save a lot of time!!

Hope to hear from you guys soon!

Friendly greetings

Frederik Maertens
VoipDepot

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### RoryA

##### MrExcel MVP, Moderator
Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!\$J:\$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!\$A:\$A,0))

#### VoipDepot

##### New Member
Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!\$J:\$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!\$A:\$A,0))
I'll try this it in a few moments, thank you for your very very swift reply Rorya!

Keep you updated in a few moments if it works or not.

#### VoipDepot

##### New Member
Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!\$J:\$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!\$A:\$A,0))

Hi

This is the formula with the correct values and references :

=INDEX('[voipdepot-allnet.xlsx]voipdepot-allnet'!\$J:\$J,match(65762,'[voipdepot-allnet.xlsx]voipdepot-allnet'!\$B:\$B,0))

However he gives me an error on this exact part :

\$J,match

This is highlighted.

Any idea?

Thanks again!

#### RoryA

##### MrExcel MVP, Moderator
I guess you use different separators in your formulas? Try this:

=INDEX('[voipdepot-allnet.xlsx]voipdepot-allnet'!\$J:\$J;match(65762;'[voipdepot-allnet.xlsx]voipdepot-allnet'!\$B:\$B;0))

#### VoipDepot

##### New Member
Assuming A2 in workbook B contains your unique product ID, and you are looking for that in column A on workbook A, then you could use this formula:
=INDEX('[voipdepot-supplier.xlsx]voipdepot-supplier'!\$J:\$J,match(A2,'[voipdepot-supplier.xlsx]voipdepot-supplier'!\$A:\$A,0))

I was experimenting with your formula. I should have mentioned i'm using Excel 2010.

And it works!!!! :D

The correct formula is however :

=INDEX('[voipdepot-allnet.xlsx]voipdepot-allnet'!\$J:\$J;MATCH(60608;'[voipdepot-allnet.xlsx]voipdepot-allnet'!\$B:\$B;0))

the commas need to be ;

You single handedly saved me soooooooooooooo much work, thanks a million!

If you are in need of Voip hardware, just drop by at www.voipdepot.be and I get some special pricing to you!

This thread can be closed now.

Thanks!

Replies
1
Views
267
Replies
1
Views
101
Replies
7
Views
104
Replies
1
Views
405
Replies
1
Views
62

1,141,863
Messages
5,709,063
Members
421,611
Latest member
Lisa W

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