How to use the IF function with VLOOKUP (examples and how to)

You do not know how to use the IF function with the VLOOKUP function in any case to get the best effect. If you are looking to use the IF function with VLOOKUP with specific examples, please refer to the following article.

How to use the IF function with VLOOKUP (examples and how to) Picture 1

The following article will guide you 3 cases that often use IF function to combine VLOOKUP with examples and specific ways, please follow along.

IF function

The If function returns the value if the condition is evaluated as TRUE and will return another value if the condition is evaluated as FALSE.

Syntax

IF (logical_test, [value_if_true], [value_if_false])

Inside:

1. logical_test: an expression that can be TRUE or FALSE.
2. value_if_true : value you want to return if the logical_test argument evaluates to TRUE.
3. value_if_false : value you want to return if the logical_test argument evaluates to FALSE.
4. Learn more about the IF function here http://TipsMake.vn/ham-if-trong-excel/

VLOOKUP function

The Vlookup function is a column lookup function, which searches for a value in the first column from the left of the data table. If the function is found, it will return one of the next columns in the same row as the value in the first column you specify.

Syntax:

= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

1. lookup_value : value to search, this value can be a string, a reference or some value of the main table.
2. table_array: reference table or table containing values ​​to search, values ​​in the first column of table_array are values ​​searched by lookup_value .
3. col_index_num : index column in table_array will get the value returned to the main table.
4. range_lookup : is a logical value that helps you specify the Vlookup function to find exact or relative searches.
5. Learn more about the Vlookup function here http://TipsMake.vn/ham-vlookup-in-excel/

IF function combined with VLOOKUP

Here are examples of how to use the IF function in conjunction with VLOOKUP.

Example 1: Suppose you have the following data:

How to use the IF function with VLOOKUP (examples and how to) Picture 2

You use the IF function in combination with Vlookup to give a% reward according to the Rewards table .

If an employee in the Northern region has a turnover of> = 200,000,000, he will be rewarded with 10%, if not awarded (return 0). If an employee in the Southern region has a turnover of> = 220,000,000, he will receive a bonus of 10%. If he does not reach the turnover, it will not be normal (return 0). And if employees in the Central region have a turnover> = 100,000,000, they will be rewarded (pay 15%), otherwise it will return 0.

Request analysis

If the first employee is requested, the IF function should be used to check 'this employee's turnover' with 'corresponding regional sales in the Rewards table ', then if the condition of the if function is true then return on the bonus box% (column 3 in the Bonus table ), otherwise return '0'.

In order to fulfill the condition of the IF function, you need to use the Vlookup function to search for 'sales of the corresponding area in the Rewards table ' and return the revenue (column 2) to compare with employee's revenue. .

To return the% reward box, which is column 3 in the Rewards table , you also need to use the Vlookup function similar to the Vlookup function in terms of the IF function, but the return column in the Vlookup function is column 3.

Doing:

First, select the first cell in the Bonus% column and enter the function:

= IF (D6> = VLOOKUP (C6; $ ​​C $ 17: $ E $ 20; 2; 0); VLOOKUP (C6; $ ​​C $ 17: $ E $ 20; 3; 0); "0")

In the function:

1. D6 is the first employee's revenue box.
2. VLOOKUP (C6; $ ​​C $ 17: $ E $ 20; 2; 0), this vlookup function looks up cell C6 (first employee area) in the area column of Bonus table (C17: E20) and returns the numeric column 2 (revenue) in the reward table, the lookup type in the Vlookup function is the exact lookup.

Note: You need to position the reward table C17: E20 if you want to copy the formula to the following staff. To neck the position, after you drag the area of ​​the Reward table C17: E20, then you press the F4 key on the keyboard, automatically the function will appear more fixed symbols $ C $ 17: $ E $ 20

1. VLOOKUP (C6; $ ​​C $ 17: $ E $ 20; 3; 0) This function will return the% reward (column 3) in the Bonus table if the conditions in the IF function are true.
2. "0" is returned when the condition in the IF function is false, the employee will not be rewarded.

After you have entered the IF function, you press Enter , immediately the results will be displayed, for example, this first employee in the North, but the revenue is less than 200,000,000 so it will not be rewarded. and returns 0.

How to use the IF function with VLOOKUP (examples and how to) Picture 3

You just need to copy the function formula to other cells, you will give the percentage reward of other employees.

How to use the IF function with VLOOKUP (examples and how to) Picture 4

In addition, you can use the IF function to trap errors for the Vlookup function.

Example 2: Assume you get the following error:

How to use the IF function with VLOOKUP (examples and how to) Picture 5

# N / A error because there is no lookup_value value of Vlookup function, to avoid this error, you can use the IF function as follows:

= IF (B16 = ""; ""; VLOOKUP (B16; B5: D13; 3; 0))

Cell B16 is the lookup_value value of the Vlookup function. The IF function will check if cell B16 is empty, the function will return an empty value, if cell B16 has a value, it will execute the Vlookup function and return the search results.

How to use the IF function with VLOOKUP (examples and how to) Picture 6

You only need to enter the name of the employee to search for revenue, the function will perform a search and return the results.

How to use the IF function with VLOOKUP (examples and how to) Picture 7

Example 3: The example uses the IF function to customize the position of a reference column in the VLOOKUP function.

Assuming you have the following data, in cell C15 you have a list of 2 options: Revenue and Region , you need to use IF and VLOOKUP in combination so that when you select which option, the result The search returns exactly that option.

How to use the IF function with VLOOKUP (examples and how to) Picture 8

To make this request, you need to understand the following:

The Vlookup function will look up the data in cell B16 , and in the return results of the Vlookup function you use the IF function to customize the return column of the Vlookup function.

If cell C15 is Region , the result will return column 2 in the lookup table, otherwise it will return column 3 (the Revenue column ) in the lookup table.

So with the example above you will have the following Vlookup function:

= VLOOKUP (B16; B5: D13; IF (C15 = "Area"; 2; 3); 0)

To avoid errors like Listing 2, you can combine the IF function to catch errors, this time the function will become:

= IF (B16 = ""; ""; VLOOKUP (B16; B5: D13; IF (C15 = "Area"; 2; 3); 0))

Your results will be as follows:

How to use the IF function with VLOOKUP (examples and how to) Picture 9

When you choose to Revenue, the results will return the revenue of that employee.

How to use the IF function with VLOOKUP (examples and how to) Picture 10

Above are the 3 commonly used IF functions in combination with VLOOKUP function along with specific examples for you to follow. Hopefully through this article, you will have more useful knowledge to be able to combine the IF function and the VLOOKUP function as needed. Good luck!