Lesson#250: Excel VLOOKUP Functions: Finding Data Matches

In the world of Microsoft Excel, if there’s one function that stands out as the go-to tool for data lookup, it’s the VLOOKUP function. Mastering Excel’s VLOOKUP function is like having a superpower that lets you quickly find and retrieve specific data from a table. In this detailed guide, we will explore the magic of VLOOKUP, using the keyword “Excel VLOOKUP Functions” to highlight its significance. We’ll walk through a step-by-step tutorial and illustrate its power with more than 15 practical examples, all drawn from a simple example table.

Unleashing the VLOOKUP Function

At its core, the VLOOKUP function is designed to search for a specific value (the lookup value) in the first column of a table (known as the lookup table). Once it finds a match, it can retrieve data from the same row in a specified column within that table. Whether you need to fetch product prices, customer information, or any other data associated with a unique identifier, VLOOKUP is your trusty sidekick.

VLOOKUP Function Syntax:

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

• lookup_value: The value you want to find in the first column of the table.
• table_array: The range that contains your lookup table.
• col_index_num: The column number from which you want to retrieve data.
• [range_lookup] (optional): A logical value (TRUE or FALSE) that indicates whether you want an approximate match (TRUE) or an exact match (FALSE).

Now, let’s dive into practical examples using a simple example table.

The Example Table

Let’s start with a straightforward example table:

Product Code	Product Name	Price ($)
P001	Apple	1.00
P002	Banana	0.75
P003	Orange	1.25
P004	Mango	1.50
P005	Strawberry	2.00
P006	Pineapple	2.25

Step-by-Step Tutorial

Example 1: Basic VLOOKUP

Let’s say you want to find the price of a product with the code “P003.”

Using VLOOKUP:

=VLOOKUP("P003", A2:C7, 3, FALSE)

Here:

• "P003" is the lookup value.
• A2:C7 is the table array.
• 3 specifies that we want to retrieve data from the third column (Price).
• FALSE ensures an exact match.

The formula returns 1.25, which is the price of the product with the code “P003.”

Example 2: Approximate Match

Suppose you have a table of grades and corresponding score boundaries, and you want to find the grade for a score of 85.

Using VLOOKUP:

=VLOOKUP(85, F2:G6, 2, TRUE)

In this case:

• 85 is the lookup value.
• F2:G6 is the table array.
• 2 indicates the second column (Grade).
• TRUE signifies an approximate match.

The formula returns “B” because 85 falls between 80 and 89, which corresponds to a grade of “B.”

Example 3: Handling Errors

If the lookup value is not found, Excel returns #N/A. You can use the IFERROR function to display a custom message.

Using VLOOKUP with IFERROR:

=IFERROR(VLOOKUP("P007", A2:C7, 2, FALSE), "Product not found")

This formula displays “Product not found” if “P007” is not in the table.

Example 4: Lookup Value from a Cell

You often have the lookup value in a cell. This makes your formula dynamic.

Using VLOOKUP with Cell Reference:

=VLOOKUP(D2, A2:C7, 3, FALSE)

In this case, the lookup value is in cell D2, and the formula retrieves the corresponding price.

Example 5: Using Wildcards

Suppose your table contains product codes with variations, and you want to find the price of any product starting with “P00.”

Using VLOOKUP with Wildcards:

=VLOOKUP("P00*", A2:C7, 3, FALSE)

Adding the asterisk (*) wildcard lets you find any product code starting with “P00.”

Example 6: Case-Insensitive Lookup

If your data isn’t case-sensitive, you can make your lookup case-insensitive using the LOWER function.

Using VLOOKUP with Case-Insensitive Lookup:

=VLOOKUP(LOWER("p002"), LOWER(A2:A7), 2, FALSE)

In this example, we convert both the lookup value and the first column of the table to lowercase to ensure a case-insensitive match.

Example 7: VLOOKUP with Dropdown Lists

Dropdown lists can simplify data entry. You can use VLOOKUP to fetch data based on the selected value from a dropdown.

Using VLOOKUP with Dropdown:

1. Create a dropdown list with product codes.
2. Use VLOOKUP to fetch details based on the selected code.

Example 8: Retrieving Data from Different Sheets

Your data might be spread across different sheets within the same workbook. VLOOKUP can effortlessly reference this data.

Using VLOOKUP with Data from Another Sheet:

=VLOOKUP("P006", 'Sheet2'!A2:C7, 3, FALSE)

In this example, we’re fetching data from ‘Sheet2’ using VLOOKUP.

Example 9: Handling Duplicates

If your lookup column contains duplicates, VLOOKUP will return the first match it encounters. To find subsequent matches, you can use array formulas or helper columns.

Using Helper Columns:

1. Create a helper column that assigns unique identifiers to duplicates.
2. Use VLOOKUP with the helper column to retrieve multiple matches.

Example 10: Lookup with Multiple Criteria

VLOOKUP can manage scenarios where you need to match multiple criteria. However, it requires helper columns or a combination of functions like INDEX and MATCH.

Using VLOOKUP with Multiple Criteria:

1. Create helper columns with combined criteria.
2. Use VLOOKUP with the helper columns to retrieve data based on multiple conditions.

Example 10.1: Lookup with Multiple Criteria – Basic

Let’s say you have a table with sales data for different products and you want to find the total sales for a specific product (e.g., “Apple”) in a particular month (e.g., “January”).

Table: Sales Data

Product	Month	Sales
Apple	January	100
Banana	January	150
Apple	February	120
Banana	February	180

Using VLOOKUP with Multiple Criteria:

=SUMIFS(C2:C5, A2:A5, "Apple", B2:B5, "January")

In this example:

• We use the SUMIFS function to find the total sales (C2:C5) where both the product is “Apple” (A2:A5) and the month is “January” (B2:B5).

The formula returns 100, which is the total sales of “Apple” in “January.”

Example 10.2: Lookup with Multiple Criteria – Advanced

Suppose you have a more complex dataset with additional criteria, such as finding the total sales for a specific product (e.g., “Apple”) in a particular month (e.g., “January”) for a specific region (e.g., “North”).

Table: Sales Data

Product	Month	Region	Sales
Apple	January	North	50
Banana	January	North	75
Apple	February	North	60
Banana	February	North	90
Apple	January	South	50
Banana	January	South	75
Apple	February	South	60
Banana	February	South	90

Using VLOOKUP with Multiple Criteria:

=SUMIFS(D2:D9, A2:A9, "Apple", B2:B9, "January", C2:C9, "North")

In this advanced example:

• We use the SUMIFS function to find the total sales (D2:D9) where the product is “Apple” (A2:A9), the month is “January” (B2:B9), and the region is “North” (C2:C9).

The formula returns 50, which is the total sales of “Apple” in “January” in the “North” region.

You can adapt this approach to handle even more complex scenarios with multiple criteria in your data analysis using Excel.

Conclusion

The VLOOKUP function is a game-changer in Excel, making data retrieval a breeze. Whether you’re dealing with extensive datasets, creating interactive reports, or conducting in-depth data analysis, VLOOKUP can help you access the precise information you need. By mastering the art of VLOOKUP and its various applications, you become a data-savvy Excel user, capable of making informed decisions and transforming raw data into actionable insights.