VLOOKUP help again please.

I

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)
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top