Blog

How to Replace Vlookup Formula instead of IF Formulas

How to Replace Vlookup Formula instead of IF Formulas

                  

                         We look at how to replace a typical nested IF formula with a VLOOKUP formula. Compared to nested IF statements, VLOOKUP is simpler and more transparent. It's also easier to adjust later. Once set up, you can change the formula's logic without even touching the formula itself.

You might build or inherit a worksheet that uses a series of nested IF statements to assign values of some kind. Many people use nested IF statements this way because the approach is easy once you get the hang of it. But nested IF statements can be difficult to maintain and debug.

 

Step -1

Write complicated if formula for the scenario of calculating the incentive for the salesman. =IF(E15<10000,"0",IF(E15<20000,"2",IF(E15<30000,"3",IF(E15<40000,"4",IF(E15>40000,"5")))))


 



 
Step -2

Here we will assign the table for the Vlookup Formula by creating the table with the criteria,
and we will write the V lookup formula with approximate match by writing the "True" in the Range Lookup option
and it will return the approximate value from the table. and here the result is dynamic compare to nested if formula while by changing the value in the table will bring the changes in the results also.










This method will save lot of time and also easy to audit the formula compare to complicated if formula.


 

Video Link: Watch Video

Phone : +91 8606648045
Email : info@excelskillcorner.com
Excel Skill Corner
Kerala, India.