Cell to update depending on input of another cell

[gebo84]

Hi All,

I have a price calculator I'm trying to set up, and i want the costs to update depending on the number that is input into a cell.

So in B12 the Qty will be entered and i need this to update cell B14 based on a couple of things.

if the number is between 10-19 i need it to return the value in cell N3
if the number is between 20-49 i need it to return the value in cell N4
if the number is between 50-99 i need it to return the value in cell N5
if the number is between 100-199 i need it to return the value in cell N6
if the number is between 200-299 i need it to return the value in cell N7
if the number is between 300-499 i need it to return the value in cell N8
if the number is between 500-999 i need it to return the value in cell N9
if the number is between 100-1999 i need it to return the value in cell N10

I've tried the IF formula combined with the IF(AND formula and i cant get it to work

thanks

 

[Fluff]

What if B12 is less than 10?

 

[gebo84]

What if B12 is less than 10?

It shouldn't be as the MOQ is 10 but i guess the formula could include less than 10

 

[Fluff]

Ok, how about

=INDEX(N3:N10,MATCH(B12,{10,20,50,100,200,300,500,1000},1))

 

[gebo84]

Ok, how about

=INDEX(N3:N10,MATCH(B12,{10,20,50,100,200,300,500,1000},1))

This works perfect thank you!

 

[Fluff]

You're welcome & thanks for the feedback.