VLOOKUP Formula Excel with Examples

Last Updated on: 18th February 2024, 07:21 pm

In the realm of spreadsheet wizardry, few formulas wield as much power and versatility as the VLOOKUP function in Microsoft Excel. Whether you’re a seasoned Excel veteran or a newcomer to the world of spreadsheets, understanding how to leverage VLOOKUP can significantly enhance your data analysis and manipulation capabilities.

What is VLOOKUP?

VLOOKUP, short for “Vertical Lookup,” is a lookup and reference function in Excel used to search for a value in the leftmost column of a table and return a value in the same row from a specified column. Essentially, it allows you to search for a value vertically within a dataset and retrieve corresponding information from adjacent columns.

See also: COUNTIFS and SUMIFS Excel Functions Explained

Syntax of VLOOKUP

The syntax of the VLOOKUP function is as follows:

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

• lookup_value: The value you want to search for.
• table_array: The range of cells that contains the data. The leftmost column of this range is where the lookup_value will be searched.
• col_index_num: The column number in the table_array from which to retrieve the value.
• [range_lookup] (optional): A logical value that specifies whether you want an exact or approximate match. If TRUE or omitted, VLOOKUP will search for an approximate match. If FALSE, VLOOKUP will search for an exact match.

Example Usage of VLOOKUP

Let’s dive into some examples to illustrate how VLOOKUP works:

Example 1: Basic VLOOKUP

Suppose we have a table containing a list of products and their corresponding prices. We want to retrieve the price of a specific product based on its name.

To find the price of a Banana, we can use the following formula:

=VLOOKUP("Banana", A2:B6, 2, FALSE)

In this formula:

• "Banana" is the lookup_value.
• A2:B6 is the table_array.
• 2 specifies the second column in the table_array (the Price column).
• FALSE indicates that we want an exact match.

The result of this formula will be $0.75, the price of a Banana.

Example 2: Approximate Match

Now, let’s consider a scenario where we have a table that maps numeric grades to letter grades. We want to find the letter grade corresponding to a specific numeric score.

To find the letter grade for a numeric score of 75, we can use the following formula:

=VLOOKUP(75, A2:B6, 2, TRUE)

In this formula:

• 75 is the lookup_value.
• A2:B6 is the table_array.
• 2 specifies the second column in the table_array (the Letter Grade column).
• TRUE indicates that we want an approximate match.

The result of this formula will be c, as it finds the closest match to 75, which is 70 in the Numeric Grade column.

Advanced Usage of VLOOKUP Formula

Example 1: Using VLOOKUP with Multiple Criteria

In some cases, you may need to perform a lookup based on multiple criteria. This can be achieved by combining the VLOOKUP function with other functions like CONCATENATE or TEXTJOIN to create a composite lookup value.

Suppose we have a table containing sales data for different regions and products. We want to retrieve the sales amount for a specific product in a specific region.

To find the sales amount for “Banana” in the “North” region, we can use the following formula:

=VLOOKUP(CONCATENATE("North", "Banana"), A2:C7, 3, FALSE)

In this formula, CONCATENATE("North", "Banana") creates the composite lookup value “NorthBanana”, which combines the region and product. The formula then searches for this composite value in the first column of the table array and returns the corresponding sales amount.

Example 2: Using VLOOKUP with Dynamic Table Ranges

In dynamic scenarios where the size of your data may change, you can use named ranges and structured references to create dynamic table ranges. This allows your VLOOKUP formulas to adapt automatically to changes in the dataset.

Suppose we have a dynamic table named “SalesData” that contains sales data for various products. We want to retrieve the sales amount for a specific product based on user input.

To achieve this, we can define a named range for the table range:

1. Select the range of data (excluding headers) in the “SalesData” table.
2. Go to the Formulas tab, click on Define Name.
3. Enter a name for the range, such as “SalesRange”, and click OK.

Now, we can use the named range in our VLOOKUP formula:

=VLOOKUP("Banana", SalesRange, 2, FALSE)

This formula will look up “Banana” in the first column of the dynamic table range “SalesRange” and return the corresponding sales amount.

Example 3: Using VLOOKUP with Wildcards

VLOOKUP can be combined with wildcard characters to perform partial matches or pattern matching.

Suppose we have a table containing a list of products with various descriptions. We want to retrieve the price of a product based on a partial description.

To find the price of any product containing the word “Apple”, we can use the following formula:

=VLOOKUP("*Apple*", A2:B6, 2, FALSE)

In this formula, the wildcard asterisks (*) before and after “Apple” indicate that we are searching for any product description containing the word “Apple”. The formula will return the price of the first matching product.

These advanced examples demonstrate the flexibility and power of the VLOOKUP formula in Excel, showcasing its ability to handle complex lookup scenarios and adapt to dynamic data structures. By mastering these techniques, you can elevate your Excel skills and efficiently analyze and manipulate data in various business contexts.