Excel range fuckery [SOLVED]

Dr. Frenchenstein

So i'm trying to build a formula that will key off cells that define an upper and lower limit, for a commission calculation.

0-50,0000 2%
50,001-85,000 3%
85,001-100,000 8%

I'm trying to create a formula that will take a salesperson's commission number, and apply the correct percentage. I'm going to keep hunting around on google but i don't think i'm using the right terminology (I was trying range and array, but those are something else)

Any experts out there (this is probably super easy, i'm just dumb)?

Deebaser

Nested Ifs would do the job

=IF(C2<50001,.02,IF(C2<85001,.03,.08))

Deebaser

In english
If a Cell is less than 50,000 you want 2%, else IF a cell is less than 85,000 you return 3%, otherwise it's 8%

Dr. Frenchenstein

Oh snap, lookup functions are the SHIT.

i was doing if's but i figured there had to be a better way.

jackal

VLookup and HLookup have an option to match as if it is matching a range. You would set up the table like:
0 | .02
50,001 | .03
85,001 | .08

So 20,000 would match .02. I don't know the exact syntax. It's been a while since I've been an excel monkey, but the help for those functions should get you the rest of the way.

Dr. Frenchenstein

you actually don't need the V or the H

the formula i ended up using is (say those columns are a-c) =lookup(sales,A1:C3) and you get your result!!!

Deebaser

Use "vlookup". No one uses "lookup".
Just pointin' out best practices.

Daenris

Deebaser wrote: »

Use "vlookup". No one uses "lookup".
Just pointin' out best practices.

Yeah, my version of Excel (2010) even points out that lookup is only provided for backward compatibility with older versions. Vlookup (and hlookup) give you a bit more control over the search via allowing you to select which column (or row) you return the result from and providing a boolean flag for either doing exact or nearest matches.

Dr. Frenchenstein

vlookup looks for a direct match, right? I'm looking for a value within the range.

like if i did =vlookup(Sales,A1:C3,3) it would only return the percentage if the sales value matched a value in A, right?

Deebaser

=vlookup(Sales,A1:C3,3,TRUE) will sort you out.

Dr. Frenchenstein

So it does! thank you sir, you are a scholar and a gentleman.

I've always used the FALSE designator, i've never even looked at what true did.

Mods, you can lock this