Hi there, I have a sales sheet that I use VLOOKUP for to return an item value for SKU which met my needs until now. The problem that I have is that the current VLOOKUP formula that I have at present will only work out the value of one item as it only references the SKU and performs the VLOOKUP to return a value for that SKU. I also need my formula to calculate the price for multiple items which I will show below.
[TABLE="width: 500"]
<tbody>[TR]
[TD]QTY SOLD[/TD]
[TD]SKU[/TD]
[TD]VALUE I WOULD LIKE RETURNED[/TD]
[TD][/TD]
[TD]VLOOKUP COLUMN: SKU[/TD]
[TD]VLOOKUP COLUMN: QTY SOLD = 1[/TD]
[TD]VLOOKUP COLUMN: QTY SOLD = EACH ADDITIONAL ITEM[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU-TEST1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]SKU-TEST1[/TD]
[TD]10[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SKU-TEST1[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SKU-TEST1[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The formula would basically lookup a table and if the QTY SOLD was 1 it would return the value of 1 item (10) but for each additional item sold it would add on the corresponding value which for 2 items would be (10+9), 3 items (10+9+9) 4 items (10+9+9+9) etc.
Is this possible?
[TABLE="width: 500"]
<tbody>[TR]
[TD]QTY SOLD[/TD]
[TD]SKU[/TD]
[TD]VALUE I WOULD LIKE RETURNED[/TD]
[TD][/TD]
[TD]VLOOKUP COLUMN: SKU[/TD]
[TD]VLOOKUP COLUMN: QTY SOLD = 1[/TD]
[TD]VLOOKUP COLUMN: QTY SOLD = EACH ADDITIONAL ITEM[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SKU-TEST1[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]SKU-TEST1[/TD]
[TD]10[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SKU-TEST1[/TD]
[TD]19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SKU-TEST1[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The formula would basically lookup a table and if the QTY SOLD was 1 it would return the value of 1 item (10) but for each additional item sold it would add on the corresponding value which for 2 items would be (10+9), 3 items (10+9+9) 4 items (10+9+9+9) etc.
Is this possible?