Using VLOOKUP in Excel

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.

Table of Contents:

• What is VLOOKUP in Excel?
• Syntax of VLOOKUP in Excel
• How to Use VLOOKUP in Excel?
• VLOOKUP with Exact Match vs. Approximate Match
• Using VLOOKUP across Multiple Sheets
• Using VLOOKUP across Multiple Workbooks
• Advanced VLOOKUP Techniques
• Absolute references ($)
• Common Errors in VLOOKUP and How to Fix Them
• Real-World Applications of VLOOKUP in Excel

What is VLOOKUP in Excel?

VLOOKUP primarily stands for “Vertical Lookup,” an arbitrary Excel function useful to search for a specific value in the first column of a table and return any corresponding value from another column in the same row. This term has gained importance for fast data retrieval from large datasets thus becoming an integral part of data analysis and reporting.

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 for.
• table_array: Range of cells where the data is to be searched for.
• col_index_num: Column from where the 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 103from a table:

For Employee ID 103, use the following formula to determine the Employee Name:

=VLOOKUP(A4, A2:C6, 2, FALSE)

Output in Excel

How to Use VLOOKUP in Excel?

In MS Excel, the VLOOKUP function lets you find one value in a column and pull out matching data from another column in the same table. VLOOKUP is excellent for large data issues and for making the value easy to find.

Follow these few simple steps to work with VLOOKUP in Excel efficiently.

Step 1: Organize the data

Before you apply the VLOOKUP function, always ensure that your data is well organized in table structure, with the first column filled with lookup values and the rest of the columns containing the other related data for proper retrieval.

Step 2: Identifying the search value

Select a value to be searched using the VLOOKUP function, which must be in the first column of your table, and the corresponding data will be gathered from any other specified column in that same table.

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

Specify the match type for the VLOOKUP function according to your search requirements:

• Approximate Match – If there is no exact match discovered and you want the closest record instead, use TRUE.
• Exact Match – If you want to look for an exact value corresponding to your lookup value, use FALSE.

VLOOKUP with Exact Match vs. Approximate Match

The VLOOKUP function allows two match types that define how the search will take place. The type you want to use should be dictated by the data you are working with and your specific needs.

1. Exact Match(FALSE):

• It finds the exact value, which matches the lookup value.
• Returns an error in case there is no exact match. 
• Ideal for search of Unique IDs, names, or exact data points.

2. Approximate Match(TRUE):

• It finds the nearest match which is less than or equal to the lookup value.
• The first column needs to be sorted in ascending order to get accurate results.
• Most suited for: Searching grade ranges, tax brackets, or pricing tiers.

Excel Mastery Course by Intellipaat

Learn from Experts & Advance Your Career in Data Analytics

Explore Program

Using VLOOKUP across Multiple Sheets

Multiple sheets can be a mess to deal with while working in Excel, but not if one uses VLOOKUP for data retrieval from different sheets. VLOOKUP is one of the powerful tools in Excel that helps to enhance your workflow by enabling you to call for relevant data dynamically instead of painstakingly switching from one sheet to the other.

Before using VLOOKUP, ensure that:

• Looking up involves finding the right value from the target worksheet’s first column.
• The data table has a correct structure with a unique identifier in the first column.
• The column index number accurately defines the data that you wish to extract.

For example, this table contains the Employee Database:

Here we need to find the department of employee ID-102 using the formula =VLOOKUP(A3,A1:D4,3,FALSE).

Thus, the output will look like this:

Explanation:

• FALSE – Exact match.
• 102 – Lookup Value (Employee ID).
• A1:D4 – Range to be selected from.
• 3 – Column number (Department is in column 3).

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 a 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(A3, '[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.

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”

Formula:

=IF(VLOOKUP(A3, 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.

Absolute references ($)

One can actually use VLOOKUP in Excel in order to acquire data from another workbook provided that the filename, sheet, or range can be specified. One of the most common issues is when formulas are dragged across several cells: references will be altered on the go.

Thus, to prohibit that from happening, we apply the “$” in the reference to adjust the criteria.

Example scenario:

You have two workbooks

• SalesData.xlsx (Source Workbook)
• Report.xlsx (Destination Workbook)

Data in SalesData.xlsx (Sheet1):

Now, from Report.xlsx, you need to fetch the Sales Amount of a given Order ID.

Applying the formula =VLOOKUP(A2, ‘[SalesData.xlsx]Sheet1’!$A$2:$C$4, 3, FALSE) on Sheet2.

Understanding the Formula

• A2 – Lookup value (Order ID in this case)
• [SalesData.xlsx]Sheet1′!$A$2:$C$4 – Absolute reference ($) locks the table range.
• 3 – Column Index (Sales amount in column 3).
• FALSE – Exact match.

Output: If A2 = 1001, the formula returns 75,000 (Sales Amount for Order ID 1001).

Key Takeaways

• References should be aligned correctly to columns and sheets in the formulas.
• Make use of absolute references ($ symbol) to prevent range shifting.
• While setting up the formula, you may want to leave the external workbook open.

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 numeric.

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.
• 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.

INDEX and MATCH as an alternative to VLOOKUP

Although VLOOKUP is a brilliant function, it has several shortcomings that also include:

• It can run exclusively from left to right. That means the lookup column has to be the first column of the table.
• When a column is added or deleted, the column numbering index tends to shift, and this in turn creates problems.
• Fails to perform lookups in both ways (left and right).

To overcome these restrictions, Excel extends an advanced option set: INDEX and MATCH.

We can now forgo traditional VLOOKUP and utilize the INDEX-MATCH combo to perform lookups that can be automated dynamically.

The syntax:

=INDEX(return_range, MATCH(lookup_value, lookup_range, match_type))

• INDEX, in Excel, retrieves values from a specific row and column within a table.
• The MATCH function finds the position of the lookup value concerning a specified column or row.

Example:

Imagine you have a database table of employees, where Employee ID is in Column B, so the names are in Column A. If you wish to use VLOOKUP to look up the name based on the ID, you would face yet another obstacle thrown at you since VLOOKUP cannot look leftward.

Employee Data (A2:C5)

For example, if we would like to obtain the Employee Name (Column A) based on the Employee ID (Column B).

The formula used by INDEX and MATCH will be:

=INDEX(A2:A5, MATCH(B3, B2:B5, 0))

INDEX AND MATCH V/S VLOOKUP

Key Takeaways:

• INDEX + MATCH is a rather versatile and powerful lookup combination compared to VLOOKUP.
• Values can be searched from any direction (left or right).
• If you are working with a dynamic dataset where columns can change, INDEX + MATCH is more reliable.

You can truly speed up and make your lookup process in Excel even more dynamic and easier as you master the combination of INDEX and MATCH so that you can make your data analysis more effective.

VLOOKUP With MATCH in Excel

The video has demonstrated how one can improve the use of the VLOOKUP function in Excel with an added MATCH function for more dynamic referencing of columns and greater robustness and flexibility in data retrieval processes.

Step 1: Analyzing the Dataset

The dataset consists of a table with several columns, with the intent of looking for a given value in the first column and obtaining relevant information from the others.

Step 2: Limitations of VLOOKUP

According to traditional settings, the VLOOKUP function most of the time relies on a specific index number within the table, which again can get quite frustrating if the table structure undergoes any alteration.

Step 3: Overview of the MATCH Function

The MATCH function essentially returns the relative position of the item in the array, which can be utilized to make the dynamic column index for VLOOKUP.

Here we use =MATCH(“Department”, A1:D1,0) to find the column index dynamically.

Since Department is in the 3rd column, the result is returned as 3.

Step 4: VLOOKUP + MATCH

Instead of placing column numbers for VLOOKUP, you can determine column indexes based on the header name by wrapping the results obtained from MATCH.

Here we combine the MATCH function within VLOOKUP to find the department for Employee ID 103.

The formula =VLOOKUP(lookup_value, table_array, MATCH(column_name, header_range, 0), FALSE) is implemented in this scenario.

The output returned is HR since the formula finds the department for Employee ID 103.

With this function, you can locate a value and automatically obtain information from any column marked by its header name. You’ll eventually be more flexible in keeping track of everything.

Moreover, even if you modify some columns, the formula will still work (e.g., moving ‘Salary’ before ‘Department’) since the MATCH function sets the column number automatically.

Once you’ve learned this technique, you’ll be able to make Excel spreadsheets resilient and flexible, which becomes very handy in scenarios where the data changes frequently.

Real-World Applications of VLOOKUP in Excel

The VLOOKUP function in Excel has found greater acceptance within almost every industry for effective data retrieval and management. Here are some of its key applications:

• Tracking Sales Performance- The comparison of actual sales data with targets set by the company.
• Inventory Control – Fast retrieval of product information by unique product ID.
• Employee Record Lookup- Get employee details based on unique employee ID.
• Financial Analysis- Matching transaction records for audit and financial tracking purposes.
• Grading System- Assign student grades automatically depending on their marks.

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!

FAQs