Replace a long formula of Nested IF with a simple VLOOKUP formula
Hello Formula Experts,
I am sure you all have a good grasp on the formulas in Excel. But sometimes it happens that we write unnecessary too long formula when a simple solution giving the same result is possible.
Today I am giving a example of the same and a Quiz for you. Doesn’t it sound exciting.
hmmm Yes definitely.
So first let me put the image of the example I am going to solve you.
Now you must have realized that the underlying criteria data is slab of Marks. And we used to arrive at the Result for various students generally with a Nested IF formula as I have done in Column J. There are many IF within IF to check all the criteria.
Now coming to Better Solution:
I have put a simple Formula of Vlookup but what I have done differently is in the Fourth Argument of the Formula. i.e. telling Excel to find Exact Match or Approximate Match. Generally we use “0” to find the exact match and we dont know what will happen if we write “1” i.e.Approximate Match. This article will show you one of useful application of Approximate Match.
You can see “1” in the Vlookup Formula that make Excel to find the Approximate Match and not Exact Match. In the case of slab it finds outcome which is next immediate low in column A.
Note: Your base data (slabs of Class) needs to be Sorted. Otherwise there will be weird Outcome. Hope you got the reason why. Try Unsorted base data and see what happens.
You can replace “1” with “TRUE” or you can even omit to write the fourth argument itself.
The alternate both formula giving the same outcome are
Download the Example File
Now the Quiz
The same solution you can apply to Indian Income Tax Slab Rate. I am giving below the Slab Rates for your quick reference. And two tips for arriving at the solution.
- You have to write two VLOOKUPs in the formula
- And you have to create an additional column in the Base Data as its Marginal Tax System i.e. if Income is 6,00,000, The first 2,50,000 will attract 0% tax rate; next 2,50,000 will attract 10% tax rate and; rest at 20%.
I am looking for the solution from you all.
Thanks and Regards,
CA Dhaval Paun