A tricky lookup question

[Adam P]

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?

 

[texasalynn]

are you going to have more than 7 different percentages? if not than an if statement would work

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

 

[Richard Schollar]

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

 

[Adam P]

Yeah, there would be 10 ranges and 10 corresponding percentages.

 

[texasalynn]

then Richard's suggestion will work

 

[Adam P]

Thanks guys, I'll try that.

A