How to use VLOOKUP in MS Excel?

 

VLOOKUP Introduction

Microsoft Excel is an incredibly versatile tool used for a variety of data management tasks. Among its many functions, VLOOKUP (Vertical Lookup) stands out as a powerful feature for searching and retrieving data from a table. Whether you’re managing a business inventory, analysing sales data, or creating detailed reports, VLOOKUP can significantly streamline your workflow by allowing you to quickly find specific information within large datasets.

This blog post will delve into the intricacies of the VLOOKUP function, including its syntax, practical applications, and a detailed example to help you master this essential Excel tool.

 

What is VLOOKUP?

The VLOOKUP function is designed to search for a value in the first column of a specified range (table_array) and return a corresponding value from another column in the same row. This makes it ideal for looking up data in vertical lists or tables.

 

VLOOKUP Syntax

The basic syntax of the VLOOKUP function is:

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

• lookup_value: This is the value you want to search for in the first column of your table. It can be a specific number, text, or a cell reference.
• table_array: This is the range of cells that contains the data you want to search. It includes the column where the lookup_value is located and the column from which you want to retrieve data.
• col_index_num: This represents the column number in the table_array from which to retrieve the value. The first column in the range is 1, the second is 2, and so on.
• [range_lookup]: This optional argument specifies whether you want an exact match or an approximate match. Use FALSE for an exact match and TRUE for an approximate match (default).

 

How VLOOKUP Works

1.    It searches for the lookup_value in the first column of the table_array.

2.    It finds the row where the lookup_value is located.

3.    It retrieves the value from the column specified by col_index_num in the same row.

 

Example: Employee Data Lookup

To illustrate how VLOOKUP works, let’s use a practical example involving employee data. Imagine you have a table with employee details, and you want to find specific information based on an employee ID.

 

Sample Data

Consider the following table of employees:

 

Goal

Suppose you want to find the Salary & Branch of the employee with Employee ID 201002.

Step-by-Step Solution

1.    Organize Your Data

Ensure your data is organized in a table format, with the Employee ID in the first column, followed by other details. For this example, let’s assume your data is in cells A1:I19.

2.    Enter the VLOOKUP Formula

To find the Salary for Employee ID 201002, use the following formula in another cell, say K2:

=VLOOKUP(J2, B:I, 8, FALSE) <select the whole column>

Here’s a breakdown of the formula:

o   J2 is where you enter the Employee ID you want to look up.

o   B:I is the range of the table_array containing the employee data.

o   8 specifies that we want to retrieve data from the 8th column of the table_array, which is the Salary column.

o   FALSE indicates that we want an exact match.

 

3.    Enter the Lookup Value

Type 201002 in cell J2. The formula in K2 will now search for this ID in the Employee ID column and return the corresponding Salary, which is 25000.

 

4.    View the Result

Cell k2 should now display 25000 showing the Salary of Employee ID 201002.

 

5.    Now try to fetch Branch with same method.

 

Advanced Tips for Using VLOOKUP

Handling Errors

If VLOOKUP cannot find a match, it returns the #N/A error. To handle this gracefully, use the IFERROR function to display a custom message or an alternative result:

=IFERROR(VLOOKUP(J2,B:I,8,FALSE), "Invalid ID")

This formula will display "Not Found" instead of #N/A if the lookup value does not exist.

 

Using Named Ranges

For better readability and management, you can use named ranges in your VLOOKUP formulas. To define a named range:

1.    Select the range of cells (e.g., B:I).

2.    Go to the Formulas tab and click Define Name.

3.    Enter a name for the range (e.g., empData) and click OK.

You can then use the named range in your VLOOKUP formula:

==IFERROR(VLOOKUP(J2,empData,8,FALSE), "Invalid ID")

 

Approximate Matches

If you’re working with numerical data and you want to find an approximate match, use TRUE for the [range_lookup] argument. Ensure your data is sorted in ascending order for accurate results. For example:

==IFERROR(VLOOKUP(J2,empData,8,TRUE), "Invalid ID")

This will return the closest match if an exact match is not found.

 

VLOOKUP vs. Other Lookup Functions

While VLOOKUP is powerful, other Excel functions like HLOOKUP (Horizontal Lookup), INDEX, and MATCH can offer more flexibility. For instance, INDEX and MATCH can perform lookups in any direction (not just vertically), which can be useful for more complex data structures.

 

Common Pitfalls to Avoid

1.    Data Sorting: Ensure that if you use approximate matches, your data is sorted correctly. If not sorted, the result may be incorrect.

2.    Column Index Out of Range: Ensure that the col_index_num is within the bounds of the table_array. If the column index exceeds the number of columns in the range, VLOOKUP will return an error.

3.    Lookup Value Not Found: Be cautious with the lookup_value. Ensure it exists in the first column of your table_array and that the data type matches (text vs. number).

 

Final Remarks

The VLOOKUP function is a fundamental tool in Excel that can save you time and effort by enabling quick and efficient data retrieval. By understanding its syntax, applications, and potential pitfalls, you can harness its full potential to streamline your data management tasks. Experiment with different datasets and scenarios to become more proficient in using VLOOKUP. As you gain more experience, you’ll find even more ways to leverage this powerful function in your Excel workflows.