Excel - Look up values on a different worksheet Tutorial

In this video, you will learn how to look up values on a different worksheet using Microsoft 365. The video demonstrates how to use the VLOOKUP function to aggregate data from multiple worksheets into one place.
By entering the appropriate arguments, such as the column reference and the range of cells to search, you can find matching values and retrieve the desired information.
This tutorial will help you efficiently gather and analyze data from different worksheets, improving your data management skills.

2:39
4705 views

Description

Objectifs :

Understand how to use the VLOOKUP function in Excel to aggregate data from multiple worksheets, including the use of absolute cell references for copying formulas.

Chapitres :

Introduction to VLOOKUP
The VLOOKUP function in Excel is a powerful tool that allows users to search for a value in one column and return a corresponding value from another column. This is particularly useful when working with data spread across multiple worksheets. In this guide, we will explore how to effectively use VLOOKUP to aggregate data from different sheets.
Setting Up Your Data
In our example, we have two worksheets: one containing page numbers and hits, and another with page names. The first sheet lists page numbers in column A and their corresponding hits in column B. The second sheet contains page numbers in column A and page names in column B. This setup allows us to use VLOOKUP to combine this information.
Using VLOOKUP to Aggregate Data
To begin using VLOOKUP, we will start on the 'pages' tab. Here are the steps to follow: 1. Identify the column with the data you want to retrieve. In this case, the page names are in column B (the second column). 2. Define your lookup table. The data we want to search spans from cell A2 to B39. 3. Navigate to the 'pageviews' tab and enter the headings for your data. 4. In the cell where you want the result, type the VLOOKUP function: - Start with 'VLOOKUP('. - Enter the lookup value (e.g., F2). - Specify the lookup table by referencing the other tab and the range (e.g., 'pages!A2:B39'). - Indicate the column number (2 for page names). - For exact matches, add 'FALSE' as the range lookup argument. 5. Press Enter to complete the formula.
Handling Errors and Inputting Data
After entering the formula, you may encounter an error if the lookup value in cell F2 is empty. To resolve this, copy a page ID from the first sheet and paste it into cell F2. This will allow VLOOKUP to return the corresponding page name. Repeat this process for other page IDs to see their respective names.
Using Absolute Cell References
To efficiently copy the VLOOKUP formula down a column, you need to use absolute cell references. This ensures that the lookup table remains constant while the lookup value changes. To create an absolute reference, add dollar signs before the column and row numbers in your lookup table argument (e.g., '$A$2:$B$39'). This way, when you drag the formula down, the reference to the lookup table will not change.
Conclusion
In summary, the VLOOKUP function is an essential tool for aggregating data from multiple worksheets in Excel. By understanding how to set up your data, use the function correctly, handle errors, and apply absolute references, you can streamline your data analysis process. Mastering these techniques will enhance your ability to work with complex datasets effectively.

FAQ :

What is VLOOKUP used for in Excel?

VLOOKUP is used to search for a specific value in the first column of a range and return a corresponding value from another column in the same row, making it useful for aggregating data from different sheets.

How do I create a lookup table for VLOOKUP?

To create a lookup table for VLOOKUP, organize your data in a range of cells where the first column contains the values you want to search for, and the subsequent columns contain the data you want to retrieve.

What does the 'False' argument in VLOOKUP mean?

'False' in VLOOKUP indicates that you want an exact match for the lookup value. If an exact match is not found, the function will return an error.

How can I use absolute cell references in VLOOKUP?

You can use absolute cell references in VLOOKUP by adding dollar signs to the cell references in your formula (e.g., $A$2:$B$39). This ensures that the reference does not change when you copy the formula to other cells.

What should I do if I get an error message when using VLOOKUP?

If you receive an error message when using VLOOKUP, check to ensure that your lookup value exists in the first column of your lookup table and that you have entered the correct range and column reference.

Quelques cas d'usages :

Aggregating Web Traffic Data

Using VLOOKUP to combine data from multiple sheets in an Excel workbook, such as page IDs, names, and hits, to analyze web traffic and performance metrics.

Generating Reports

Employing VLOOKUP to pull specific data points from a large dataset to create concise reports for stakeholders, improving data presentation and decision-making.

Data Validation

Utilizing VLOOKUP to cross-reference data entries against a master list to ensure accuracy and consistency in data entry processes.

Sales Analysis

Applying VLOOKUP to match product IDs with sales data across different sheets, allowing for comprehensive sales analysis and inventory management.

Customer Feedback Aggregation

Using VLOOKUP to aggregate customer feedback from various sources into a single sheet, enabling better analysis of customer satisfaction and areas for improvement.

Glossaire :

VLOOKUP

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

lookup table

A range of cells that contains the data you want to search through using a lookup function like VLOOKUP.

absolute cell references

A way to keep a cell reference constant when copying a formula to another cell, denoted by a dollar sign (e.g., $A$1).

page ID

A unique identifier assigned to a specific page, used to track and reference that page in databases or spreadsheets.

page views

The number of times a specific page has been viewed, often used as a metric for website traffic.

Available in Microsoft365

00:00:06

worksheet in a workbook.

00:00:09

As an example, we downloaded some web site data from a database,

00:00:13

and the first sheet has page numbers for the site pages,

00:00:17

and the hits on each page.

00:00:19

The second sheet also has the page numbers, plus the page names.

00:00:24

VLOOKUP can help me aggregate this data so I can see

00:00:28

page IDs, names and hits in one place.

00:00:33

I'll start here on the pages tab because it gives me two

00:00:36

of the function's arguments.

00:00:38

First, I see the page names are listed in column B, the second column over.

00:00:44

This is what I want VLOOKUP to find, the answers I want

00:00:47

to see, so my column reference argument is 2.

00:00:51

Also, I know I want to find the page name that matches a given page number.

00:00:58

That data starts in Cell A2 an ends in cell B39,

00:01:03

so A2 to B39 is my look up table argument.

00:01:08

Now I'll go to the page views tab, enter a couple of headings,

00:01:13

and my function, =VLOOKUP,

00:01:19

open parentheses, and F2 is my look up value

00:01:23

because that's where I'll enter the page IDs.

00:01:27

Next I enter pages, the name of the other tab,

00:01:30

an exclamation point, then my look up table,

00:01:35

a comma, and my column reference.

00:01:38

In other words, the look-up table argument is now

00:01:41

the name of the worksheet, the exclamation point,

00:01:45

and the range of cells I want to search.

00:01:49

Because I need exact matches, I'll enter False as

00:01:52

my range look up argument, press Enter to finish the formula;

00:01:56

and you can see I get an error message because

00:01:58

the formula wants a value in cell F2.

00:02:02

So I'll copy a page ID here,

00:02:06

paste it,

00:02:11

and now I know how many hits National Page News 2 received;

00:02:15

and to do this again, press Escape to clear the marquee,

00:02:19

copy another page number,

00:02:24

paste it;

00:02:26

and you'll see another page name.

00:02:29

So next, I'll show you how to use absolute cell

00:02:32

references in VLOOKUP formulas; they are how you copy

00:02:35

the formula down a column.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

E-learning Intermédiaire

Vu 2646 fois

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 pivot tables, and take advantage of all that Excel has to offer!

ALL COURSES