Excel - XLOOKUP (Basic metrics) Tutorial

In this video, you will learn about how to search and find what you need using Microsoft 365. The video covers the usage of OneDrive, Yammer, and Delve to help you locate and access documents, collaborate with colleagues, and stay updated on the latest information.
This will help you improve your productivity and efficiency in finding relevant content within the Microsoft 365 environment.

4:19
412 views

00:00:03

Welcome. In this tutorial we will

00:00:05

see together how to implement

00:00:07

the HLOOKUP function.

00:00:09

For your information,

00:00:10

Excel has advantageously replaced the

00:00:13

historical vlookup with this one.

00:00:16

In general terms, the goal of any lookup

00:00:18

function is to retrieve data we need,

00:00:21

but we do not have. In our example,

00:00:24

we will search for the quantity.

00:00:27

Through a common item found in another table.

00:00:31

This will be the order number.

00:00:34

This common item is presented in

00:00:36

the order details, as I said before,

00:00:39

on the column E.

00:00:42

So in these two tables

00:00:46

to be linked here, in column E we want

00:00:49

to transport the quantity so we can

00:00:52

complete column H. How you might ask?

00:00:55

If you have already used the old vlookup,

00:00:59

it was mandatory that in the table where

00:01:02

you want to retrieve the information,

00:01:04

Order details in this case.

00:01:07

The common column had to be placed

00:01:11

before the column to transport.

00:01:13

So that means here. Furthermore,

00:01:17

some time ago in Excel the order number,

00:01:21

had to be positioned before the quantity

00:01:24

column so either column A or B to

00:01:26

be able for the function to work and

00:01:29

transport the data with the Hlookup

00:01:31

this is no longer an issue,

00:01:33

so to go through the Hlookup I

00:01:36

will go to the table, the orders table.

00:01:38

Then I will look for the

00:01:41

Hlookup function.

00:01:42

Using either the formula bar

00:01:45

or the insert function button.

00:01:48

I could find the Hlookup

00:01:51

In the most recently used or in

00:01:55

the lookup and reference category.

00:01:58

As I have used it recently.

00:02:00

I can find it on the most recently used.

00:02:03

I validate it.

00:02:06

And I only have to fill in

00:02:08

the different parameters.

00:02:09

You will notice that the

00:02:11

parameters are quite numerous.

00:02:12

We will focus on the basic features

00:02:14

of the Hlookup in this tutorial.

00:02:17

The first step will be to

00:02:19

specify what would be the element

00:02:21

considered as the search value.

00:02:23

As mentioned before,

00:02:24

it will be the order number

00:02:27

as it is the common element.

00:02:29

I therefore want to recover the quantity

00:02:33

relating to order number 10,954.

00:02:36

The lookup array parameter

00:02:39

is filled by specifying the

00:02:41

column of the table to be linked.

00:02:44

That means the one containing

00:02:47

all order numbers.

00:02:48

So it is column E in our

00:02:52

order details example.

00:02:53

The returned array parameter

00:02:55

will be always the one to tell

00:02:57

me on the table in which I've

00:02:59

retrieved the information,

00:03:00

which is the column to be

00:03:03

transported to the other table.

00:03:05

As we have established

00:03:06

before it will be quantity.

00:03:08

So I go back to my order details

00:03:11

and I click on quantity.

00:03:13

The if not found or 4th parameter

00:03:15

allows you to specify the text or

00:03:18

the piece of information that will

00:03:21

be shown in case of a mismatch.

00:03:23

If you do not feel it,

00:03:24

there will be an error message

00:03:27

and a that we know already.

00:03:30

I will personalize it in this

00:03:33

example by putting item not found.

00:03:37

The match mode is an important

00:03:41

parameter in which you must absolutely

00:03:44

and in most cases fill with a zero

00:03:47

as it says to the function that you

00:03:49

are looking for an exact match.

00:03:52

The other additional options in

00:03:54

the match mode will be explained

00:03:56

in the Advanced Lookup Function

00:03:59

tutorial once I validate it.

00:04:04

Everything will be shown,

00:04:06

including the personalized text.

00:04:10

And that's how you can simply and

00:04:13

effectively set an HLOOKUP function.

DiLeaP AI: THIS MIGHT BE HELPFUL

Excel - The HLOOKUP function

In this video, you will learn about the HLOOKUP function in Microsoft Excel.
The video covers the basic features of the HLOOKUP function and demonstrates how to use it to retrieve data based on a common element.
By specifying the search value, lookup array, returned array, and match mode, you can easily set up the HLOOKUP function.
This will help you efficiently retrieve data and complete your tasks in Excel.

2:20
497 views

Excel - The VLOOKUP function

In this video, you will learn about the VLOOKUP function in Microsoft 365. The video demonstrates how to use the VLOOKUP function to retrieve data from another table based on a reference field.
It covers the steps to select the lookup value, specify the table array, determine the column index number, and choose the match type.
This knowledge will help you effectively use the VLOOKUP function in Microsoft 365 to retrieve specific data.

4:10
595 views

Excel - VLOOKUP: How and when to use it

In this video, you will learn about VLOOKUP and how and when to use it.
The video covers the VLOOKUP function, which is similar to a phone book, where you start with a piece of data and use it to find other related information.
The video demonstrates how to use VLOOKUP to find prices based on part numbers in a large spreadsheet.
By entering the part number in a specific cell, you can retrieve the corresponding price.
This will help you efficiently find information in a large dataset and save time.

2:38
1144 views

Excel - Obtain the numeric coordinates of a cell

In this video, you will learn how to obtain the numeric coordinates of a cell using Microsoft 365. The video covers the use of the row and column functions to determine the position of a cell.
By using these functions, you can easily retrieve the row and column numbers of a specific cell.
This knowledge will be helpful when working with functions that require cell references.
By understanding how to obtain the numeric coordinates of a cell, you will be able to retrieve values from specific positions in different tables, enhancing your data analysis skills.

3:02
384 views

Excel - The nuts and bolts of VLOOKUP

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
1448 views