How to change a value according to a rule - bigger than, smaller than

[olibeira]

Hello,

My problem is very simple.

I have a cell where there is a value which is a quantity.

I need to create a formula to change the price according to the quantity.

I already have the information of the different prices in the rows.

From 0 to 10 units, price is 10 euros, from 11 to 20 units the price is 8 euros, more than 20 units the price is 5 euros.

I tried to do that with the IF function by I guess you can't create multiple arguments such as bigger than x and smaller or equal than y.

Thank you for your help.

[philaugust2004]

=index($f$2:$g$4,match(a1,$f$2:$f$4,1),2)

A1 is the quantity you enter and c1 is formula as above

[jeffreybrown]

You could create a lookup table as shown in post #2 and use...

=LOOKUP(A1,F2:G4)

or, if you did not want to create a lookup table you could use...

=LOOKUP(A1,{0;11;21},{10;8;5})

[olibeira]

=index($f$2:$g$4,match(a1,$f$2:$f$4,1),2)

A1 is the quantity you enter and c1 is formula as above

Thank you, i tried putting that formula in excel but it doesnt work.

Can you explain me the how the formula works so that I can try to replicate it.

Update: I already understood. Thanks.

[olibeira]

You could create a lookup table as shown in post #2 and use...

=LOOKUP(A1,F2:G4)

or, if you did not want to create a lookup table you could use...

=LOOKUP(A1,{0;11;21},{10;8;5})

Thanks, I've tried the lookup table and it works.

[jeffreybrown]

Happy to hear you now have a working solution. You're very welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[olibeira]

Happy to hear you now have a working solution. You're very welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Yes, it solves.

I never worked with that function before that's why I was having difficulties.

[jeffreybrown]

This might help you in your quest to learn about more formulas...

https://support.office.com/en-us/art...90033e188#bm12

or

http://blog.contextures.com/archives...ays-16-lookup/