Confused on lookup formulas. Any Help?

[Aranon]

I am trying to make a pricing markup sheet, where if the price is between (example) between 1 cent to $1 the cost of the item would be multiplied by a certain percent(3.7), but if the cost of the item was more, like between $21.01 and $22 it would be multiplied by a different % (2.65) and so on...

I know my formula should say something like Cell XY in Column B (cost of item) compareto column D&E, if cost of item in cell XY is >= column D but
<= column E then multiply by column F

How do I write this so it will work? Here is a portion of my pricing guide lines I'm making for selling parts.

Column B..............Column D...............Column E.............Column F
Part cost............Part cost is >=........Part cost is <=.....Multiply by
...$..1.83.................$0.01..................$1.00....................3.70
...$22.50.................$1.01..................$2.00....................3.65
...$12.34.................$2.01..................$3.00....................3.60
...$..0.83.................$3.01..................$4.00....................3.55
...$..1.85.................$4.01..................$5.00....................3.50
Any help would be greatly appreciated. I bought the excel Bible 2003 but I can't seem to put it all togeather to work. I would like it so I just type in a part name and cost of the part and the worksheet looks it up and add the correct markup and puts the selling price of the item in a cell by the cost of the item in colum C. ( Column A would be the description of the item)Thanks

[TheRobsterUK]

Would the "IF(AND(...." function work? For example:

=IF(AND(cost of item in cell XY is >= column D, cost of item in cell XY is <= column E), multiply by column F)

Worth a shot.

-Rob

[Aranon]

Could you write it as a formula please?

[Bryan Hessey]

I am trying to make a pricing markup sheet, where if the price is between (example) between 1 cent to $1 the cost of the item would be multiplied by a certain percent(3.7), but if the cost of the item was more, like between $21.01 and $22 it would be multiplied by a different % (2.65) and so on...

I know my formula should say something like Cell XY in Column B (cost of item) compareto column D&E, if cost of item in cell XY is >= column D but
<= column E then multiply by column F

How do I write this so it will work? Here is a portion of my pricing guide lines I'm making for selling parts.

Column B..............Column D...............Column E.............Column F
Part cost............Part cost is >=........Part cost is <=.....Multiply by
...$..1.83.................$0.01..................$1.00....................3.70
...$22.50.................$1.01..................$2.00....................3.65
...$12.34.................$2.01..................$3.00....................3.60
...$..0.83.................$3.01..................$4.00....................3.55
...$..1.85.................$4.01..................$5.00....................3.50
Any help would be greatly appreciated. I bought the excel Bible 2003 but I can't seem to put it all togeather to work. I would like it so I just type in a part name and cost of the part and the worksheet looks it up and add the correct markup and puts the selling price of the item in a cell by the cost of the item in colum C. ( Column A would be the description of the item)Thanks

I think the attached will save much in explanation, a simple Lookup table.

note, I put your table on Sheet2 one row in error, B9:B13 should be one row higher, and a new B13 for prices $5.00 and above entered, but the idea is still the same.

hth
---

[Aranon]

I tried adapting that formula to my pricing sheet but wouldn't work.

I have included as an Attachment my Excel worksheet with Sheet 1 and 2. Sheet 1 has the cost of the parts and Sheet two has the the look up tables to find the correct mark-up based on the cost of the items.

I am sure this is simple for some, but it's got me pulling my hair out. Can anyone please help.
Thanks in advance. Aranon

[Bryan Hessey]

I tried adapting that formula to my pricing sheet but wouldn't work.

I have included as an Attachment my Excel worksheet with Sheet 1 and 2. Sheet 1 has the cost of the parts and Sheet two has the the look up tables to find the correct mark-up based on the cost of the items.

I am sure this is simple for some, but it's got me pulling my hair out. Can anyone please help.
Thanks in advance. Aranon

as

=IF(C4="","",C4+(C4*VLOOKUP(C4,Pricing,3,TRUE)/100))

---

[Aranon]

Thank you for helping me, but that formula only marked it up 16 cents. From $4.66 to $4.82. It should have multiplied $4.66 times 3.5 which is $16.31 .

Sheet 1 Cell C4 x Sheet 2 Cell C6 or
$4.66 x 3.5 = $ 16.31

What do I need to change so it will multiply by the corresponding mark-up?

Thank you again.
Aranon

[BenjieLop]

I tried adapting that formula to my pricing sheet but wouldn't work.

I have included as an Attachment my Excel worksheet with Sheet 1 and 2. Sheet 1 has the cost of the parts and Sheet two has the the look up tables to find the correct mark-up based on the cost of the items.

I am sure this is simple for some, but it's got me pulling my hair out. Can anyone please help.
Thanks in advance. Aranon

Try this. On Cell D4 (Sheet 1), enter this formula:

=if(C4="","",C4*(vlookup(C4,Sheet2!$A$1:$C$51,3)))

and copy down until your requirements are met.

Hope this helps you.

Regards.

[Aranon]

Yes, YEs, YES, YES !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thank-you, Thank-You, Thank-You!!!!!!!!!!!!!!!
It works beautifully. Perfect.

Thank You again.
Aranon

[BenjieLop]

You are welcome. I am glad that I can be of help.

Regards.

[Solon Magrizos]

benjielop,
i had a similar question torturing me for some time now !
thanks so much

[BenjieLop]

benjielop,
i had a similar question torturing me for some time now !
thanks so much

You are welcome. I am just glad that I can be of help.