Using Vlookup IF Combination in Excel

IF function in Excel is a conditional function used to search for values satisfying specific conditions. It is a basic and user-friendly function when values need to meet one of two given conditions. In this article, we explore another dimension of the IF function by combining it with Vlookup.

Guide on Combining Vlookup and IF Functions in Excel

Discussing the Vlookup function, this is a function to search for the required value in a column. Using the Vlookup function to look up information that satisfies both rows and columns. Combining the use of Vlookup and IF, we get an operation that searches for many conditions, including both rows and columns. To better understand how to use Vlookup and IF together, the following guide will explain the usage for you.

How to use Vlookup IF combined in Excel

Suppose we have a table tracking the daily revenue of a department in the company, divided into 4 groups A B C D, each group has different targets. Our task is to find out the results of employees who meet the targets. Below is a summary table of the revenue generated by 8 employees from the 4 groups yesterday.

Here are the targets of the groups, which are crucial because if we use Vlookup, we need to look up information from here.

Purpose of the IF function:
In this example, the IF function will identify which cases meet the revenue criteria and determine whether the result is achieved or not in the result cell. However, the Excel IF function cannot access data in the second table to determine the revenue of the group achieving the target, especially when there are up to 4 different targets. If this is a table with 100 employees and dozens of groups, you may spend the entire day creating processing.

Formula:
- IF (Logical_test, Value_if_True, Value_if_False)

Explanation:
- If the “Logical_test” is true, the function returns “Value_if_True,” otherwise, it returns “Value_if_False.”

Purpose of the Vlookup function:

As mentioned earlier, if the Excel IF function cannot determine whether those employees have met the target, the Vlookup function will be responsible for filtering out satisfying values for each member in their respective groups. Afterward, it will further consider the IF function to ultimately produce the result.

Formula:
- VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Explanation:
- lookup_value: The value used for lookup
- table_array: The lookup value table, in absolute address form (with $ in front by pressing F4)
- col_index_num: The order of the column to retrieve data from the lookup value table.
- range_lookup: The search range, TRUE is equivalent to 1 (relative lookup), FALSE is equivalent to 0 (absolute lookup).

How to use the Vlookup and IF functions together in this example

When combining two nested formulas, it takes the following form:

IF(Logical_test >= Vlookup(lookup_value, table_array, col_index_num, [range_lookup]), Value_if_True, Value_if_False)

With Logical_test >= Vlookup, indicating that in our scenario, we need to consider greater than or equal to for achievement.

Step 1: First, enter the command line into the result cell as follows: =IF(D4 >= Vlookup(C4, $C$16:$D$20, 2, 0), 'Achieved', 'Not Achieved'.

- With D4 >= Vlookup: D4 here represents the revenue attributed to the first employee. When D4 >= Vlookup meets the condition, it will display 'Achieved' or 'Not Achieved' otherwise.

- C4, $C$16:$D$20, 2, 0: References the group column in column C and the row order from 16 to the target column in column D, with the final value in row 20.

Note: To display characters in the list format, you need to enclose the range as usual and then press F4.

Step 2: Upon pressing Enter, you will see the result for the first employee, Nguyễn Ngọc Anh, belonging to group A with Revenue of 18. When scrolling down to the group (figure number 3) , you will see that group A only requires a target of 18, so employee Nguyễn Ngọc Anh is displayed as Achieved.

Performing similarly with the remaining values, you will get results as shown below.

Certainly, you can verify the accuracy when using the Vlookup and IF combination.

I hope with this article on using Vlookup and IF, you can self-learn Excel and understand the synergy between these two functions without the need for training centers. Notice that we perform Vlookup first before considering IF, making Vlookup the target object that IF needs to satisfy. If there's any step you don't understand during the process of implementing the article or self-learning Excel, feel free to ask here for quick clarification.

In our recent exploration, we learned how to use Vlookup and IF together. To supplement your knowledge and passion for Excel, Mytour recommends exploring Hlookup, a function similar to Vlookup, for details on using the Hlookup function. Additionally, don't forget about Sumif function, Sumif function combines Sum and IF to calculate conditional sums. On the other hand, if you have a long spreadsheet and don't want to waste time counting columns, use the Round function right away.

Furthermore, in Excel, there are numerous other fundamental functions that you can self-learn and practice. Each basic function in Excel has different syntax and purposes for you to choose and integrate into your specific problems. Memorizing all the basic functions in Excel requires diligence and a good memory.