# Nested If Statements with numerous conditions

#### Gregor77

##### New Member
The new boss gave me a bit of a challange here....

Using excel 2003 (SP3)

Cell "I6" is a calc dividing a retail target and how the person performed giving me a %. example 158%. Then the person belongs to a catagory eg: 1,2,3,4. Each catagory has a % target and a \$\$\$ value assigned to the target.

Here is what I put together, but it will only calc the first incentive... if the % in "I6" changes to like 500%, the value is still the same? I did something wrong!

=IF(I6>='Incentives'!B3,'Incentives'!C3,IF(I6>='Incentives'!B4,'Incentives'!C4))

B3=100% on another tab
B4= \$400

And so on...

Ideally it would be great if it was more like: if the person is catagory 1, and they are = or greater than 100% then apply incentives B3 (which is the \$\$\$ value if over a certain %), then if the person is 125% then apply Incentive C3, if over 150% then apply INCentive C4.

I would just like to see what the \$\$\$ is if someone is at a certain %.

Any help would be much appreciated!!!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Jonmo1

##### MrExcel MVP
Try a lookup..

=LOOKUP(I6,'Incentives'!B3:C4)

Be sure the data on Incentives, is sorted Ascending by column B.

Hope that helps.

#### Gregor77

##### New Member
I tried that one, but the rates change monthly, but the data is changed daily. How would look up know what the % range is?

#### Jonmo1

##### MrExcel MVP
I'm not sure I understand the problem

If the rates change, I presume they are changed in the B3:C4 range..
Then the formula would be using the updated rates..

#### Gregor77

##### New Member

Yes, it does work, thanks! I didn't realise that it adjusted based on the rates on the main page. But how do I get the "Lookup" to function if nested?

=IF(D6="1",LOOKUP(I6,'Incentives'!\$B\$2:\$C\$4,IF(D6="2",LOOKUP(I6,' Incentives'!\$B\$8:\$C\$10))))

Would this work?

#### iDeals

##### Board Regular
Code:
``=if(and(i6>=0.1,i6<0.125),incentives!b3,if(and(i6>=0.125,i6<0.15),incentives!c3,if(i6>=0.15,incentives!d3,"")))``

#### Jonmo1

##### MrExcel MVP

How many options are there for D6?
And is D6 literally just a number 1 or 2 or 3 etc?

You can try

=LOOKUP(I6,CHOOSE(D6,'Incentives'!\$B\$2:\$C\$4,'Incentives'!\$B\$8:\$C\$10))

The choose part can handle up to 29 different ranges based on the value of D6 (1 through 29)

#### Gregor77

##### New Member
Yes, D6 is just 1,2,3,4...

#### Jonmo1

##### MrExcel MVP
Also, if your lookup ranges are consistently spaced, ie every 6 rows like your example
B2:C4 then 6 rows down (from B2) is B8:C10, then 6 rows down (From B8) is B14:C16 etc...

Or another similar pattern as long as it's consistent..

Then you can do this

=LOOKUP(I6,OFFSET('Incentives'!\$B\$2:\$C\$4,(D6-1)*6))

The 6 is the number of rows between the first row of each range (2,8,14,20)

#### Gregor77

##### New Member
Wow that works, I learned allot today! Thanks!!!!

Replies
21
Views
643
Replies
7
Views
231
Replies
2
Views
155
Replies
2
Views
245
Replies
3
Views
148

1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back