VLOOKUP function usage and specific examples

The VLOOKUP function in Excel is extremely useful for looking up data. Here’s what you need to know about using the VLOOKUP function in Excel .

You want to check a specific value in an Excel spreadsheet and look up information about it. Running a VLOOKUP saves you a lot of work in this case. It is one of the best ways to perform a vertical query in Microsoft Excel. This article will show you the meaning, usage and examples of the VLOOKUP function in Microsoft Excel.

What is the VLOOKUP function?

The VLOOKUP function is a data search function in Excel. Simply put, from an available data code, the VLOOKUP function will search in a common database list to find other data related to the original data code. There are many types of database lists, which can include a table of company personnel, product types, customer lists, or anything else. And below is a list of products that company A is selling on the market:

Database tables usually have identifiers for each product. In the data table above, the special identifier is the “Item Code” column. Note : To be able to use the VLOOKUP function, the data table must have a column containing identifiers such as code or ID, and must be the first column as in the table above.

VLOOKUP is probably the most famous function in Excel, but for good and bad reasons. On the bright side, VLOOKUP is easy to use and does something very useful. For new users, in particular, it is a joy to watch VLOOKUP scan a table, find a match, and return the correct result. Mastering VLOOKUP is an essential skill for beginners and advanced Excel users alike.

On the downside, VLOOKUP is limited and has dangerous default values. Unlike INDEX and MATCH (or XLOOKUP), VLOOKUP requires a complete table with lookup values ​​in the first column. This makes it difficult to use VLOOKUP with multiple criteria. Additionally, VLOOKUP’s default matching behavior makes it easy to get incorrect results. But don’t worry! The key to using VLOOKUP successfully is mastering the basics.

VLOOKUP has four arguments: lookup_value, table_array, column_index_numand range_lookup. Lookup_valueare the value to look up and table_arraythe vertical range of data to search within. The first column of table_arraymust contain the lookup values ​​to search. The argument column_index_numis the column number of the value to retrieve, where the first column of table_arrayis column 1.

Finally, range_lookupcontrol the behavior of finding the matching value. If range_lookupit is FALSE (or 0), then VLOOKUP will perform an exact match. If range_lookupit is TRUE (or 1), then VLOOKUP will perform an approximate match. Important, range_lookupthis is optional and defaults to TRUE, however for an approximate match to be accurate, the first column in the database table must be sorted A->Z.

Formula of VLOOKUP function in Excel

The VLOOKUP function in Excel has the following formula:

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

In there:

• VLOOKUP: Is the function name
• Parameters in bold are required.
• lookup_value: The value to look up
• table_array: The table containing the values ​​to look up, in absolute value form with a $ sign in front, for example: $A$3:$E$40.
• col_index_num: The order of the column containing the search value on table_array. For example, in table $A$3:$E$40, column B contains the value to be searched, then col_index_num here will be 2; table $C$3:$F$40, column E contains the search value, then col_index_num here will be 3.
• range_lookup: Is the search range, TRUE is equivalent to 1 (relative lookup), FALSE is equivalent to 0 (absolute lookup). This parameter is not required to always be present in the formula.

Relative lookups can only be used when the values ​​to be looked up in table_array are sorted (ascending or descending or alphabetically). For such tables you can use relative lookups, which is similar to using infinite IF . For values ​​to be looked up that cannot be or are not sorted, use absolute lookups to find the exact value.

Video tutorial on using the VLOOKUP function

Examples illustrating the VLOOKUP function

Example 1: VLOOKUP function to evaluate student and employee rankings

Suppose, you have the following student transcript:

And the classification table is as follows:

Now we will use the VLOOKUP function to enter the ratings for the students. You will notice that the rating table has been arranged in order from low to high (from weak to good), so in this case we can use relative search.

In Excel these two tables are presented as follows:

Here, the value to be searched is in column C, starting from row 6. The search range is $A$18:$B$21, the column order containing the searched value is 2.

In cell D6, enter the formula: =VLOOKUP($C6,$A$18:$B$21,2,1). This is a relative lookup formula, you can perform an absolute lookup if you want (or because the ranking table is not sorted in order) by adding 0 to the formula like this: =VLOOKUP($C6,$A$18:$B$21,2,0). Press Enter.

Click on cell D6, a small square appears in the lower right corner, click on it and drag down the table to copy the ranking formula for the remaining students.

Then, we have the result of using the VLOOKUP function to classify students’ academic performance as follows:

Are you wondering why we need to use $ before C6? $ will freeze column C, only changing the rows when you drag the formula down the table. And $A$18:$B$21 will freeze the sorting table, so it won’t change when you drag the formula.

Example 2: VLOOKUP function searches absolutely to get data

Suppose, you have an employee data table, storing employee code, full name, position. Another table stores employee code, hometown, education level. Now you want to fill in information about hometown, education level for each employee, how to do it?

Suppose you have the following table of employees and employee hometowns:

Now you want to fill in the hometown information for the employee. In cell D4, you enter the absolute search formula as follows: =Vlookup($A4,$A$16:$C$25,2,0)

Then press Enter. Click the small square that appears in the corner of cell D4 and drag it down the table to copy the formula to other employees.

To fill in the qualifications information for employees, in cell E4 you enter the absolute lookup formula: =VLOOKUP($A4,$A$16:$C$25,3,0)

You continue to press Enter and drag down to copy the formula for the remaining employees, we will get the following result:

Example 3: Using VLOOKUP to extract data

Continuing with the data set of example 2, now we will find the hometown of 3 employees: Nguyen Hoang Anh, Tran Van Anh and Nguyen Quang Vinh. I have extracted it into a new table F15:G18.

I will do this search on table A3:E13, after filling in hometown and education level. Now, enter the following absolute search formula into cell G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > press Enter.

Copy the formula for the remaining 2 employees and we get the following result:

Note that in this example, the lookup value is in column B, so the lookup range is calculated from column B, not from column A.

Example 4: Using the VLOOKUP function on 2 different Excel sheets

Returning to the data set in example 2, after the employee’s qualifications and hometown are filled in, we name the sheet QTM.

In another sheet of the spreadsheet, named QTM1, you need to get information about the qualifications and positions of employees with the order of the employees changed. This is where you see the real power of the VLOOKUP function.

To search for data about the employee’s “Qualification”, you enter the formula: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) into cell C4 of sheet QTM1.

In there:

• B4 is the column containing the value to look up.
• QTM! is the name of the sheet containing the table with the value to be searched, after the sheet name you add the !
• $B$3:$E$13 is the table containing the lookup value and the sheet containing the table (QTM).
• 4 is the serial number of the “Qualification” column, calculated from the “Full name” column on the QTM sheet.
• 0 is absolute search.

Press Enter, then copy the formula to all remaining employees in the table, we get the following result:

To search for employee “Position” data, in cell D4 of sheet QTM1, enter the formula: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), press Enter.

Copy the formula to the remaining employees, we get the following:

What is the VLOOKUP function used for?

First we need to find the exact answer to the question “what do we use the VLOOKUP function for?”.

The VLOOKUP function is used to help look up information in a data field or list based on available identifiers.

For example, inserting a VLOOKUP function with a product code into another spreadsheet will display information about the product that matches that code. That information could be a description, price, or inventory quantity, depending on the content of the formula you write.

The smaller the amount of information you need to find, the more difficult it is to write a VLOOKUP function. Typically, you will use this function in a reusable spreadsheet like a template. Each time you enter the appropriate product code, the system will retrieve all the necessary information about the corresponding product.

Things to remember about the VLOOKUP function

Here is a list of important things to remember about the VLOOKUP function in Excel:

• When range_lookup is omitted, VLOOKUP will allow an inexact match, but will still use an exact match if available.
• The biggest limitation of the function is that it always takes the rightmost value. The function will take data from the columns to the right of the first column in the table.
• If the lookup column contains duplicate values, VLOOKUP will only match the first value.
• The function is not case sensitive.
• Suppose there is an existing VLOOKUP formula in a worksheet. In that case, the formulas may break if you insert a column into the table, because the column index values ​​are hard-coded and do not change automatically when columns are inserted or deleted.
• VLOOKUP allows the use of wildcard characters, for example, the asterisk (*) or the question mark (?).
• Suppose the table you are working with contains numbers entered as text. If you are only getting numbers as text from one column in the table, that is fine. But if the first column of the table contains numbers entered as text, the #N/A! error will display if the lookup value is also not in text.
• The #N/A! error occurs if the VLOOKUP function cannot find a match for the provided lookup_value.
• The #REF! error occurs if:
  • The col_index_num argument is greater than the number of columns in the provided table_array
  • The formula attempted to reference cells that do not exist.
• The #VALUE! error occurs if:
  • The col_index_num argument is less than 1 or is not recognized as a numeric value.
  • The range_lookup argument is not recognized as one of the logical values ​​TRUE or FALSE.
• How to use the IF function in Excel
• How to use the AVERAGEIF function on Excel

Common errors when using the VLOOKUP function in Excel

After knowing how to use Vlookup, you also need to know common errors and how to handle the problems.

Lookup value is in wrong column

One of the most common errors you’ll encounter when using a VLOOKUP formula is the function returning only the #N/A value. This error occurs when it can’t find the lookup value you asked VLOOKUP to look for.

In some cases, you can use the XLOOKUP function instead of VLOOKUP, however, this error is only easily fixed in the VLOOKUP function.

How to fix the problem:

The reason why VLOOKUP doesn’t find the desired value could be because the value isn’t in the table. However, if it returns all #N/A values, and the lookup value is text, it’s most likely because it can only read from left to right.

When creating a table in Excel for VLOOKUP, make sure to place the lookup value to the left of the value you want to return. The easiest way to do this is to place it in the first column of the table.

Now the formula will return a value:

In general, VLOOKUP is a great way to query data faster in Microsoft Excel. Although VLOOKUP queries are vertical on columns and have some other limitations, Microsoft is constantly updating the lookup features on Excel to expand their applications. For example, HLOOKUP, XLOOKUP… can help you look up data in many different directions. QuanTriMang.com will continue to provide you with information on how to use this function in the following articles.