Excel - XLOOKUP (Basic metrics) Tutorial

4:19
114 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.

Excel - XLOOKUP (Basic metrics) Tutorial

RELATED COURSES This tutorial is part of the following courses

Microsoft Excel Video Training – Intermediate