Merging With Same Cell Value and Matching Other Column Value

[shah0101]

Hello Experts,

Following is actual data and the common column in both sheets is "Style No.".

The two sheets have thousands of records. Lets say first sheet is with prices and order quantity and the second sheet is with actual shipped quantity.




TABLE 1 WITH PRICES:
[TABLE="width: 922"]
<colgroup><col><col><col><col><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]Style No. [/TD]
[TD]Suppl. Style[/TD]
[TD]brand[/TD]
[TD]Color[/TD]
[TD]Sex[/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]XL[/TD]
[TD]Quantity/Pcs[/TD]
[TD]Unit price[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]14406146[/TD]
[TD]MR-1[/TD]
[TD]Mo[/TD]
[TD]light navy[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.50[/TD]
[TD] $ 976.67[/TD]
[/TR]
[TR]
[TD]14406146[/TD]
[TD]MR-1[/TD]
[TD]Mo[/TD]
[TD]black[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.50[/TD]
[TD] $ 976.67[/TD]
[/TR]
[TR]
[TD]14406146[/TD]
[TD]MR-1[/TD]
[TD]Mo[/TD]
[TD]dark beige[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.50[/TD]
[TD] $ 976.67[/TD]
[/TR]
[TR]
[TD]14406146[/TD]
[TD]MR-1[/TD]
[TD]Mo[/TD]
[TD]light olive[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.50[/TD]
[TD] $ 976.67[/TD]
[/TR]
[TR]
[TD]14406147[/TD]
[TD]16203[/TD]
[TD]Mo[/TD]
[TD]light navy[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 3.50[/TD]
[TD] $ 1,139.28[/TD]
[/TR]
[TR]
[TD]14406147[/TD]
[TD]16203[/TD]
[TD]Mo[/TD]
[TD]black[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 3.50[/TD]
[TD] $ 1,139.28[/TD]
[/TR]
[TR]
[TD]14406147[/TD]
[TD]16203[/TD]
[TD]Mo[/TD]
[TD]dark beige[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 3.50[/TD]
[TD] $ 1,139.28[/TD]
[/TR]
[TR]
[TD]14406147[/TD]
[TD]16203[/TD]
[TD]Mo[/TD]
[TD]light olive[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 3.50[/TD]
[TD] $ 1,139.28[/TD]
[/TR]
[TR]
[TD]14406148[/TD]
[TD]3015[/TD]
[TD]Mo[/TD]
[TD]navy[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 1.50[/TD]
[TD] $ 1,374.61[/TD]
[/TR]
[TR]
[TD]14406148[/TD]
[TD]3015[/TD]
[TD]Mo[/TD]
[TD]black[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 1.50[/TD]
[TD] $ 1,374.61[/TD]
[/TR]
[TR]
[TD]14406149[/TD]
[TD]17323[/TD]
[TD]Mo[/TD]
[TD]navy[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.30[/TD]
[TD] $ 1,049.39[/TD]
[/TR]
[TR]
[TD]14406149[/TD]
[TD]17323[/TD]
[TD]Mo[/TD]
[TD]black[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.30[/TD]
[TD] $ 1,049.39[/TD]
[/TR]
[TR]
[TD]14406150[/TD]
[TD]19618[/TD]
[TD]Mo[/TD]
[TD]black[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.21[/TD]
[TD] $ 1,031.21[/TD]
[/TR]
[TR]
[TD]14406150[/TD]
[TD]19618[/TD]
[TD]Mo[/TD]
[TD]navy[/TD]
[TD]men[/TD]
[TD]29[/TD]
[TD]43[/TD]
[TD]29[/TD]
[TD]101[/TD]
[TD] $ 2.21[/TD]
[TD] $ 1,031.21[/TD]
[/TR]
</tbody>[/TABLE]






TABLE 2 WITH SHIPPED QUANTITIES:

[TABLE="width: 1213"]
<colgroup><col><col><col><col><col><col><col span="3"><col><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Carton [/TD]
[TD]Total [/TD]
[TD]Style No. [/TD]
[TD]Customs-No. [/TD]
[TD]Colour [/TD]
[TD]Lot [/TD]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Pcs/Ctn [/TD]
[TD]Total Pcs [/TD]
[TD]G.W. [/TD]
[TD]N.W.[/TD]
[TD]Carton Size[/TD]
[TD]Column4[/TD]
[TD]Column5[/TD]
[TD]CBM[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]M[/TD]
[TD]L[/TD]
[TD]XL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Length[/TD]
[TD]Width[/TD]
[TD]Height[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]14406146[/TD]
[TD] [/TD]
[TD]light navy[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]13[/TD]
[TD]12.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]14406146[/TD]
[TD] [/TD]
[TD]black[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]13[/TD]
[TD]12.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1[/TD]
[TD]14406146[/TD]
[TD] [/TD]
[TD]dark beige[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]13[/TD]
[TD]12.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]14406146[/TD]
[TD] [/TD]
[TD]light olive[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]13[/TD]
[TD]12.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]14406147[/TD]
[TD] [/TD]
[TD]light navy[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]14.4[/TD]
[TD]13.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]14406147[/TD]
[TD] [/TD]
[TD]black[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]14.4[/TD]
[TD]13.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]1[/TD]
[TD]14406147[/TD]
[TD] [/TD]
[TD]dark beige[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]14.4[/TD]
[TD]13.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]1[/TD]
[TD]14406147[/TD]
[TD] [/TD]
[TD]light olive[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]14.4[/TD]
[TD]13.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]32[/TD]
[TD]0.0768[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]14406148[/TD]
[TD] [/TD]
[TD]navy[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]19[/TD]
[TD]18.1[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]14406148[/TD]
[TD] [/TD]
[TD]black[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]19[/TD]
[TD]18.1[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]0.12[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]14406149[/TD]
[TD] [/TD]
[TD]navy[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]10.9[/TD]
[TD]10.1[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]0.072[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]14406149[/TD]
[TD] [/TD]
[TD]black[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]10.9[/TD]
[TD]10.1[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]0.072[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]14406150[/TD]
[TD] [/TD]
[TD]black[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]11[/TD]
[TD]10.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]0.072[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]14406150[/TD]
[TD] [/TD]
[TD]navy[/TD]
[TD]M/L/XL[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]11[/TD]
[TD]10.2[/TD]
[TD]60[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]0.072








[/TD]
[/TR]
</tbody>[/TABLE]






Now what I want to achieve is:
1) merge the style no. to a single row of same kind on second sheet (removing colour/lot columns)
2) then relate/attach/bring the prices from the first sheet based on "Style No." right on the same row in front of it to calculate the value of actual shipped goods.

I dont know if it can be acieved with PivotTable() or Vlookup() or Match(). Can you please guide / advise.

I hope I am making sense.

Thanks,

 

[Toadstool]

Hi shah0101,

I'd normally build a third table, starting with the unique distinct list of Style Nos. then do the calculations. If you want it in Table 2 then I've inserted a column but the calculation only show for the last entry of a duplicated Style No.

I don't see a Shipping Qty so I'm assuming the Carton number is the quantity shipped.

This calculation assume that TABLE 1 prices are the same for duplicate Style Nos.

The formula in C23 needs to be copied down all rows in TABLE 2.

	A	B	C	D	E	F	G	H	I	J	K
TABLE 1 WITH PRICES:
Style No.	Suppl. Style	brand	Color	Sex	M	L	XL	Quantity/Pcs	Unit price	Amount
MR-1	Mo	light navy	men
MR-1	Mo	black	men
MR-1	Mo	dark beige	men
MR-1	Mo	light olive	men
Mo	light navy	men
Mo	black	men
Mo	dark beige	men
Mo	light olive	men
Mo	navy	men
Mo	black	men
Mo	navy	men
Mo	black	men
Mo	black	men
Mo	navy	men
TABLE 2 WITH SHIPPED QUANTITIES:
Carton	Total	Value Shipped	Style No.	Customs-No.	Colour	Lot	Column1	Column2	Column3	Pcs/Ctn
M	L	XL
	light navy	M/L/XL
	black	M/L/XL
	dark beige	M/L/XL
light olive	M/L/XL
	light navy	M/L/XL
	black	M/L/XL
	dark beige	M/L/XL
light olive	M/L/XL
	navy	M/L/XL
black	M/L/XL
	navy	M/L/XL
black	M/L/XL
	black	M/L/XL
navy	M/L/XL

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]14406146[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$976.67[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]14406146[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$976.67[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]14406146[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$976.67[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]14406146[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$976.67[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]16203[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$3.50[/TD]
[TD="align: right"]$1,139.28[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]16203[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$3.50[/TD]
[TD="align: right"]$1,139.28[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]16203[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$3.50[/TD]
[TD="align: right"]$1,139.28[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]16203[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$3.50[/TD]
[TD="align: right"]$1,139.28[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]3015[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$1.50[/TD]
[TD="align: right"]$1,374.61[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]3015[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$1.50[/TD]
[TD="align: right"]$1,374.61[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]14406149[/TD]
[TD="align: right"]17323[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.30[/TD]
[TD="align: right"]$1,049.39[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]14406149[/TD]
[TD="align: right"]17323[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.30[/TD]
[TD="align: right"]$1,049.39[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]14406150[/TD]
[TD="align: right"]19618[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.21[/TD]
[TD="align: right"]$1,031.21[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]14406150[/TD]
[TD="align: right"]19618[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.21[/TD]
[TD="align: right"]$1,031.21[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406146[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406146[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406146[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$70.00[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406147[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406147[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406147[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$98.00[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406148[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$9.00[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406149[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$13.80[/TD]
[TD="align: right"]14406149[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]14406150[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$13.26[/TD]
[TD="align: right"]14406150[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]

</tbody>

Sheet2 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C23[/TH]
[TD="align: left"]=IF(COUNTIF($D23:$D$36,D23)=1,INDEX($J$3:$J$16,MATCH(D23,$A$3:$A$16,0))*SUMIFS($A$23:$A$36,$D$23:$D$36,D23),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shah0101]

Dear ToadStool,

Thank you very much for your help.

As you said: "I'd normally build a third table, starting with the unique distinct list of Style Nos. then do the calculations". I think this would be the right approach so I made a new sheet with Pivot and now all the "Style No." from table 2 are unique and have the relevant quantities (screenshot is attached, which is such a relief, thanks to you).

Now is there any possibility that we can bring / fetch / link the relevant price, brand, etc of similar "Style No." from table 1 to the the pivot table or otherwise?

Thanks in advance.

-------
P.S. for some reason I am unable to link the scrrenshot so here is the link: https://drive.google.com/file/d/1el52YYwZZHr3274RxpBjnxs6w59Ht61K/view?usp=sharing

 

[Toadstool]

It's too late here for me to build pivot tables so I'll just say that if you've the total then the INDEX MATCH will retrieve prices:

[COLOR=Blue]=INDEX([COLOR=Red]$J$3:$J$16,MATCH([COLOR=Green]D23,$A$3:$A$16,0[/COLOR])[/COLOR])[/COLOR]

 

[shah0101]

It's too late here for me to build pivot tables so I'll just say that if you've the total then the INDEX MATCH will retrieve prices:

[COLOR=Blue]=INDEX([COLOR=Red]$J$3:$J$16,MATCH([COLOR=Green]D23,$A$3:$A$16,0[/COLOR])[/COLOR])[/COLOR]

===================

I created the pivot table already with unique values based on "Style No.".

The prices are in Sheet "Contract" Range L14:L200 - The range for "Style No." in this sheet is: A14:A200.

The "Pivot" Sheet brought the records which are now shrinked to 56 records which is okay. The "Style No." column range is: A3:A58 -

Can you please help me write the index code to bring the relevant prices in "Pivot" sheet in column range J3:J58 from the "Contract" sheet for matching "Style No.".

Much appreciated!

Thanks in advance.

 

[Toadstool]

Add a new column to TABLE 2 to get the unit price.

	A	B	C	D
Carton	Total	Style No.	Price from TABLE 1

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]$1.50[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]$1.50[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406149[/TD]
[TD="align: right"]$2.30[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406149[/TD]
[TD="align: right"]$2.30[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406150[/TD]
[TD="align: right"]$2.21[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406150[/TD]
[TD="align: right"]$2.21[/TD]

</tbody>

Sheet2 (3)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D23
[/TH]
[TD="align: left"]=INDEX($J$3:$J$16,MATCH(C23,$A$3:$A$16,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shah0101]

Add a new column to TABLE 2 to get the unit price.

	A	B	C	D
Carton	Total	Style No.	Price from TABLE 1

<tbody>
[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]$2.50[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]$3.50[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]$1.50[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]$1.50[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406149[/TD]
[TD="align: right"]$2.30[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406149[/TD]
[TD="align: right"]$2.30[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406150[/TD]
[TD="align: right"]$2.21[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]14406150[/TD]
[TD="align: right"]$2.21[/TD]

</tbody>

Sheet2 (3)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D23[/TH]
[TD="align: left"]=INDEX($J$3:$J$16,MATCH(C23,$A$3:$A$16,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Dear Toadstool,

Thank you soooooooooooooooooooo very much.

 

[Toadstool]

You're welcome!

 

[shah0101]

You're welcome!

Hi Toadstool,

Need some more help.

The scenario is still the same but now the prices are in three different worksheets. Can we now have a same index code which we can write for different ranges?

TIA.

 

[Toadstool]

You can't have a single INDEX and/or MATCH using multiple ranges so you'll have to search each in turn.

I've put tables 1,3 and 4 on one sheet just to demonstrate (so you'll need to add sheet names to the arrays) and I'm showing Style Nos and Price in different columns just for fun.

The IFERROR searching tables 1 and 2 will fall into the next search if no match is found, otherwise it returns the first Pice. If the Style number in table 2 isn't on tables 1, 3 or 4 then you'll get a #N/A error.

	A	B	C	D	E	F	G	H	I	J	K	L
TABLE 1 WITH PRICES:
Style No.	Suppl. Style	brand	Color	Sex	M	L	XL	Quantity/Pcs	Unit price	Amount
MR-1	Mo	light navy	men
MR-1	Mo	black	men
TABLE 3 WITH PRICES:
	Style No.	Suppl. Style	brand	Color	Sex	Quantity/Pcs	Unit price	Amount
	Mo	light navy	men
	Mo	black	men
	Mo	dark beige	men
TABLE 4 WITH PRICES:
BD	LD	Style No.	Suppl. Style	brand	Color	Sex	M	L	Quantity/Pcs	Unit price	Amount
Mo	navy	men
Mo	black	men
TABLE 2 WITH SHIPPED QUANTITIES:
Carton	Total	Value Shipped	Style No.	Extracted Price

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]14406146[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$2.50 [/TD]
[TD="align: right"]$976.67 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2222222[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$4.22 [/TD]
[TD="align: right"]$976.67 [/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]14406147[/TD]
[TD="align: right"]16203[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]$1.55 [/TD]
[TD="align: right"]$1,139.28 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]14406147[/TD]
[TD="align: right"]16203[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]$1.55 [/TD]
[TD="align: right"]$1,139.28 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]14406147[/TD]
[TD="align: right"]16203[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]$1.55 [/TD]
[TD="align: right"]$1,139.28 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]3015[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$3.33 [/TD]
[TD="align: right"]$1,374.61 [/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]3015[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]$3.33 [/TD]
[TD="align: right"]$1,374.61 [/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14406146[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2222222[/TD]
[TD="align: right"]4.22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14406147[/TD]
[TD="align: right"]1.55[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14406148[/TD]
[TD="align: right"]3.33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3333333[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1 (2)​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E21
[/TH]
[TD="align: left"]=IFERROR(INDEX($J$3:$J$4,MATCH(D21,$A$3:$A$4,0)),IFERROR(INDEX($H$8:$H$10,MATCH(D21,$B$8:$B$10,0)),INDEX($K$14:$K$15,MATCH(D21,$C$14:$C$15,0))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]