HLOOKUP with tiers

**LEICHO** · 08-15-2013, 04:48 PM

Hi everyone!
I'm trying to create an HLOOKUP formula that will match a number of sales with a commission % for 3 tiers. I haven't gotten too far with this despite it's simplicity. Please help! I'm getting frustrated.

The sales $$ are:
* $344
* $895
* $976

and the commission % are: over $900 is 4%, more than $600 is 2% and below $600 is 1%

**cpwalker** · 08-15-2013, 05:18 PM

I'm assuming one row is one sale in your sales data. If that's true, I think it would be easier to use nested IF formulas for the commission calculation. The formula would be like the below, where cell A2 represents the price of the item sold in that row.

=IF(A2<=600,1%,IF(AND(A2>600,A2<=900),2%,3%))

The $600 and $900 cut-off points are entered directly into the formula , but best practice would be to reference cells containing those values so they can be easily changed by anyone.

**LEICHO** · 08-16-2013, 04:57 PM

Thank you so much for the reply! However, I'm stuck on trying to figure out how to do it with the HLOOKUP formula

**LEICHO** · 08-16-2013, 04:58 PM

Actually, nevermind, i figured out the mistake. i wasn't entering the data in ascending order for the lookup table, instead I was entering in descending order. Thanks your help though!!