Returning result between values

[tintostu]

Hi All

I'm trying to setup a sheet that discounts rates depending on quantity ordered therefore if the order placed is between 0 - 5 the price would be £10, 6 - 15 the price would be £9 etc etc

Please can you help

Thanks

tintostu

 

[Aladin Akyurek]

Hi All

I'm trying to setup a sheet that discounts rates depending on quantity ordered therefore if the order placed is between 0 - 5 the price would be £10, 6 - 15 the price would be £9 etc etc

Please can you help

Thanks

tintostu

Create a 2-column table like below, in A:B...

0,10
6,9
16,5
60,2

Now you can invoke:

=LOOKUP(E2,$A$2:$A$10,$B$2:$B$10)

which would yield the appropriate price for the quantity in E2.

It's easier if you name the range in A QUANTITY and the range in B PRICE, so that the formula becomes:

=LOOKUP(E2,QUANTITY,PRICE)

The following would be equivalent:

=VLOOKUP(E2,$A$2:$B$10,2,1)

=INDEX(PRICE,MATCH(E2,QUANTITY,1))