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!

5 ★ | 1 Vote

May be interested

  • How to print a PDF file - The easiest way to print a PDF documentPhoto of How to print a PDF file - The easiest way to print a PDF document
    how to print a pdf file - the easiest way to print a pdf document. currently, there are quite a lot of software used to read pdf files on the technology market, but the most popular is adobe's product: adobe reader or a lightweight product familiar to those who use it.
  • How to insert and copy PDF files into Word quickly and standardlyPhoto of How to insert and copy PDF files into Word quickly and standardly
    when you receive the file or data in pdf format that you want to edit, or simply copy a paragraph in the pdf file but it is not done because the pdf file does not allow users to intervene to edit. so how to chu?
  • What is a scan? What are photo scans and document scans?Photo of What is a scan? What are photo scans and document scans?
    you can easily convert a computer file into an image using the print tool. but have you ever wondered the opposite is how to turn from an image into a data file on your computer? that is scan. so what is scan?
  • Beautiful Symbol templates in WordPhoto of Beautiful Symbol templates in Word
    beautiful symbol templates in word. you are looking for beautiful symbol templates in word to use in your word documents to be more vivid. so invite you to refer to the beautiful symbol templates in word that the article has summarized and shared below.
  • How to hide columns and hide rows in Excel 2016, 2013, 2010Photo of How to hide columns and hide rows in Excel 2016, 2013, 2010
    how to hide columns and hide rows in excel 2016, 2013, 2010. below the article share to you how to hide columns, hide rows, show hidden columns, show hidden rows in excel 2016, 2013, 2010, please follow along. track.
  • How to make Slide beautiful and professionalPhoto of How to make Slide beautiful and professional
    how to make slide beautiful and professional. presentation is an indispensable skill in work and other issues in this life. want to have a good presentation, you need to have a lot of skills, in which the indispensable presence