The nested IF function is an equation that allows you to test in multiple ways whether a condition is true or false. Today you will learn how to replace the nested IF function with the CONSULTAV function
What is the nested IF function?
The nested IF function is one of the most used in Excel because it allows you to test the result of a logical test in a compound way and carry out a specific action based on that result. With this function you can perform up to three actions each time it is invoked, so you will apply two logic tests or more.
What is the CONSULTAV function?
The QUERY V function is an equation that allows searching in a column until a value determined by the user is found. Users who are familiar with the VLOOKUP function often compare it to how to use the VLOOKUP function .
The CONSULTAV function works through four arguments: the cell where you want to show the result, the beginning of the column that includes a set of cells where the search will be carried out, the end value of the column and a Boolean value that will be “FALSE” if you’re going to do an exact search, so this is the most recommended.
How to replace the nested IF function with the V QUERY function
If you have the Microsoft Office application on your computer or you decide to install Microsoft Office online and you have a data set in Microsoft Excel, you can replace the nested IF function with the CONSULTAV function.
Simplify the use of the nested IF function
As the nested SI function is added to a formula, its complexity and understanding increases due to the presence of multiple evaluations. Therefore, the process of interpreting the formula becomes complicated.
It is useful to use the CONSULTAV function as an alternative to overriding the repetitive use of the nested IF function . In this way, the size and difficulty of the formula is reduced. We also recommend that you learn how to use the VLOOKUP function in Excel so that you get the most out of this tool.
How is the nested IF function applied?
We can apply the IF function nested with an example such as the following: consider a list of ten employees whose names, surnames, the date of entry of each one, the years of seniority and the recognition that will be a cell subject to a condition.
Considering that the employees started their work on the same day and month but in different years, it is possible to indicate the recognition based on the seniority time and apply the IF function nested to a formula.
Inserting the formula In Excel
The formula under the use of the nested IF function would have the following data: = IF (E3> = 30, “trip and increase”, IF (E3> = 25, “increase”, IF (E3> = 20, “trip” , IF (E3> = 15, «diploma», IF (E3> = 10, «medal», IF (E3> = 5, «Pin», «NA»)))))) In this way, we indicate that if the length of service is less than 5 years, there is no recognition, if it is between 5 and 9 years old, the employee will receive a pin.
Then, between the ages of 10 and 14, you will get a medal, between the ages of 15 and 19 a diploma, between the ages of 20 and 24 you will receive a trip, between the ages 25 and 29 a trip, and those 30 years or more will a trip and an increase in salary. Therefore , the nested IF function is used five times .
Replace the nested IF function with CONSULTAV
To replace the nested IF function with QUERY V you must create a table that specifies the age and each of the acknowledgments. According to the previous example, we must enter 0 for NA, 5 for pin, 10 for medal, 15 for diploma, 20 for increase, and so on until we complete 30 years.
Assuming that the years of seniority are in column A and the recognition in column B, then the formula will be = CONSULTAV (C5, $ A $ 18: $ B $ 24.2), where C5 refers to the column “Seniority” , “$ A $ 18: $ B $ 24” corresponds to the column of the new aging table and the value 2 to the possible results that will be obtained.
