0Nested If statements are complicated to type down and include many parameters. The best method to replace them is to use the lookup function. Using the lookup function shortens the formula at a considerable level.
Let's take an example of a lookup function's approach to solve the problem of Nested If statement.
Suppose I want to calculate commission on sales for a salesman.The % of the commission is tabulated in ascending order of the sales amount, that means. The % of the commission will increase as the sales amount rises.
For the demonstration purpose. I have designed the following commission table.
| From | To | % |
| 0 | 79999 | 0 |
| 80000 | 100000 | 0.5 |
| 100001 | 149999 | 0.6 |
| 150000 | 199999 | 0.65 |
| 200000 | 249999 | 0.7 |
| 250000 | 299999 | 0.75 |
| 300000 | 349999 | 0.8 |
| 350000 | 399999 | 0.9 |
| 400000 | 999999999999 | 0.95 |
According to the above table. If sales figure is from 80000 to 100000, the salesperson will get 0.5% of commission on the sales amount, and the person who has credited a whopping sales amount of 400000 and above will get the commission of 0.95%
This table has multiple conditions so naturally Nested if formula comes in mind where we need to test various states using various if statement. Let us study the use of Nested If statement
As you can see the nested if statement becomes pretty lengthy and cumbersome to write.
So let solve the above issue using Lookup function.
As you can see in the above image, we have used the lookup function. The sales person Tom has credited a sales amount of Rs.272840/-.In our table, the amount falls in the range of 250000 to 299999 and % against this range is 0.75.
So here is the syntax of LOOKUP function.
1)LOOKUP( value, lookup_range, [result_range] )
2)LOOKUP( value, array )
We will take the second formula.
Our Lookup table that is the array part of the formula will be taken by commission table ranging from F2 TO H10 and will be locked by $ Dollar Sign, you can see in the above image.
While the value part of the formula will be taken by the sales achieved by the salesperson that is cell B2
After inserting the formula, we get the outcome.
Just drag the formula down.
This way we can replace Nested If statement with Lookup function.
