Discount Table setup

[Kanga]

As part of an assessment I have to create a discount table to show 0-4
sales attract 0% discount, 5-10 sales attract a 2% discount, 11-20
sales attract an 5% discount and so on. I created a simple table with 2
columns - the first column has the numbers 0, 5,11 and 21. And a second
column with 0%, 2%, 5%, 8%. In another worksheet I created a VLOOKUP
function to find the appropriate discount - and it works! But the
teacher states that it is close but will not work correctly - the
values in column 1 are the change points - 0,5,11,21 Column 2 contains
the discount - try again. What have I done wrong?

 

[Paul]

As part of an assessment I have to create a discount table to show 0-4
sales attract 0% discount, 5-10 sales attract a 2% discount, 11-20
sales attract an 5% discount and so on. I created a simple table with 2
columns - the first column has the numbers 0, 5,11 and 21. And a second
column with 0%, 2%, 5%, 8%. In another worksheet I created a VLOOKUP
function to find the appropriate discount - and it works! But the
teacher states that it is close but will not work correctly - the
values in column 1 are the change points - 0,5,11,21 Column 2 contains
the discount - try again. What have I done wrong?

When posting, always quote the formula you are using.
What is the fourth parameter in your VLOOKUP? In your situation, you should
be looking for an approximate match, not an exact one.

 

[Kanga]

Thanks Paul - I have edited my original message now with the VLOOKUP
formula - can you help?

 

[Paul]

Thanks Paul - I have edited my original message now with the VLOOKUP
formula - can you help?

Well, where is it? You need to post the edited form of your message. You
should do this in the same thread, by replying to my reply.

 

[Max]

Your function : =VLOOKUP(D19,Discount!A3:B7,2,TRUE)

looks ok, but think the lookup table should be Discount!A3:B6 instead,
going by your details in the post (fills only A3:B6 in sheet: Discount)?