Forum Discussion
Apply one markup if A matches B, else apply second markup
Hi there,
I am new to this forum, and still quite new to excel too, so please be gentle with me :-)
I am looking to apply a formula that will check to see if the Product Code starts with a specific letter (B), i then want to check what category it is and then apply a markup based on the category and whether it starts with a 'B' or not.
I had a similar spreadsheet working without the category: =IF(LEFT(C6,1)="B",S6/(1-$X$3),(S6/(1-$X$4))) but i am not sure how i can incorporate the category match into the formula as well.
Your help will be greatly appreciated.
Hi Karen,
Welcome to Microsoft Excel Community!
I think that the definition of markup is the amount you want to add it to the unit price.
Based on this definition, please put this formula in cell D2 and drag it down:
=IF(LEFT(A2,1)="b",(C2*VLOOKUP(B2,$F$2:$H$4,2,0))+C2,(C2*VLOOKUP(B2,$F$2:$H$4,3,0))+C2)
I hope this helps you
Haytham
8 Replies
Hi Karen,
Welcome to Microsoft Excel Community!
I think that the definition of markup is the amount you want to add it to the unit price.
Based on this definition, please put this formula in cell D2 and drag it down:
=IF(LEFT(A2,1)="b",(C2*VLOOKUP(B2,$F$2:$H$4,2,0))+C2,(C2*VLOOKUP(B2,$F$2:$H$4,3,0))+C2)
I hope this helps you
Haytham
Hi Haytham Amairah,
I have just noticed that all products starting with the letter B are applying a percentage that is 100% more than it should be . . for example, the formula is:
=IF(LEFT(D54,1)="B",(Q54*(VLOOKUP(L54,'Sheet1'!I4:K13,2,0)))+Q54,(Q54*(VLOOKUP(L54,'Sheet1'!I4:K13,3,0))))+Q54
Result: D54 does actually = B, so it should apply % markup associated to cell Q54 in the table area of I4:K13, second column across. However, instead of applying a 55% markup, it is actually showing a 155% markup!This error is only affecting the products that do actually start with a B, all other products show the correct %.
Any idea what is causing this?
Kind Regards,
Karen
Hi Karen,
Please show me how the data looks like.
It's different from the original data in this conversation.
Ohhh, you are a star. That works great! Thank you sooooooo much :-)
