When we have to do a logical test and get the result TRUE or FALSE, we can use IF function. But in case if there are multiple logical tests to give the result, we can put nested IF formula. In a single formula we can put excel IF multiple conditions within IF function. This is called as nested IF. If the conditions are many, nested IF formula may be little complicated. Modifying or Correcting the nested IF formula are difficult. To make this simple, we can use VLOOKUP to replace nested IF formula. VLOOKUP approximate match function can be used instead of excel if multiple conditions. The same is explained with the example.
The employees of an organization are eligible for commission based on the sales turnover achieved by them. The commission % is based on the sales made by them as below:
Since there are multiple conditions in to be checked for commission, we can put nested IF to calculate the same as below:
VLOOKUP approximate match to replace nested IF
Instead of nested IF, we can also use VLOOKUP with approximate match. For this we will have to create a table to define the sales and commission rate. This table should be sorted in ascending order.
With the help of this table we can put VLOOKUP with approximate match. Approximate match will bring next largest value that is less than your lookup value.
In this formula you can see that the range lookup i TRUE, that means VLOOKUP will do approximate match. Based on the turnover value, the commission % is changed in the commission column.
This formula is very easy to understand and easy to change the conditions.