Using VLOOKUP Function in Excel, Syntax, and Clear Illustrative Examples

 NOTE: Download 70+ Basic and Advanced Excel Exercises Now

This approach helps you:
- Apply the VLOOKUP function in your tasks
- Combine VLOOKUP with other Excel functions
- Resolve issues with the VLOOKUP function if they arise

The VLOOKUP function in Excel is a column-based value search function that returns results in a vertical manner. This Excel function is widely used and convenient for individuals utilizing Excel for data analysis and column-specific data retrieval. In the realm of Excel learning tips, Mytour will present to you 2 fundamental methods of utilizing this function.

Example of Vlookup function, Vlookup function exercises in Excel.

How to use the Vlookup function in Excel

I. What is the Vlookup function?

The Vlookup function is one of the basic functions commonly used in Excel, allowing users to search for data based on columns. The pre-programmed Vlookup function assists users in quickly finding and retrieving information. Mastering the Vlookup function enhances your ability to manipulate data efficiently, saving time.

II. Syntax of the Vlookup function

 Note the function syntax:

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

Where:

III. Examples of VLOOKUP in Excel

1. How to do a relative search:

Example: Based on the grade classification table corresponding to the given scores, classify the academic performance for the students listed below:

Example of VLOOKUP function in Excel

Using the formula in cell D6: =VLOOKUP(C6,$C$16:$D$20,2,1)
Result obtained:

Result when using vlookup function

2. How to do an exact search

For an exact search, you will find more detailed information compared to a relative search.
Example: Fill in the Hometown and Education level of employees in the table based on the given employee codes below.

Example of an exact search using the vlookup function

To fill in the employee's hometown, use the Vlookup formula for cell E6 as follows: =VLOOKUP(A6,$D$12:$F$17,2,0)
A6 is the value to be searched
$D$12:$F$17 is the lookup table
2 : column index number on the lookup table
0 : Exact match lookup type

Similarly, to fill in the employee's education level, follow these steps:

For cell F6 with the formula: =VLOOKUP(A6,$D$12:$F$17,3,0)

Result using the Vlookup function for exact value search

3. Relative and Absolute Values

With absolute values, you will find the most accurate results. Absolute values will not change their address even if they appear in different columns or cells. Therefore, we use absolute values when we need to find the most accurate information and use relative values when searching for results that are accurate or close enough. For more information, you can refer to articles on referencing functions in Excel.

IV. Combining Vlookup with Hlookup, Left, Right, Match

In the previous content, Mytour provided a basic understanding of the Vlookup function. To help you better understand and apply it in real situations, we will now present a specific exercise. This problem involves the combination of the Vlookup function with other Excel functions such as Hlookup, Left, Right, and Match.

Consider the following data with the following conventions:
- Table 1: RICE CONSUMPTION STATISTICS
- Table 2: TABLE OF NAMES, MANUFACTURERS, AND PRICES

Requirements to be addressed:

Question 1: Based on the first 2 characters on the left and 2 characters on the right of the Product Code in Table 1, find and fill in the values for the Product Name - Manufacturer Name column (Compute data for column C5:C10).
E.g., KD-NĐ means Khang Dân - Nam Định rice.
Question 2: Fill in the Unit Price for each item based on the Product Code in Table 1 and lookup in Table 2. (Compute data for column D5:D10).
Question 3: Calculate the total cost = Quantity * Unit Price

Answers:

Question 1:
- Explanation: We need to derive a formula to fetch data for Product Name and Province Name, then combine these two formulas to get the answer for Question 1.

 + Fetching Product Name: Retrieve the first 2 characters in the Product Code column in Table 1 (A5:A10) compared with the first 2 characters in the Product Code column in Table 2, and extend the data range to the entire Product Name column in Table 2 (A15:B20) or (A15:E20).
 >> Use Vlookup Function: In a cell without data, enter the following formula: =VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)
After entering, press Enter. If it shows 'Khang Dân,' you have completed 40% of the answer to Question 1, simple, isn't it? Let's continue.

+ Get Province Name: Extract the rightmost 2 characters in the Product Code column (A5:A10) in Table 1 and compare them with the last 2 characters in the 'Product Code - Province Name' column in Table 2, the data range being (A16:E20).
 >> Since we are fetching data row-wise, we use the Hlookup function. In a cell without data, try entering the following formula:

= HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)
After entering, press Enter. If it shows 'Nam Định,' you have completed an additional 40% of the answer to Question 1. Now you can envision the answer. The next step is to combine these two functions to compute data for the Product Name - Province Name column.

+ Combine Two Formulas: There are various formulas to concatenate or join strings. In this problem, we guide you on how to concatenate using the & function. Specifically, we use a hyphen (with a space) to separate the two computed formulas, i.e., Product Name and Province Name, as follows: ' - '
 In general, the formula will look like this =+vlookup&' - '&hlookup (Note: No equals sign (=) at the beginning of the Hlookup function)
 Then, at C5, enter the formula:
 C5=+VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)&' - '&HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)
 Press Enter to see the result. If the answer is correct, it should display 'Khang Dân - Nam Định'
 If the result is okay, for the next cell, C6:C10, you just need to hover over the edge of the formula in cell C5 and drag it down to C10. That's it!
 Similarly,

C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&' - '&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE)
This completes Question 1 of the problem statement. Let's move on to solve Question 2.

Question 2: Calculate Unit Price
The formula will be as follows: In cell D5, enter the formula
D5=+VLOOKUP(LEFT(A5,2),$A$16:$E$20,MATCH(RIGHT(A5,2),$A$16:$E$16,0),FALSE)

Question 3: Calculate Total Amount
Oh, this question often appears in data computation problems such as salary tables, expenses, total amounts, etc. It's quite easy, isn't it?
The formula for calculating the total amount will be as follows: In cell F5, enter E5=+D5*E5

V. Common Errors When Using Vlookup

1. Error #N/A

Error #NA Definition: Error #NA is returned in an Excel formula when a suitable value is not found. When using the VLOOKUP function, we encounter the #NA error when the search condition is not found in the lookup array, specifically in the first column of the VLOOKUP function's lookup array.

Reference: How to Fix #NA Error in Excel

2. Error #REF!

Error #REF! occurs when the specified column is undefined, as illustrated in the example below. Col_index_num is 3, while Table_array is B2:C10, containing only 2 columns. Hence, the system will report a #REF! error.

Reference: How to Fix #REF! Error in Excel

3. Error #VALUE!

Error #VALUE! occurs when the Col_index_num is less than 1 in the formula. For example, in the scenario below, Col_index_num equals 0, resulting in the appearance of the #VALUE! error.

Reference: How to Fix #VALUE! Error in Excel

4. Error #NAME?

Error #NAME? appears when Lookup_value lacks double quotation marks ('') ('' is used to format text to help Excel understand the formula). In the example below, Cải xoăn lacks double quotes ('') causing Excel to misunderstand the formula. Fix the error by replacing Cải xoăn with 'Cải xoăn'.

Reference: How to Fix #NAME? Error in Excel

VI. Some considerations when using VLOOKUP function

1. Using Absolute References

Utilizing absolute references ensures that copying a formula from one column to another will not alter the formula.

In the example below, the formula in cell C13 is =VLOOKUP(B13,$B$2:$C$10,2,0). When copying the formula to cell C14, the Table_array will remain unchanged.

2. Avoid Storing Numeric Values as Text.

If numeric data in the data table is formatted as text, as shown in the example below in column A. When you enter the formula =VLOOKUP(A8$A$2:$B$5,2,0) in the revenue column, the data will return an #N/A error. To convert text data to numbers, select Home => Choose Wrap Text => Select Number.

3. Lookup table contains duplicate values

If your lookup table contains multiple duplicate values, the VLOOKUP function will return the first result it finds from top to bottom. In the example, the table returns the result for Apple as 97 instead of 23 below.

- Solution 1: If you want to remove duplicate values, you can highlight the lookup table and choose Data => Select Remove Duplicates

- Solution 2: Use a Pivot Table to filter the result list

With the two methods of relative and absolute Vlookup, the function helps you easily analyze data in Excel spreadsheets. Vlookup aids in summarizing details for filtering necessary lists, making your work more accurate and time-efficient. It's a function commonly used in spreadsheet operations.

To further your understanding of lookup functions, you may refer to the Hlookup function in Excel introduced by Mytour. The Hlookup function assists you in searching and referencing by column or row. When you use this function, it satisfies the specified conditions.

Besides the Vlookup function in Excel, you can explore the COUNTIF function. COUNTIF is a convenient counting function that provides accurate results based on specified conditions.

In the realm of computational functions in Excel, there's the versatile COUNTIF function. Another widely used function is SUMIF, a conditional summing function that allows you to calculate the sum of individual columns or rows without summing up all the data in the Excel file. It combines the functionalities of the IF and Sum functions, making it popular for Excel data usage.

When it comes to Excel, the MID function is indispensable. It's a fundamental Excel function that aids in extracting character strings from a given range of characters in an Excel spreadsheet. The MID function is extensively used in cases where you need to retrieve specific characters from long character strings, which can extend up to hundreds of characters.