Product Data

[Amanda1234]

I have a Master price file that has multiple columns starting with the product code. I would like another sheet to pick up certain columns when we type in just the code.

ie. Master Sheet - Product code, Supplier, Description, Buy Price, Conversion, Unit Price
New Sheet - Product code typed in brings up Supplier, Description, price all in separate cells.

I have tried vlookup formulas in each cell but it slows the sheet down alot when we copy/paste a handful of codes down column 1 at a time.

 

[Legacy 143009]

Paste this formula to new sheet B2:

=VLOOKUP($A2, 'Master Sheet'!$A:$F, 2)

C2:

=VLOOKUP($A2, 'Master Sheet'!$A:$F, 3)

D2:

=VLOOKUP($A2, 'Master Sheet'!$A:$F, 6)

Apart from the above, you may also try the formula below. This doesn't require separate formulas but header names must be identical.

= INDEX('Master Sheet'!$A:$F, MATCH($A2, 'Master Sheet'!$A:$A, 0), MATCH(B$1, 'Master Sheet'!$A$1:$F$1, 0))

 

[Peter_SSs]

Welcome to the MrExcel board!

Since you have Excel 365, you can bring all the wanted columns with a single formula - no need for one in each column.

You have not given exact details but see if something like this might help. I have assumed that a particular product code will only occur once in the master sheet.

Amanda1234.xlsm
	A	B	C	D	E	F
1	Product Code	Supplier	Description	Price	Conversion	Unit price
2	Code 1	Supplier 1	Desc 1	P1	Conv 1	UP 1
3	Code 2	Supplier 2	Desc 2	P2	Conv 2	UP 2
4	Code 3	Supplier 3	Desc 3	P3	Conv 3	UP 3
5	Code 4	Supplier 4	Desc 4	P4	Conv 4	UP 4
6	Code 5	Supplier 5	Desc 5	P5	Conv 5	UP 5
Master

Amanda1234.xlsm
	A	B	C	D
1	Code	Supplier	Description	Price
2	Code 4	Supplier 4	Desc 4	P4
3	Code 2	Supplier 2	Desc 2	P2
4
Sheet2

Cell Formulas
Range	Formula
B4,B2:D3	B2	=IF(A2="","",FILTER(Master!B$2:D$100,Master!A$2:A$100=A2,""))
Dynamic array formulas.

 

[Amanda1234]

Welcome to the MrExcel board!

Since you have Excel 365, you can bring all the wanted columns with a single formula - no need for one in each column.

You have not given exact details but see if something like this might help. I have assumed that a particular product code will only occur once in the master sheet.

Amanda1234.xlsm
	A	B	C	D	E	F
1	Product Code	Supplier	Description	Price	Conversion	Unit price
2	Code 1	Supplier 1	Desc 1	P1	Conv 1	UP 1
3	Code 2	Supplier 2	Desc 2	P2	Conv 2	UP 2
4	Code 3	Supplier 3	Desc 3	P3	Conv 3	UP 3
5	Code 4	Supplier 4	Desc 4	P4	Conv 4	UP 4
6	Code 5	Supplier 5	Desc 5	P5	Conv 5	UP 5
Master

Amanda1234.xlsm
	A	B	C	D
1	Code	Supplier	Description	Price
2	Code 4	Supplier 4	Desc 4	P4
3	Code 2	Supplier 2	Desc 2	P2
4
Sheet2

Cell Formulas
Range	Formula
B4,B2:D3	B2	=IF(A2="","",FILTER(Master!B$2:D$100,Master!A$2:A$100=A2,""))
Dynamic array formulas.

Hi Peter_SSs

Thats great thank you. It eliminates 3 vlookup cells and just leaves 1 column that needs a vlookup as it is separate to the others. Saved me alot of time, so very much appreciated.

 

[Peter_SSs]

Thats great thank you.

You're welcome.

just leaves 1 column that needs a vlookup as it is separate to the others.

Would you like to explain what that formula is and where it is (say in relation to the sample data/layout in post #3)? There may possibly be an alternative for that too.

 

[Amanda1234]

You're welcome.


Would you like to explain what that formula is and where it is (say in relation to the sample data/layout in post #3)? There may possibly be an alternative for that too.

HI Peter_SSs,

so the price would be in column F as apposed to D. D and E would be lank with no formula so users can add a manual QTY to order and multiply with Price in F.

 

[Peter_SSs]

Thanks for the clarification. Yes, if you are going to have a manual entry within the data row, a separate formula would be appropriate.