two lookups

[Johnboy28]

Hi,

Can someone help with a formula - If an amended price is present it will calculate product weight value with new price, if not it will use current pricing. Thanks for your help.

<tbody>
[TD="class: xl86, colspan: 2, align: center"]New Pricing[/TD]
[TD="class: xl88, width: 341, colspan: 3, align: center"]Current Pricing[/TD]
[TD="width: 125, align: center"][/TD]
[TD="width: 140, align: center"][/TD]

[TD="class: xl65, align: center"]Amended Code[/TD]
[TD="class: xl66, align: center"]Amended Price[/TD]
[TD="class: xl67, align: center"]Product Weight[/TD]
[TD="class: xl68, align: center"]Actual Code[/TD]
[TD="class: xl66, align: center"]Price/kg[/TD]
[TD="class: xl69, align: center"]Actual Value[/TD]
[TD="class: xl70, align: center"]Amended Value[/TD]

[TD="class: xl72, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl89, align: center"]150[/TD]
[TD="class: xl74, align: center"]16732[/TD]
[TD="class: xl73, align: center"]$1.38[/TD]
[TD="class: xl75, align: center"]$6,999.06[/TD]
[TD="class: xl76, align: center"]$206.32[/TD]

[TD="class: xl64, align: center"][/TD]
[TD="class: xl81, align: center"][/TD]
[TD="class: xl90, align: center"]250[/TD]
[TD="class: xl82, align: center"]16902[/TD]
[TD="class: xl81, align: center"]$7.59[/TD]
[TD="class: xl83, align: center"]$17,742.82[/TD]
[TD="class: xl84, align: center"]$1,896.36[/TD]

[TD="class: xl64, align: center"][/TD]
[TD="class: xl81, align: center"][/TD]
[TD="class: xl90, align: center"]350[/TD]
[TD="class: xl82, align: center"]16953[/TD]
[TD="class: xl81, align: center"]$2.89[/TD]
[TD="class: xl83, align: center"]$4,090.01[/TD]
[TD="class: xl84, align: center"]$1,009.91[/TD]

[TD="class: xl64, align: center"]15506[/TD]
[TD="class: xl81, align: center"]$7.89[/TD]
[TD="class: xl90, align: center"]650[/TD]
[TD="class: xl82, align: center"]16973[/TD]
[TD="class: xl81, align: center"]$3.20[/TD]
[TD="class: xl83, align: center"]$4,576.08[/TD]
[TD="class: xl85, align: center"]??[/TD]

[TD="class: xl64, align: center"]26354[/TD]
[TD="class: xl81, align: center"]$8.10[/TD]
[TD="class: xl90, align: center"]500[/TD]
[TD="class: xl82, align: center"]20104[/TD]
[TD="class: xl81, align: center"]$7.79[/TD]
[TD="class: xl83, align: center"]$16,115.59[/TD]
[TD="class: xl85, align: center"]??[/TD]

[TD="class: xl64, align: center"][/TD]
[TD="class: xl81, align: center"][/TD]
[TD="class: xl90, align: center"]450[/TD]
[TD="class: xl82, align: center"]20112[/TD]
[TD="class: xl81, align: center"]$4.00[/TD]
[TD="class: xl83, align: center"]$652.06[/TD]
[TD="class: xl85, align: center"]1797.9525[/TD]

[TD="class: xl64, align: center"][/TD]
[TD="class: xl81, align: center"][/TD]
[TD="class: xl90, align: center"]250[/TD]
[TD="class: xl82, align: center"]20147[/TD]
[TD="class: xl81, align: center"]$6.87[/TD]
[TD="class: xl83, align: center"]$18,813.97[/TD]
[TD="class: xl85, align: center"]1716.2525[/TD]

[TD="class: xl63, align: center"]15506[/TD]
[TD="class: xl77, align: center"]$7.89[/TD]
[TD="class: xl91, align: center"]360[/TD]
[TD="class: xl78, align: center"]20409[/TD]
[TD="class: xl77, align: center"]$6.64[/TD]
[TD="class: xl79, align: center"]$289.68[/TD]
[TD="class: xl80, align: center"]??[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="class: xl71, align: center"]$69,279.28[/TD]
[TD="class: xl71, align: center"]$6,626.79[/TD]

</tbody>

 

[Cross Terrier]

Don't you need a volume in there somewhere?

 

[FormR]

If an amended price is present it will calculate product weight value with new price, if not it will use current pricing

Hi, like this maybe:

Excel 2013/2016
	A	B	C	D	E	F	G
2	Amended Code	Amended Price	Product Weight	Actual Code	Price/kg	Actual Value	Amended Value
3			150	16732	1.38	6,999.06	207
4			250	16902	7.59	17,742.82	1897.5
5			350	16953	2.89	4,090.01	1011.5
6	15506	7.89	650	16973	3.2	4,576.08	5128.5
7	26354	8.1	500	20104	7.79	16,115.59	4050
8			450	20112	4	652.06	1800
9			250	20147	6.87	18,813.97	1717.5
10	15506	7.89	360	20409	6.64	289.68	2840.4
Sheet1

Cell Formulas
Range	Formula
G3	=C3*IF(ISNUMBER(B3),B3,E3)

 

[wideboydixon]

Just this?

Book1
	A	B	C	D	E	F	G
1	New Pricing		Current Pricing
2	Amended Code	Amended Price	Product Weight	Actual Code	Price/kg	Actual Value	Amended Value
3			150	16732	£1.38	£6,999.06	£206.32
4			250	16902	£7.59	£17,742.82	£1,896.36
5			350	16953	£2.89	£4,090.01	£1,009.91
6	15506	£7.89	650	16973	£3.20	£4,576.08	£5,128.50
7	26354	£8.10	500	20104	£7.79	£16,115.59	£4,050.00
8			450	20112	£4.00	£652.06	£1,797.95
9			250	20147	£6.87	£18,813.97	£1,716.25
10	15506	£7.89	360	20409	£6.64	£289.68	£2,840.40
11						£69,279.27	£18,645.70
Sheet1

Cell Formulas
Range	Formula
G3	=IF($B3="",$C3*$E3,$C3*$B3)

WBD

 

[Johnboy28]

Hi, like this maybe:

Excel 2013/2016

	A	B	C	D	E	F	G

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]Amended Code[/TD]
[TD="align: center"]Amended Price[/TD]
[TD="align: center"]Product Weight[/TD]
[TD="align: center"]Actual Code[/TD]
[TD="align: center"]Price/kg[/TD]
[TD="align: center"]Actual Value[/TD]
[TD="align: center"]Amended Value[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]16732[/TD]
[TD="align: center"]1.38[/TD]
[TD="align: center"]6,999.06[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]207[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]16902[/TD]
[TD="align: center"]7.59[/TD]
[TD="align: center"]17,742.82[/TD]
[TD="align: right"]1897.5[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]350[/TD]
[TD="align: center"]16953[/TD]
[TD="align: center"]2.89[/TD]
[TD="align: center"]4,090.01[/TD]
[TD="align: right"]1011.5[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]15506[/TD]
[TD="align: center"]7.89[/TD]
[TD="align: center"]650[/TD]
[TD="align: center"]16973[/TD]
[TD="align: center"]3.2[/TD]
[TD="align: center"]4,576.08[/TD]
[TD="align: right"]5128.5[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]26354[/TD]
[TD="align: center"]8.1[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]20104[/TD]
[TD="align: center"]7.79[/TD]
[TD="align: center"]16,115.59[/TD]
[TD="align: right"]4050[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]450[/TD]
[TD="align: center"]20112[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]652.06[/TD]
[TD="align: right"]1800[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]20147[/TD]
[TD="align: center"]6.87[/TD]
[TD="align: center"]18,813.97[/TD]
[TD="align: right"]1717.5[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]15506[/TD]
[TD="align: center"]7.89[/TD]
[TD="align: center"]360[/TD]
[TD="align: center"]20409[/TD]
[TD="align: center"]6.64[/TD]
[TD="align: center"]289.68[/TD]
[TD="align: right"]2840.4[/TD]

</tbody>

Sheet1​

[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: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=C3*IF(ISNUMBER(B3),B3,E3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your help. Works well.

 

[Johnboy28]

Thanks for your reply. I ended up using this formula. =C3*IF(ISNUMBER(B3),B3,E3)