VLOOKUP function: How to use it and specific examples

The Vlookup function is widely used in Excel. Below are detailed instructions on how to use the Vlookup function in Microsoft Excel.

The Vlookup function is widely used in Excel. Below are detailed instructions on how to use the Vlookup function in Microsoft Excel .

 

You want to check a specific value in an Excel spreadsheet and look up information about it. Running a VLOOKUP saves you considerable effort in this case. It's one of the best ways to perform a vertical query in Microsoft Excel. This article will explain the meaning, usage, and provide illustrative examples of the VLOOKUP function in Microsoft Excel.

 

What is the VLOOKUP function?

The VLOOKUP function is a data lookup function in Excel . Simply put, starting from a given data entry, the VLOOKUP function will search a list of related data in a database to find other data related to that entry. Database lists can be of many types, including tables about company personnel, product categories, customer lists, or anything else. Below is a list of products that company A is selling on the market:

Database tables often have identifying markers for each product. In the table above, the special identifying marker is the "Item Code" column. Note : To use the VLOOKUP function, the data table must have a column containing an identifying marker such as a code or ID, and it must be the first column as in the table above.

 

The VLOOKUP function is perhaps the most famous function in Excel, but for both good and bad reasons. On the good side, VLOOKUP is very easy to use and does something very useful. Especially for new users, watching the VLOOKUP function scan a table, find a match, and return an accurate result is incredibly enjoyable. Mastering the VLOOKUP function is a necessary skill, from beginners to advanced Excel users.

Conversely, unlike INDEX and MATCH (or XLOOKUP), the VLOOKUP function requires a complete table with lookup values ​​in the first column. This makes using the VLOOKUP function with multiple criteria difficult. Additionally, VLOOKUP's default matching behavior makes it easy to get inaccurate results. But don't worry! The key to successfully using the VLOOKUP function is mastering the basics.

VLOOKUP has 4 arguments : lookup_value, table_array, column_index_numand range_lookup. Where:

• Lookup_valueis the value we are looking for
• table_arrayThis is the vertical data range to search within. The first column table_arraymust contain the lookup values ​​to be searched.
• The argument column_index_numis the column number of the value to be retrieved, where the first column table_arrayis column 1.
• Finally, range_lookupcontrol the behavior to find a matching value.
  • If range_lookupthe result is FALSE (or zero), the VLOOKUP function will perform an exact match.
  • If range_lookupthe answer is TRUE (or 1), the VLOOKUP function will perform an approximate match.
  • Importantly , range_lookupthis is optional and defaults to TRUE; however, for an accurate approximate search, the first column in the database table must be sorted alphabetically (A-Z).

 

The formula for the 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: This is the function name.
• The parameters in bold are required.
• lookup_value: The value used for lookup
• table_array: The table containing the values ​​to be searched, in absolute form with a $ sign in front, for example: $A$3:$E$40.
• col_index_num: The column number containing the lookup value in table_array. For example, in table $A$3:$E$40, column B contains the lookup value, so col_index_num here will be 2; in table $C$3:$F$40, column E contains the lookup value, so col_index_num here will be 3.
• range_lookup: This is the search range. TRUE is equivalent to 1 (relative lookup), FALSE is equivalent to 0 (absolute lookup). This parameter is not always required in the formula.

Relative lookups can only be applied when the values ​​to be searched in the table_array are already sorted (ascending, descending, or alphabetically). For such tables, you can use relative lookups, similar to using an infinite IF function . For values ​​that cannot be sorted or have not yet been sorted, use absolute lookups to find the exact value.

Video tutorial on using the VLOOKUP function.

 

Examples illustrating the VLOOKUP function

Example 1: The VLOOKUP function to evaluate and rank students and employees.

Let's say you have a student report card like this:

No.	Full name	Average score	Ranking
1	Nguyen Hoang Anh	9.1
2	Tran Van Anh	8.3
3	Nguyen Quang Vinh	9.5
4	Tran Hong Quang	8.6
5	Do Thanh Hoa	5.0
6	Le Hong Ngoc	4.5
7	Doan Thanh Van	7.2
8	Ngo Ngoc Bich	0.0
9	Hoang Thu Thao	6.6
10	Dinh Minh Duc	8.7

And the ranking system is as follows:

Ranking regulations
0	Weak
5.5	Medium
7	Rather
8.5	Good

Now we will use the VLOOKUP function to input the grades for the students. Notice that the grading table is sorted from lowest to highest (from weakest to best), so in this case we can use a relative lookup.

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, and the column containing the search value is number 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 wish (or because the ranking table is not yet sorted) 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 will appear in the bottom right corner. Click on it and drag it down the entire table to copy the ranking formula for the remaining students.

Then, using the VLOOKUP function, we get the following result to classify students' academic performance:

 

Are you wondering why we use a $ before C6? The $ helps to fix column C, only changing the rows when you drag the formula down to the entire table. And $A$18:$B$21 helps to fix the ranking table, preventing it from changing when you drag the formula.

Example 2: The VLOOKUP function performs an absolute lookup to retrieve data.

Let's say you have a table of employees storing employee IDs, full names, and job titles. Another table stores employee IDs, hometowns, and educational qualifications. Now, if you want to fill in the hometown and educational qualifications for each employee, how would you do that?

Let's say you have a table of employees and their hometowns as follows:

Now you want to fill in the employee's hometown information. In cell D4, enter the following absolute lookup formula: =Vlookup($A4,$A$16:$C$25,2,0)

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

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

Continue pressing Enter and scrolling down to copy the formula for the remaining employees, and you will get the following result:

Example 3: Using VLOOKUP to extract data

Continuing with the dataset from example 2, we will now find the hometowns of three employees: Nguyen Hoang Anh, Tran Van Anh, and Nguyen Quang Vinh. I have extracted them into a new table F15:G18.

I will perform this lookup on table A3:E13, after filling in the place of origin and educational level. Then, enter the following absolute lookup formula into cell G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > press Enter.

Copying the formula for the remaining two employees gives us the following result:

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

Example 4: Using the VLOOKUP function on two different Excel sheets.

Returning to the dataset in example 2, after the employees have been filled in with their qualifications and hometown, we name the sheet QTM.

In another sheet of the spreadsheet, named QTM1, you need to retrieve information about the employee's qualifications and job title, with the order of employees changed. This is where you see the true power of the VLOOKUP function.

To look up the "Skill Level" data for an employee, 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 values ​​used for searching.
• QTM! is the name of the sheet containing the table with the values ​​to search for. Add an exclamation mark (!) after the sheet name.
• $B$3:$E$13 is the table containing the lookup values ​​and the sheet containing the table (QTM).
• 4 is the column number for "Level," starting from the "Full Name" column on the QTM sheet.
• 0 is an absolute lookup.

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

To look up the "Job Title" data of an employee, in cell D4 of sheet QTM1, enter the formula: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), then press Enter.

Copying the formula for the remaining employees, we get the following:

How to use the VLOOKUP function in Excel with Copilot

You can use Copilot, Microsoft's built-in AI assistant, to build VLOOKUP formulas for you.

Before you get too excited, remember these things:

• You can only use this feature if you have a Copilot Pro subscription or a Microsoft 365 Personal or Family subscription that includes Copilot.
• Copilot only works with Excel files stored on OneDrive or SharePoint that have AutoSave enabled.
• Make sure to format your data as a table (Copilot doesn't work with regular ranges).

Now, let's begin.

1. With the workbook open, click Copilot in the toolbar to open a new conversation. Or click any cell and select the Copilot icon that appears next to it.
2. Describe what you need in the message bar. For example: "Please write a VLOOKUP formula to retrieve email addresses from Table A to Table B." The more specific your request to the AI ​​assistant, the better.
3. Copilot will generate a recipe, along with a preview of the result, and display it in the chat window.
4. Optionally, you can ask Copilot to adjust the formula until it's exactly what you need.
5. Hover over Insert column below the results preview to see the results directly in your spreadsheet. If it looks good, click Insert column . Or, you can copy and paste the formula.

It's easy!

What is the VLOOKUP function used for?

First, we need to find the correct answer to the question "what do we use the VLOOKUP function for?".

The VLOOKUP function is used to help retrieve information within a data field or list based on available identifiers.

For example, inserting a VLOOKUP function along with a product code into another spreadsheet will display the information of the product corresponding to that code. This information could include a description, price, or inventory quantity, depending on the formula you write.

The smaller the amount of information to be searched, the more difficult it becomes to write a VLOOKUP function. Typically, you would use this function in a reusable spreadsheet like this example. Each time you enter the appropriate product code, the system will retrieve all the necessary information about that 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_lookupomitted, the VLOOKUP function will allow an exact match, but will still use an exact match if one exists.
• The biggest limitation of this function is that it always retrieves values ​​from the right. 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, the VLOOKUP function will only match the first value.
• The function is not case-sensitive.
• Suppose there is an existing VLOOKUP formula in a spreadsheet. In that case, the formulas might break if you insert a column into the table, because the column index values ​​are hardcoded and do not automatically change when columns are inserted or deleted.
• VLOOKUP allows the use of wildcards, for example, asterisks (*) or question marks (?).
• Suppose the table you're working with contains numbers entered as text. If you're only retrieving the numbers as text from one column in the table, that's fine. But if the first column of the table contains numbers entered as text, the #N/A! error will be displayed if the lookup value is also not text.
• The #N/A! error occurs if the VLOOKUP function does not 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 did 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 either a logical value of TRUE or FALSE.
• How to use the IF function in Excel
• How to use the AVERAGEIF function in Excel

Common mistakes when using the VLOOKUP function

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

The lookup value is in the wrong column.

One of the most common errors you'll encounter when using VLOOKUP formulas is that the function returns only the #N/A value. This error occurs when it cannot find the lookup value you requested VLOOKUP to search for.

In some cases, you can use the XLOOKUP function instead of VLOOKUP; however, it's easier to fix this error with the VLOOKUP function.

How to fix the problem:

The reason VLOOKUP might not find the desired value could be that the value is not in the table. However, if it returns all #N/A values, and the lookup value is text, it's most likely because it's read-only.

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:

Incorrect number format

When dealing with numbers as lookup values, it can be very frustrating trying to find errors. If you encounter an error, the first thing to check is the number format.

Formatting issues can occur when importing data from external databases or copying data from external sources. To determine if your data is readable as text, check for left-aligned characters or error symbols. When formatted correctly, numbers will display right-aligned.

How to fix the error:

If the numbers in the table are displayed as text, you will need to convert them to numbers. The most efficient way to do this is to use an Error Indicator.

1. Select the column containing the lookup value. A quick way to select all data is to click the first cell in the desired column. Next, while holding down the CTRL key, click the Down Arrow key.
2. After selecting all the data in the table column, click on Error Indicator and choose Convert to Number.

Overall, VLOOKUP is a great way to quickly retrieve data in spreadsheets. While VLOOKUP queries are vertical across columns and have some other limitations, Microsoft continuously updates Excel's lookup features to expand their applications. For example, HLOOKUP, XLOOKUP, etc., can help you look up data in various ways. QuanTriMang.com will continue to provide you with information on how to use this function in subsequent articles.