Would this work for you?
C2:Code:=IF(A2=1,VLOOKUP(B2,$E$2:$F$2,2,0),VLOOKUP(B2,$E$2:$F$2,2)+((A2-1)*VLOOKUP(B2,$E$2:$G$2,3,0)))
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.
QTY SOLD SKU VALUE I WOULD LIKE RETURNED VLOOKUP COLUMN: SKU VLOOKUP COLUMN: QTY SOLD = 1 VLOOKUP COLUMN: QTY SOLD = EACH ADDITIONAL ITEM 1 SKU-TEST1 10 SKU-TEST1 10 9 2 SKU-TEST1 19 3 SKU-TEST1 28
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?
Would this work for you?
C2:Code:=IF(A2=1,VLOOKUP(B2,$E$2:$F$2,2,0),VLOOKUP(B2,$E$2:$F$2,2)+((A2-1)*VLOOKUP(B2,$E$2:$G$2,3,0)))
Holy smokes dude, that is seriously awesome! I have been scratching my head about this for absolutely ages! Am I right in the following:
=IF(A2=1,VLOOKUP(B2,$E$2:$F$2,2,0) is how it works out the price of 1 item.
, means that if the above cell A2 is greater than 1 then perform the next calculation below
VLOOKUP(B2,$E$2:$F$2,2)+((A2-1)*VLOOKUP(B2,$E$2:$G$2,3,0))) 1st vlookup returns value 10 then 2nd vlookup deducts 1 from A2 then multiplies it by the additional item price
I cannot thank you enough for this, awesome contribution!
Hey Orbus,
Yes, that's exactly how it works. You're welcome, glad it helped, see ya around.
Try:
=SUMPRODUCT(VLOOKUP(B3;E$3:G3;{2\3};0)*IF({1\0};1;A3-1))
Hi guys, I tried editing the formula that Cyrus posted however it is returning incorrect values for sales with more than 1 item. The Amount Sold is in column Column D and the SKU is in Column F. The lookup table is in a separate workbook called ProductCosts and the single item price is in Column E and additional item cost is in Column F. Currently the table is 35 rows in length but I changed the lookup to search to row 100 as we are constantly adding in new SKUs and product costs. The row that I am trying to implement the formula on is row 2644
=IF(D2644=1,VLOOKUP(F2644,ProductCosts!$A$1:$G$100,5,0),VLOOKUP(F2644,ProductCosts!$A$1:$G$100,5)+((D2644-1)*VLOOKUP(F2644,ProductCosts!$A$1:$G$100,6,0)))
Dazkangel, I tried editing your formula but I got absolutely nowhere as the sections where "{2\3}" and "IF{{1\0}" have completely stumped me.
Any help is very much appreciated. Thank you very much
Last edited by Orbus; Jun 23rd, 2019 at 05:55 AM. Reason: typo
Excel Regional Settings mean that some symbols don't travel too well.Dazkangel, I tried editing your formula but I got absolutely nowhere as the sections where "{2\3}" and "IF{{1\0}" have completely stumped me.
Here's dazkangel's formula formatted in a way that hopefully works for you:
(Nice take on the problem by the way, dazkangel!)Code:=SUMPRODUCT(VLOOKUP(B2,E$2:G2,{2;3},0)*IF({1;0},1,A2-1))
Matty
Hi Matty, apologies in advance for my Excel noobness but is there any way that you could explain what each part does so I can edit the formula to show the correct values on the sheet that I mentioned above that I use in real life as opposed to the example I gave in post 1 please?
I think I have it working with the following formula as the results look correct:
Does this look OK to everyone else?Code:=IF(D2642=1,VLOOKUP(F2642,ProductCosts!$A$1:$G$100,5,false),VLOOKUP(F2642,ProductCosts!$A$1:$G$100,5,false)+((D2642-1)*VLOOKUP(F2642,ProductCosts!$A$1:$G$100,6,false)))
Matty - I tried editing the formula you provided but it kept on returning the N/A error.
Try again, last time I was wrong when copying. See in my drive:
https://1drv.ms/x/s!Akz1FKD4hMCRk1uiAMaJMPwZB6W5
=SUMPRODUCT(VLOOKUP(B2,E$2:G2,{2,3},0)*IF({1,0},1,A2-1))
Like this thread? Share it with others