A tricky lookup question

Adam P

New Member
Joined
Aug 17, 2006
Messages
7
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?
 

Some videos you may like

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
Joined
May 19, 2002
Messages
8,458
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
Joined
Apr 19, 2005
Messages
23,707
Hello Adam

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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,675
Messages
5,549,363
Members
410,911
Latest member
AniEx
Top