How to use Excel VLOOKUP Function with Examples: A Practical Guide with downloadable Files

 Introduction:

        Excel, the mostly used spreadsheet software, offers a plethora of powerful excel formulae / functions that enable users to manipulate and analyze data efficiently. Among these functions, VLOOKUP stands out as a versatile tool for retrieving information from a database. Whether you're a student, professional, or data enthusiast, mastering Excel VLOOKUP function can significantly enhance your data analysis capabilities. Today, you will explore the VLOOKUP function in depth, providing practical examples with relevant downloadable Excel sheet to demonstrate its versatility and usefulness.

Understanding the VLOOKUP Function:

    The VLOOKUP function in Excel allows you to search for a specific value in a table and retrieve information from a corresponding column in the same row. Its syntax is as follows:

=VLOOKUP(what you wish to search for, what is the data i.e. select table array, column number that contains the value you wish to be taken, return Exact or Approximate match, 0=False, 1=True)

Now, let's break down each component of the VLOOKUP function:

1.  What you wish to search for? This is the value you want to find in the leftmost column of the table. It can be a cell reference or a constant value.

2.  What is the data i.e. select table array: This parameter refers to the range of cells that contain the table you want to search. The leftmost column of this range should contain the lookup value.

3.  Column number that contains the value you wish to be taken: The column number in the table from which you want to retrieve the result. The first column is 1, the second is 2, and so on.

4.  Exact or Approximate Match: This parameter is optional and can take either TRUE or FALSE. When set to TRUE (or omitted), VLOOKUP performs an approximate match. When set to FALSE, it performs an exact match. Will discuss about this at the end of this article

EXAMPLE 1:    

Let's say you have a table containing Student names, marks and corresponding Grade letters. You need to determine the Grade letter for a student name “Carmen Cooper” in Cell A2 (same data has been assigned to cell G8). The step by step procedure for applying vlookup function shall be as follow:

1.      Create a table as shown below

2.  In a separate cell, enter the formula: =VLOOKUP(A2 or G8,A2:C21,3,0)

3.  Cell A2 or Cell G8 is the Look value i.e. student name “Carmen Cooper“

4.   To make calculations fast the same name “Carmen Cooper” has been assigned to cell G8 by just equating Cell G8 to Cell A2 (i.e. type “=A2” in Cell G8 without “”)

5.  A2:C21 is Data or Table Array

6.  3 is Column number that contains the Grade Letters for each student

7.  0 enables an exact match.

8.  The output of this function will display the Grade Letter “D” corresponding to the score of 45.

Download Excel File for Example 1

Example 2:

Let’s consider a scenario where you have a list of employees with their IDs, names, salaries and expected rise in salaries in an Excel sheet. You need to find the salary and expected rise in salary of a specific employee with the ID "EX-003."

1.  Create a table with employee information, including columns for ID, Name, Salary and expected rise in Salary.

2.  In a separate cell (i.e. L13), enter the formula: =VLOOKUP("EX-003", E5:H24, 3, 0).

3.  In this example, "EX-003" is the lookup value, E5:H24 represents the table range

4.  “3” indicates that the salary is in the third column, and 0 ensures an exact match. The result will display the salary of the employee having ID "EX-001." 

5.  To find the expected rise in salary for employee’s ID EX-003, enter the following formula in separate cell (i.e. L15) = VLOOKUP("EX-003", E5:G24, 4,0)

TIP:  Instead of manually entering EX=003 for each formula, automate the calculations by using the Cell number that contains EX-003. In this sheet that cell number is L11.

Download Excel File for Example 2

Conclusion

The VLOOKUP function is a valuable tool for searching and retrieving information from tables in Excel. Its versatility allows users to handle a variety of scenarios, from simple searches to more complex data analysis tasks. By understanding the syntax and applying practical examples, you can use the power of VLOOKUP to streamline your data analysis process.

When to use Exact Match and Approximate Match

In vlookup there are two scenarios i.e. Exact Match and Approximate Match.

i.        Exact Match

a.  If you enable exact match by putting range_lookup 0 or False, then Vlookup will search for an exact match in first column of table_array and will return the corresponding value from the specified col_index_num

b.  For exact match the first column must be sorted in ascending order to get correct value.

c.   Unique Identifiers: When you have a data with unique identifiers, such as students IDs, customer IDs or product identification codes, you can use an exact match to find specific records and retrieve relevant information. Exact Match ensures that accurate results have been obtained without any ambiguity.

d.  Data with no-numeric values: If your lookup data table contains non-numeric values, such as name titles, classifications, or labels, an exact match ensures that you find the exact match for the specific value you're looking up. This is particularly important when dealing with categorical data where approximate matches may not be meaningful.

e.  Data validation and verification: Exact matches are commonly used in data validation. For example, you can use VLOOKUP with an exact match to validate user input against a predefined list of valid values. This helps ensure data accuracy and consistency.

f.      Lookup tables with multiple matches: If your lookup table contains duplicate values in the first column, an exact match ensures that you retrieve the exact matching value you're interested in. It prevents the VLOOKUP function from returning the first occurrence of a value when there are multiple matches

If the first column in exact match is not sorted you may get unexpected results or incorrect matches.

Conclusion:

In short, an exact match in VLOOKUP is beneficial when you need to find a precise match based on unique identifiers, work with non-numeric data, perform data validation, or deal with lookup tables containing duplicate values. It ensures accuracy and avoids potential errors or confusion that can occur with approximate matches.

ii.      Approximate Match

a.                 The approximate match in the VLOOKUP function is useful when you want to find an approximate value that falls within a range or when you're working with data that is sorted in ascending order. Here are some scenarios where an approximate match in VLOOKUP is commonly used:

b.                 Finding the nearest value: If you have a sorted list of values and you want to find the closest value that is less than or equal to a specific lookup value, an approximate match is appropriate. For example, if you have a table with product prices and you want to find the price for a given quantity, but the exact quantity is not available, you can use an approximate match to retrieve the price for the closest available quantity

c.                  Working with ranges or bins: When dealing with data that is divided into ranges or bins, an approximate match can be helpful. For instance, if you have a table with salary ranges and corresponding tax rates, you can use an approximate match to determine the tax rate for a specific salary amount falling within a range.

d.                 Efficient searching in large datasets: Approximate matches are generally faster than exact matches, especially when working with large datasets. By using an approximate match, Excel can quickly locate the approximate position of the value in the first column of the table, which can significantly improve calculation speed.

e.                 It's important to note that when using an approximate match, the first column of the table must be sorted in ascending order for accurate results. If the data is not sorted, the VLOOKUP function may return unexpected or incorrect results.

Conclusion:

An approximate match in VLOOKUP is beneficial when you need to find the nearest match, work with ranges or bins, or improve the performance of calculations in large datasets.

Things to remember while using VLOOKUP Function

a.  The VLOOKUP function can only search for values in the first column of the table. If you need to search for values in other columns, you can use the HLOOKUP function instead.

b.  The VLOOKUP function can only return a single value. If you need to return multiple values, you can use the INDEX and MATCH functions together.

c.   The VLOOKUP function can be used with any type of data, including text, numbers, and dates.