Excel - The nuts and bolts of VLOOKUP Tutorial

In this video, you will learn about the nuts and bolts of VLOOKUP.
The video breaks down how VLOOKUP works and explains its four arguments:
the lookup value, the table array, the column reference, and the range lookup.
It also highlights some important considerations, such as the need for the lookup values to be in the left-hand column of the table array and the arrangement of data in columns vertically.
The video demonstrates how to use VLOOKUP to find exact matches or partial matches and provides examples of both.
This tutorial will help you understand and effectively use the VLOOKUP function in Microsoft 365.

3:05
4549 views

Available in Microsoft365

Objectifs :

Understand the VLOOKUP function in Excel, including its arguments and how to use it for both exact and partial matches.

Chapitres :

Introduction to VLOOKUP
The VLOOKUP function is a powerful tool in Excel that allows users to search for specific data within a table. This section will break down the components of the VLOOKUP function and explain how it operates.
Understanding the Arguments of VLOOKUP
VLOOKUP requires four key arguments to function effectively: 1. **Lookup Value**: This is the data you already know, which in this example consists of part numbers. It is advisable to use cell references for these values rather than entering them directly into the formula for ease of use. 2. **Table Array**: This refers to the range of cells that you want to search. In our example, the table array is defined as cells B3 through E52. It is crucial to ensure that the lookup values are located in the leftmost column of this range, as VLOOKUP will not search to the left of the lookup values. 3. **Column Index Number**: This argument indicates which column's data you want to retrieve. In this case, we are interested in the data from column D, which is the third column in our specified range. Remember, the data must be organized vertically in columns, as indicated by the 'V' in VLOOKUP. 4. **Range Lookup**: This final argument specifies whether you want an exact match or a partial match. By entering 'False', you request an exact match. If you want to allow for partial matches, you can use 'True' or leave this argument blank.
Examples of VLOOKUP Usage
When you enter a part number into the VLOOKUP function, it retrieves the corresponding price. However, there are scenarios where you may want to allow for partial matches. For instance, if you are calculating discounts based on quantities purchased, you might not want to restrict customers to exact quantities like 10 or 100. In such cases, entering a quantity of items purchased can yield a discount, as shown in the example where 'True' is used for the range lookup, allowing for more flexible matching.
Key Takeaways
To summarize the key points about VLOOKUP: - **Exact Matches**: Use 'False' for exact matches. - **Partial Matches**: Use 'True' or leave the argument blank for partial matches. - **Data Arrangement**: Ensure your data is organized vertically in columns, with lookup values in the leftmost column of the table array. Understanding these principles will enhance your ability to effectively utilize the VLOOKUP function in Excel.
Next Steps
In the following section, we will explore how to use the VLOOKUP function to find data across different worksheets, expanding its application and utility in your Excel projects.

FAQ :

What is the VLOOKUP function used for in Excel?

The VLOOKUP function is used to search for a specific value in the first column of a table and return a corresponding value from another column in the same row.

What are the four arguments required for the VLOOKUP function?

The four arguments are: 1) Look up value, 2) Table array, 3) Column reference, and 4) Range lookup.

Can I use VLOOKUP to search for values in any column of the table?

No, the lookup values must be in the leftmost column of the table array. VLOOKUP will not search to the left of the lookup values.

What does the Range lookup argument do?

The Range lookup argument specifies whether you want an exact match (using 'False') or an approximate match (using 'True' or leaving it blank').

How do I perform a partial match using VLOOKUP?

To perform a partial match, you can set the Range lookup argument to 'True' or leave it blank. This allows for approximate matches to the lookup value.

What happens if I use 'False' in the Range lookup argument?

Using 'False' in the Range lookup argument will return only exact matches for the lookup value.

Quelques cas d'usages :

Inventory Management

Using VLOOKUP to quickly find the price of items in an inventory list based on part numbers, improving efficiency in stock management.

Sales Reporting

Employing VLOOKUP to match sales data with product details, allowing sales teams to generate reports that include product descriptions and prices.

Customer Discounts

Utilizing VLOOKUP to calculate discounts based on the quantity of items purchased, enabling businesses to offer tailored pricing to customers.

Data Analysis

Applying VLOOKUP in data analysis tasks to merge datasets from different sources, ensuring that relevant information is easily accessible.

Financial Forecasting

Using VLOOKUP to retrieve historical sales data for financial forecasting, helping businesses make informed decisions based on past performance.

Glossaire :

VLOOKUP

A function in Excel that searches for a value in the first column of a table and returns a value in the same row from a specified column.

Look up value

The value you want to search for in the first column of the lookup table. In the example, these are part numbers.

Table array

The range of cells that contains the data you want to search. It is also known as the lookup table.

Column reference

A number that indicates which column in the table array contains the data you want to retrieve. For example, '3' refers to the third column.

Range lookup

An argument in the VLOOKUP function that specifies whether to find an exact match (False) or an approximate match (True).

Exact match

A search result that must match the lookup value exactly. This is indicated by using 'False' in the range lookup argument.

Partial match

A search result that allows for approximate matches to the lookup value. This is indicated by using 'True' or leaving the argument blank.

00:00:08

Now let's break that example down and see how it works.

00:00:11

VLOOKUP uses four arguments, or pieces of data.

00:00:16

The first argument is called the Look up value, and it's the data you know;

00:00:21

if I were using a phone book, the Look up value would be someone's name.

00:00:27

In this example though the Look up values are part numbers,

00:00:32

and I'm using a cell reference as a placeholder for those part numbers.

00:00:36

If you wanted to, could also enter the values

00:00:39

directly in the formula; like so.

00:00:44

But as a rule it's easier to enter values in cells than

00:00:47

in formulas, so that's what I did here.

00:00:50

The next argument is the block of values that you want to search.

00:00:55

Excel calls this the Table array, or the Look up table,

00:00:58

and our example uses cells B3 through E52.

00:01:03

You can use any cell range that you think will return

00:01:06

the data you need to find, but you need to remember a gotcha.

00:01:10

The Look up values, the data you know, have to be in

00:01:14

the left hand column of your Look up table; your cell range.

00:01:18

You can have data to the left of your Look up values,

00:01:21

but VLOOKUP won't search there.

00:01:25

The next argument, the number 3, is a column reference;

00:01:29

it tells VLOOKUP where you expect to find the data you want to see.

00:01:34

We're using column D; the third column over from the Look up values,

00:01:38

so I entered 3.

00:01:41

And this also points to another gotcha:

00:01:43

your data has to be arranged in columns vertically.

00:01:47

Just remember that the V in VLOOKUP stands for

00:01:51

"vertical", and columns are vertical.

00:01:54

The last argument is called the Range look up,

00:01:57

and it tells the function that I want an exact match,

00:02:00

or a partial match, to my look up value.

00:02:03

I'm using False to give me exact matches; I entered

00:02:06

a part number; I got a price.

00:02:09

But sometimes you only want a partial match.

00:02:12

For example, this Excel table calculates discounts, and

00:02:16

it assumes customers don't want to buy exactly 10 or 100 of anything.

00:02:22

In other words, I don't want to limit them to finding

00:02:24

matches to just the values in this column.

00:02:28

So I enter the number of items purchased, and I get a discount of 9%.

00:02:34

If you look at the arguments for the function, you can

00:02:36

see it uses True instead of False.

00:02:39

That lets me enter quantities that don't match the values listed in the table.

00:02:44

So another gotcha to remember:

00:02:47

False returns exact matches; True returns partial matches.

00:02:52

Also if you want to use True you can just leave the argument blank.

00:02:58

So that's how VLOOKUP works, and next I'll show you how

00:03:01

to use it to find data on a different worksheet.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2501 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES