How to Use VLOOKUP in Excel? VLOOKUP Formula

Excel is a very powerful tool for performing operations on data. In today’s world, every organization works on data, for retrieving insights from data it is important that the data is organized. Excel provides various functions to perform these operations on data. VLOOKUP is one of those functions. It is used for searching and retrieving information from a table of data.

In this article, you’ll learn about the VLOOKUP function, its uses, its working, common errors that you might encounter, and real-world applications of VLOOKUP.

What is VLOOKUP in Excel?

VLOOKUP stands for “Vertical Lookup”. It is a function in Excel that allows users to search for specific data in a table and return corresponding values from another column. This function searches for a value from the first column of a table and returns the value in the same row. VLOOKUP simplifies finding data from large datasets.

Syntax of VLOOKUP in Excel

The syntax of a VLOOKUP function is as follows:

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

Here,

lookup_value: Value that is to be searched
table_array: Range of cells where data is to be searched
col_index_num: Column from where data is to be fetched
range_lookup: This is an optional argument, which takes TRUE for approximate match and FALSE for exact match.

Example: To find the employee with ID 123 from a table:

=VLOOKUP(123, A1: D4, 2, FALSE)

How to Use VLOOKUP in Excel?

You can use the VLOOKUP function in Excel to fetch data from a table using a search value from another column. The following steps give you an idea of how to use a VLOOKUP function in Excel:

Step 1: Organize the data

In the first step, you need to make sure that the data you are going to work on is organized and in a table format.

Step 2: Identifying the search value

In this step, you need to identify the value on which you want to apply the VLOOKUP function. This value will be from the first column and will fetch the corresponding data from another column.

In the following image, “James” is used as the lookup value.

Step 3: Select the Table Range

Now you have to provide the range from which you need to retrieve data including the column with the lookup value. Here, in this image, you can see the table range is from B5 to D15.

Step 4: Specify the Column Index Number

The column index number specifies the column from which you wish to fetch the data. For instance, in the following image, the salary information that is to be extracted is in the third column. Hence, the column index number will be 3.

Step 5: Specify the Match Type

You need to specify the type of matching by the VLOOKUP function. If you want to look for an exact match, you have to set the match type as FALSE, and if you want to look for an approximate match then use TRUE.

VLOOKUP with Exact Match vs. Approximate Match

VLOOKUP works with two matching types, which determine the search pattern of the function. It depends on the data and the type of requirements that which match type is to be used. Following are the types of matching in VLOOKUP:

Exact Match(FALSE): If you want the VLOOKUP function to return the exact match of the lookup value, then the match type is to be set to FALSE. For example, if you want to get an employee salary based on the unique employee ID.
Approximate Match(TRUE): If you want the return value to be approximately equal to the lookup value, then the match type is to be set to TRUE. For example, if you want to get a salary range for a set of employees based on their department.

Using VLOOKUP across Multiple Sheets

If you want to access a particular set of data from another sheet, then you can use the VLOOKUP function with the sheet name specified in the formula.

Example: In the image below, we have selected the table range from Sheet 1, while the lookup formula is set on Sheet 2.

Using VLOOKUP across Multiple Workbooks

Similar to the above method where we have used VLOOKUP to extract data from another sheet, we can also use table range from another workbook. To do this, you need to specify the workbook name and the sheet name from that workbook in the VLOOKUP formula.

Example:

=VLOOKUP(102, ‘[Workbook3.xlsx]Sheet2’ !B5:D12, 2, FALSE)

For this formula to work, make sure that the external workbook is available for the formula to work.

Advanced VLOOKUP Techniques

VLOOKUP can be used for various advanced functions like nesting VLOOKUP functions and combining VLOOKUP with IF Statements.

Nested VLOOKUP in Excel

A Nested VLOOKUP combines two or more VLOOKUP functions. This method is used when you need to perform multiple VLOOKUP operations simultaneously to retrieve data.

Example:

Let there be two tables:

Table 1 contains the Product ID and Product Category
Table 2 contains the Product Category and Product Name.

Table 1		Table 2
Product ID	Product Category	Product Name
Food101	Biscuits	Parle
Food102	Cake	Britannia
Food103	Snacks	Peanuts

To find the Product Name using Product ID:

=VLOOKUP(VLOOKUP("Food101", Table1!A:B, 2, FALSE), Table2!A:C, 3, FALSE)

Here,

The first VLOOKUP finds the Product Category from the Product ID from Table 1.
The second VLOOKUP uses the Product Category to find the Product Name from Table 2.

2. Combining VLOOKUP with IF Statements

If you want to add conditional logic to your lookups, then you can use IF statements with VLOOKUP.

Example:

You have Employee data with Employee ID and Salary. If the salary is higher than 100000, then label them as “Senior Employee”, otherwise, “Junior Employee”

Employee ID	Salary
101	40000
102	105000
103	152000

Formula:

=IF(VLOOKUP(102, A2:B4, 2, FALSE) > 100000, “Senior Employee”, “Junior Employee”)

Here,

The VLOOKUP returns the salary for Employee ID 101.
The IF function checks if the salary is greater than 100000 and assigns the appropriate label.

Common Errors in VLOOKUP and How to Fix Them

Sometimes you might encounter errors in the VLOOKUP function, which may disrupt the lookup process. You must understand these errors and know how to fix them so that you can save time and ensure accuracy. Below are some errors and how to fix them:

1. #N/A Error

This error occurs when the VLOOKUP function is unable to find the lookup value in the given range.

Solution:

You need to double-check the lookup value and make sure there are no unnecessary spaces.
Make sure the lookup value is from the first column of the lookup range.

2. #REF! Error

This error occurs when the column index number specified in the formula exceeds the number of columns in the range.

Solution:

Make sure the lookup range includes all the required columns.
Check the column index number, it should match the range.

3. #VALUE! Error

This error occurs when the column index value specified in the formula is not a numeric value.

Solution:

Check if the column index number mentioned in the formula is a valid numerical value.

4. Missing Data in Columns

VLOOKUP function returns a blank or an unexpected result if the data is missing in the specified column.

Solution:

Check if the data in the lookup range is accurate and complete.

5. How to resolve Range issues using VLOOKUP

A common problem arises when users encounter the ‘#N/A’ error while attempting to apply the same formula throughout multiple cells in Excel by dropping down the cursor. What happens is when you drop down the cursor, the range also changes if you had F1, A1:B50 in the formula, when you move it to the 5th cell, it becomes F5, A5:B55. Let’s tackle this problem one step at a time to find a solution.

Step 1 – In attempting to extend a formula across multiple cells, you may encounter the #N/A error, indicating a change in the formula range as observed in this instance.

Step 2 – When you’re extending the formula downwards, the range values decrease with each cell. To keep the range locked for the formula, simply add a ‘$’, as shown in the image below.

Step 3 – At this point, the range remains fixed while only the lockup value undergoes changes. This adjustment ensures there won’t be any issue with ranges in Excel and you will get the precise data.

Real-World Applications of VLOOKUP in Excel

VLOOKUP in Excel has a wide range of real-world applications. Some of these applications are:

It helps in inventory management by searching and retrieving product details using unique product IDs.
It helps search for employee records using unique employee IDs.
It helps in matching transaction records for financial analysis.
VLOOKUP can be used to assign grades to students based on their marks.
It helps in comparing the sales data of an organization with its targets.

Conclusion

By now you must have understood the concept and uses of the VLOOKUP function in Excel. It is used in Excel for data searching and retrieval of the required values. If you’re working with spreadsheets and workbooks, VLOOKUP will be your go-to method for efficiently accessing the required data.VLOOKUP helps you master the advanced techniques that you can use to handle data analysis tasks with ease. If you’re eager to become an Excel expert and take your skills to the next level, join our Advanced Microsoft Excel course today!