# Thread: Retreive a Value from a Chart with Ranges (Excel 97)

1. ## Retreive a Value from a Chart with Ranges (Excel 97)

Hi!
I'm sure someone will find this to be very easy.... I'm needing to retrieve a value from a chart that has ranges. Normally I do a lot of vlookups, and nested if's, however there has to be an easier, more appropriate way to determining the value when there is a "range" as opposed to a defined value. Below is an example of what I'm trying to do:

I need to refer to the chart and determine what % discount to apply (below is the chart)... so if the bill is \$2,500, I'll need a formula to refer to the cell with the \$2,500 in it and look up the low end of the range and the high end of the range and determine which range it falls into, and then select the proper percentage. The percentage I would need in this case is 30.5%. Any help on this one would be great!
Thanks!!
LJM <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
\$0.01 \$1,809.99 0.0%
\$1,810.00 \$2,069.99 15.0%
\$2,070.00 \$2,329.99 21.4%
\$2,330.00 \$2,849.99 30.5%
\$2,850.00 \$3,369.99 31.4%
\$3,370.00 \$100,000.00 32.0%

2. ## Re: Retreive a Value from a Chart with Ranges (Excel 97)

How about something like
<pre>=VLOOKUP(D1,\$A\$1:\$B\$6,2)</pre>

Assuming it is set up like the table

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><tr><td align=center valign=bottom>1</td><td align=right valign=bottom>\$0.01 </td><td align=right valign=bottom>0.00%</td><td align=right valign=bottom>

3. ## Re: Retreive a Value from a Chart with Ranges (Excel 97)

What do you want to do if the amount is greater than \$100,000?

4. ## Re: Retreive a Value from a Chart with Ranges (Excel 97)

Anything over \$100,000 would also be at the 32%.

5. ## Re: Retreive a Value from a Chart with Ranges (Excel 97)

Hi, thanks for the response. The vlookup formula you replied with works if I'm looking for a specific value in the chart, however these are ranges.... for example the \$2,500 fits into the fourth range I listed (\$2,330.00 to \$2,849.99), so I'd want the percentage of 30.5% to be returned as my value. With the vlookup formula you mentioned the \$2,500 is not in the data range so a #NA would show up as there was no match. If you have any other ideas I'll take them... thanks!!
LJM

6. ## Re: Retreive a Value from a Chart with Ranges (Excel 97)

Then Steve's formula and table WILL work. Have you tried it?

7. ## Re: Retreive a Value from a Chart with Ranges (Excel 97)

Silly me... I always use the false command in my vlookup formulas... I wasn't thinking outside the box... Steve will need to ignore my silly email saying it didn't work... it does work.
Thanks to both of you!!
LJM

8. ## Re: Retreive a Value from a Chart with Ranges (Excel 97)

False is for an exact match. Without the FALSE it works as you wanted it to work, to allow a range of values to give a single value. You only need the "lower limit" of each "region" and the "value" and the vlookup will do the rest...

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•