# A tricky lookup question

##### New Member
I have a column of values in currency form. I need a formula that will look each value up in a "range of ranges" and apply the corresponding percentage to the original value, i.e. if the value is between \$0 and \$5000, multiply by 10%; if the value is between \$5001 and 10000, multiply by 13%; if the value is between \$10001 and \$20000, multiply by 15%, and so on.

Is this possible? Anybody have any ideas?

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### texasalynn

##### Well-known Member
are you going to have more than 7 different percentages? if not than an if statement would work

Code:
``if(and(a2<20000,a2>10000),a2*15%,if(and(a2<10001,a2>5000),a2*13%,....and you get the idea)``

#### Richard Schollar

##### MrExcel MVP

Have you used a VLOOKUP before? You could input a table with two sets of values: the first column to contain the boundaries eg \$0,\$5001,\$10,001 etc and the second column to hold your percentages corresponding to that vaue eg 10%,13%,15% etc. Then the formula you would need would resemble:

=VLOOKUP(A1,Values!\$A\$1:\$B\$10,2)

Where your values are containe in A1:B10 of sheet Values (rename as appropriate).

Make sense?

Richard

##### New Member
Yeah, there would be 10 ranges and 10 corresponding percentages.

#### texasalynn

##### Well-known Member
then Richard's suggestion will work

##### New Member
Thanks guys, I'll try that.

A

Replies
3
Views
389
Replies
2
Views
45
Replies
22
Views
234
Replies
11
Views
174
Replies
4
Views
61