Reference a column in another workbook

[jpulse]

I am trying to reference a column in another workbook. I have two separate workbooks, MasterList and InStock, the MasterList never changes, but the InStock workbook can change depending on what I have in stock at the time. I want workbook (InStock) to fill in the serial number of its matching part number if it is found in the MasterList workbook. Is there a formula that can do this? Or a Macro that can do this? Thank you for your help
Workbook 1 (MasterList.xlsx)

	A	B	C
1	Part Number	Serial Number	Description
2	50376	AE456	Desktop
3	50421	AE342	Desktop
4	50779	12GX5	Laptop
5	50799	13GD9	Laptop
6	50814	G145FDX	Tablet
7	50836	G129WQE	Tablet
8	63147	A0983456	Cellphone
9	64318	A0123879	Cellphone
10	64347	98UYT654	Scanner

<tbody>
</tbody>

Workbook 2 (InStock.xlsx)

	A	B	C
1	Part Number	Serial Number	Description
2	50376		Desktop
4	50779		Laptop
7	50836		Tablet
9	64318		Cellphone
10	64347		Scanner

<tbody>
</tbody>

 

[MrKowz]

Welcome to the forums!

Try using INDEX/MATCH:

Excel 2013/2016
	A	B	C
1	Part Number	Serial Number	Description
2	50376	AE456	Desktop
3	50421	AE342	Desktop
4	50779	12GX5	Laptop
5	50799	13GD9	Laptop
6	50814	G145FDX	Tablet
7	50836	G129WQE	Tablet
8	63147	A0983456	Cellphone
9	64318	A0123879	Cellphone
10	64347	98UYT654	Scanner
Sheet1

Excel 2013/2016
	A	B	C
1	Part Number	Serial Number	Description
2	50376	AE456	Desktop
3	50779	12GX5	Laptop
4	50836	G129WQE	Tablet
5	64318	A0123879	Cellphone
6	64347	98UYT654	Scanner
Sheet2

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)),"")

 

[jpulse]

I tried that formula, but when you drag it down the whole column it lists all serial numbers in order from the MasterList, look like it went in order from the masterlist, not skipped to the correct one.
50376 - AE456 - Desktop (correct)
50779 - AE342 - Laptop (wrong serial number)
50836 - 12GX5 - Tablet (wrong serial number)
ext.....

 

[MrKowz]

You may have your formula references off. Can you please paste the exact formula you're using? The one I provided works on my end and is a standard INDEX/MATCH formula, so it shouldn't be returning results like you have described.

 

[jpulse]

=IFERROR(INDEX(MasterList!B:B,MATCH(A2,InStock!A:A,0)),"")

<tbody>
</tbody>

 

[jpulse]

When I drag down B3 changes the formula to A3, then B4 changes to A4.......

 

[MrKowz]

Try:

=IFERROR(INDEX(MasterList!B:B,MATCH(A2,MasterList!A:A,0)),"")

What is happening is that you are pointing the lookup_array in the MATCH formula to the wrong sheet.

 

[jpulse]

You sir, are awesome! Thank you very much

 

[MrKowz]

You sir, are awesome! Thank you very much

Happy to help - thanks for the feedback. Have a great weekend!

 

[jpulse]

How can I make the formula reference another workbook, not sheet, in the same folder?