I have already written on basic vlookup function and conditional vlookup function. In this Excel tutor, i will show you how you can use vlookup function along with IF function. Recently i came across a situation where i need to use different vlookup formula when there is a different scenarios. So i thought even this one will help you also. This excel tips guide will help you on how to perform different range conditional vlookup in excel.
Using VLOOKUP formula with Nested IF function
Actually, i came across this formula, while i was working on a annual salary revision data for a company. There it had all the countries employees with different grades and bands in one sheet. I have been provided with a grid which has increment amount for each grade, grade upgrade and promotion for different countries. The employee data has country details, previous grade, current grade and the band. One way is to split the employee data into three different sheets and performing. But i had a restriction on that. I should do for all countries in one sheet. Below is the steps I have done.
1. I have created one more table for the Proposed Salary increment Grid. A unique id created by concatenating the previous grade, current grade and Band with the help of separating operator !. Please refer the below excel sheet. From A1: F12 contains original data table. From H2: K 12, the calculated salary increments for the different scenarios.
2. Then created the unique code in the employee sheet too with the same logic of concatenating previous grade, current grade and Band with the help of separating operator !. In the example, employee data is in A15 to G21. Added unique code in Column G.
3. Now, in the increment amount field column F17, inserted the vlookup formula with Nested If function as mentioned below.
=IF(B17=”India”,VLOOKUP(G17,$H$4:$I$12,2,FALSE),IF(B17=”US”,VLOOKUP(G17,$H$4:$J$12,3,FALSE),VLOOKUP(G17,$H$4:$K$12,4,FALSE)))
4. Copied the same formula to all the employees. The employee sheet has the increment amount for all the employees based on their grade upgradation or promotion and their countries.
The above logic is simple, Tested the condition and while the condition is met, executed one set of vlookup formula while that condition is not met, tested one more criteria and ran different set of vlookup formula and so on. By default you can Nested if for 7 times. For using IF formula more than 7 times, you need to take help of Excel VBA custom function.
Share your thoughts on the same. If you have any better solution for this or if you need any clarification on this, share it in the comment section below.