formula based on prefix

[splat]

Hi

I need a different markup formula based on prefix in inventory list ie:

ppbolt would get 15% markup (prefix is pp) (Description)
detnut would get 20% markup (prefix is det) (Item Code)
3rnail would get 25% markup (prefix is 3) (Item Code)

col A would be item code
col B would be description (the pp prefix is on some of the items here)
col C would be cost
col D would be retail

Thanks

 

[S Davis]

Create a table on the side, two columns, like so:

A B
pp 0.15
det 0.20
3 0.25

Now you can write a formula to analyze the first few letters of each
inventory. Let's say inventory list starts at D1, and the cost for each
inventory is at E1:

=E1+E1*(IF(LEFT($D1,3)="det",VLOOKUP($D1,$A$1:$B$3,2,FALSE),IF(LEFT($D1,2)="pp",VLOOKUP($D1,$A$1:$B$3,2,FALSE),IF(LEFT($D1,1)="3",VLOOKUP($D1,$A$1:$B$3,2,FALSE),0))))

 

[S Davis]

Sorry, didn't see your desire for specific columns - you'll need to
change to suit your needs. All that the formula is doing is looking at
either the first, first two, or first three letters and comparing it to
an exact match (FALSE) in a vlookup table, then returning that value
and adding it * the cost of the item to the original cost.

 

[splat]

I'm not coming right with these formulas, please help.

 

[Tom Ogilvy]

over then add another then down.