VLOOKUP help again please.

[iDontLikeExcel]

I have also finished my workbook. Now I am stumped on another vlookup
function. Since some people dont like to download the file I will
explain it in words and attach some screenshots.

I need to use VLOOKUP and the discount table on the sheet 3 to
calculate the relevant discount rate for the discount rate column in
sheet 4. I have attempted it as you see in the screenshot, but it
returns #NA. I am not sure if this is because my function is wrong or
the discount table is set up wrong (I have entered the values such as
"21 to 50", and im not sure if excel recognises this means from 21 to
50 teddies.)

It sounds like it could be easily solved but I cannot work it out.

First I will attach the discount image.

File Attached: http://www.excelforum.com/attachment.php?postid=343806 (discount.jpg)

 

[iDontLikeExcel]

and here's the invoice (sheet4).

File Attached: http://www.excelforum.com/attachment.php?postid=343814 (invoice.jpg)

 

[dnickelson]

Excel will pretty much certainly not recognize what "21 to 50" means.
if you want vlookup to return a value, it has to find the specified
value (D17 or 45 in your example), find that in a list, and return a
corresponding value from the specified column. if you want multiple
values to return the same number (you do) you will need to drag out
your list, say
Purchase_Amount Discount
1 0%
2 0%
3 0%
4 0%
5 0%
6 2%
7 2%
8 2%
9 2%
10 2%
11 5%
12 5%
13 5%
Etc...


it will then look in the list on the left, say for the number 8, when
it finds it, it will return 2% (the number from the next column over.

 

[wiseman]

You could use vlookup with 'true' in the last part, see this site the
'simple
http://www.mvps.org/dmcritchie/excel/vlookup.htm#gradebook
you would only have to put the lower limits on the lookup table i.e.
21 value for less than 21
50 value for between 21 and 50

Hope that is some help,
Barbara.