Advanced Lookup Function

[cainstratton]

I need a way to move data from dataset 1 to dataset 2 using a formula in dataset 2 that references dataset 1 via label (quantity, price, shipping cost per unit) and item code. Does anyone have any suggestions?

https://imgur.com/a/CpVRn

 

[Scott T]

Excel 2010
	A	B	C	D	E	F
1	item code	1	2	3	4	5
2	Quantity	13123	3201	3234	5676	9908
3	Price	1.5	1.2	2.3	2.1	2
4	Shipping	3.33	3.33	3.33	3.33	3.33
Sheet1

Excel 2010
	A	B	C	D
1	item code	Quantity	Price	Shipping
2	1	13123	1.5	3.33
3	2	3201	1.2	3.33
4	3	3234	2.3	3.33
5	4	5676	2.1	3.33
6	5	9908	2	3.33
Sheet2

Cell Formulas
Range	Formula
B2	=INDEX(Sheet1!$B$2:$F$4,MATCH(B$1,Sheet1!$A$2:$A$4,0),MATCH($A2,Sheet1!$B$1:$F$1,0))
B3	=INDEX(Sheet1!$B$2:$F$4,MATCH(B$1,Sheet1!$A$2:$A$4,0),MATCH($A3,Sheet1!$B$1:$F$1,0))
B4	=INDEX(Sheet1!$B$2:$F$4,MATCH(B$1,Sheet1!$A$2:$A$4,0),MATCH($A4,Sheet1!$B$1:$F$1,0))
B5	=INDEX(Sheet1!$B$2:$F$4,MATCH(B$1,Sheet1!$A$2:$A$4,0),MATCH($A5,Sheet1!$B$1:$F$1,0))
B6	=INDEX(Sheet1!$B$2:$F$4,MATCH(B$1,Sheet1!$A$2:$A$4,0),MATCH($A6,Sheet1!$B$1:$F$1,0))
C2	=INDEX(Sheet1!$B$2:$F$4,MATCH(C$1,Sheet1!$A$2:$A$4,0),MATCH($A2,Sheet1!$B$1:$F$1,0))
C3	=INDEX(Sheet1!$B$2:$F$4,MATCH(C$1,Sheet1!$A$2:$A$4,0),MATCH($A3,Sheet1!$B$1:$F$1,0))
C4	=INDEX(Sheet1!$B$2:$F$4,MATCH(C$1,Sheet1!$A$2:$A$4,0),MATCH($A4,Sheet1!$B$1:$F$1,0))
C5	=INDEX(Sheet1!$B$2:$F$4,MATCH(C$1,Sheet1!$A$2:$A$4,0),MATCH($A5,Sheet1!$B$1:$F$1,0))
C6	=INDEX(Sheet1!$B$2:$F$4,MATCH(C$1,Sheet1!$A$2:$A$4,0),MATCH($A6,Sheet1!$B$1:$F$1,0))
D2	=INDEX(Sheet1!$B$2:$F$4,MATCH(D$1,Sheet1!$A$2:$A$4,0),MATCH($A2,Sheet1!$B$1:$F$1,0))
D3	=INDEX(Sheet1!$B$2:$F$4,MATCH(D$1,Sheet1!$A$2:$A$4,0),MATCH($A3,Sheet1!$B$1:$F$1,0))
D4	=INDEX(Sheet1!$B$2:$F$4,MATCH(D$1,Sheet1!$A$2:$A$4,0),MATCH($A4,Sheet1!$B$1:$F$1,0))
D5	=INDEX(Sheet1!$B$2:$F$4,MATCH(D$1,Sheet1!$A$2:$A$4,0),MATCH($A5,Sheet1!$B$1:$F$1,0))
D6	=INDEX(Sheet1!$B$2:$F$4,MATCH(D$1,Sheet1!$A$2:$A$4,0),MATCH($A6,Sheet1!$B$1:$F$1,0))