Excel - XLOOKUP (Advanced metrics) Tutorial

In this video, you will learn about the intelligent search feature in Microsoft 365. The video covers the search tools in Microsoft 365, including searching on your computer, using Microsoft Search, finding content and people in SharePoint, searching your emails, and more.
This will help you save time and efficiently find documents, information, and even conversations within the Microsoft 365 suite.
Gain valuable insights into the search capabilities of Microsoft 365 and enhance your productivity.

567 views

00:00:03

Welcome. In this tutorial I will try to

00:00:05

show you how to use the advanced settings

00:00:07

related to the HLOOK UP function

00:00:09

in the loan chart that we have here.

00:00:11

As you can see the loan amounts vary.

00:00:15

We want to apply an administrative

00:00:17

expense fee related to the scales of

00:00:20

the small table here on the right.

00:00:22

See that not all amounts on the column

00:00:24

are loans, are found on the index as we are

00:00:27

working with a system of blocks if you will.

00:00:30

So it means that all loans between 0

00:00:33

and 6000 will have the same index

00:00:36

at this theoretically applied,

00:00:38

I will take the €125 fee as an example.

00:00:42

I'm going to set an HLOOKUP formula.

00:00:46

But first, let's add a column to do so,

00:00:49

and let's call it administrative expense.

00:00:54

The element that will define the

00:00:56

amount of the application fees will

00:00:58

be the amount of the loan here.

00:01:03

14,770 or sell the eight.

00:01:06

I will ask my function to search

00:01:08

that amount on the list loan amount.

00:01:13

And I will specify as well the value

00:01:17

and lookup array that I would look to.

00:01:20

The next parameter will be.

00:01:22

The if not found.

00:01:24

That will allow me to personalize

00:01:28

a text if there is a match.

00:01:30

I would leave it blank since it is optional.

00:01:34

You can recognize it because

00:01:35

it is in a square bracket.

00:01:37

On the other hand,

00:01:39

the match mode parameter,

00:01:41

which is also optional,

00:01:43

will be enabled by specifying

00:01:45

the value minus one.

00:01:46

That means exact match or next smaller item

00:01:50

which will let Excel know that if the

00:01:53

precise amount is not found in the list,

00:01:56

then it is the nearest lower

00:01:59

value that should be retrieved.

00:02:01

The last one, search mode,

00:02:03

will not be set at the moment either,

00:02:06

so I close the parenthesis,

00:02:08

validate and we'll be able to

00:02:11

copy my formula.

00:02:12

Remember to use F4 to lock the values.

00:02:19

We will be able to see that indeed,

00:02:22

if I take a random loan,

00:02:24

for instance, 13,000.

00:02:27

That it has applied a €160.00 fee.

00:02:32

We are well in the range

00:02:34

between 11,000 and 20,000.

00:02:38

So there is not really a value of 13 found,

00:02:41

but the nearest lower value was the

00:02:45

one given by the formula and itself.

00:02:48

The option we have just discussed is

00:02:51

very useful to create a sort of slice

00:02:54

system and avoid going through the

00:02:56

classic conditional functions of itself.

00:02:59

Now let's move on to a second

00:03:01

use of the HLOOKUP

00:03:03

I want to be able to use the

00:03:07

checking sheet more efficiently.

00:03:09

One way is by entering a loan number.

00:03:13

And with that automatically

00:03:15

retrieving 4 pieces of information

00:03:18

stored in the customer list.

00:03:21

The name, amount, length and monthly payment.

00:03:25

I will be able through the HLOOKUP

00:03:28

to generate a formula that will feed

00:03:31

the four pieces of information without

00:03:34

making 4 formulas individually.

00:03:37

But first I will have to transform

00:03:39

my list into a table mode.

00:03:41

I will use this opportunity

00:03:44

to rename my table loan.

00:03:46

And then place myself in the first

00:03:49

of the four cells, which is here.

00:03:52

I will enter my HLOOKUP and

00:03:56

then I will say that I need the

00:03:59

value that I will put in C6.

00:04:02

And that the information will be

00:04:05

found on my client list sheet.

00:04:08

On my column Leona number.

00:04:13

With the return array.

00:04:15

I will be able to specify the recovery

00:04:18

of the data found within the name

00:04:21

to the monthly payment columns.

00:04:24

In case of a mismatch,

00:04:26

I can very well inform the system

00:04:29

to provide an answer like unknown

00:04:32

loan number with the match mode.

00:04:38

That way I will avoid having the answer

00:04:41

non applicable if I enter a low number

00:04:44

that does not exist with the match mode.

00:04:47

I will ask for the exact one by using zero.

00:04:51

I will go back to the checking

00:04:54

sheet and automatically get an

00:04:56

unknown loner number answer.

00:04:59

If I enter a loan number found

00:05:01

in the list such as B for 1:50.

00:05:08

I would get all the corresponding items,

00:05:11

but there will be an issue

00:05:13

with the placement.

00:05:14

I will take this opportunity to show

00:05:17

and use a formula called =TRANSPOSE

00:05:20

that allows to transform elements that

00:05:22

are shown in line in the form of column.

00:05:30

So this will work as a matrix formula

00:05:33

that allows me with a single formula

00:05:35

to retrieve more information.

00:05:37

Let's say that in case.

00:05:39

I partially enter a loan number.

00:05:42

For instance F1.

00:05:43

Inevitably we will get the same message,

00:05:46

unknown loan number.

00:05:50

There is an additional parameter to set

00:05:52

In the HLOOKUP function

00:05:54

add the match mode.

00:05:56

As you can see you can activate different

00:05:59

options to get different results.

00:06:02

For example, if I put an F and a star,

00:06:04

the system will recover the first

00:06:06

loan responding to my entry.

00:06:08

So the F and star could be the first

00:06:11

credit number with F so either 157 or 196.

00:06:15

How do I get there?

00:06:17

I would inform

00:06:20

#2 Wild Card character match.

00:06:23

So let's try it and we go here, Simon,

00:06:28

there we go,

00:06:30

F 157 Simon.D. So this is another

00:06:34

interesting feature with the

00:06:36

HLOOKUP they use of the generic option.

00:06:39

Now let's move on to another example of

00:06:42

the advanced options found in the

00:06:45

HLOOKUP function in my client list sheet.

00:06:48

The list of low numbers is

00:06:51

arranged chronologically from

00:06:53

the oldest to the most recent.

00:06:55

The loans with its varied amounts have

00:06:59

been set with 24, 26 or 48 months.

00:07:05

I want in my consultation sheet

00:07:07

to retrieve the number of the

00:07:09

most recent loans among all the

00:07:12

loans within the 24 month length.

00:07:14

I will use the same formula.

00:07:16

So let's go to checkins and let's go here.

00:07:19

However, I will go through the dialog box.

00:07:23

So I go here.

00:07:25

The function arguments and I will be

00:07:27

able to specify that from the length,

00:07:30

Excel will have to search in all the

00:07:32

length in column E and send me the

00:07:35

number of the loan which is in column A.

00:07:40

The if not found is not mandatory.

00:07:43

If I leave it blank then

00:07:45

the error value will be Na.

00:07:47

In terms of match mode.

00:07:49

I will ask him to find me

00:07:50

the exact one with zero.

00:07:52

However, on the match mode if you

00:07:55

can see I do not have the choices

00:07:58

available as we do with the formula bar.

00:08:01

So I will advise you to go through

00:08:03

the help on this function so

00:08:05

you can have the explanation.

00:08:07

I will use minus one.

00:08:10

Why if we click here?

00:08:13

The system tells me that I

00:08:14

will have an exact match.

00:08:15

I will have an exact match.

00:08:17

If none found,

00:08:18

return the next smaller item,

00:08:20

the one that we used before,

00:08:22

and remember that the two is the

00:08:24

one that we used as a wildcard

00:08:26

for generic purposes.

00:08:30

We haven't set the return array

00:08:32

and that is because I made a silly

00:08:35

mistake and I'm sorry I just go here.

00:08:39

I go here and the lookup value is

00:08:43

actually the one that stays 24,

00:08:46

so that will be B18.

00:08:51

There we go. So the answer is

00:08:57

B590. So if I recap that, the low

00:09:01

number I get for a 24 month loan,

00:09:04

or the most recent one for

00:09:06

that matter, is indeed be 590.

00:09:14

There we go. 24 so the HLOOKUP

00:09:19

function is therefore a real advance

00:09:21

in terms of functionality compared

00:09:24

to the VLOOKUP we used before.

RELATED COURSES This tutorial is part of the following courses

Video Training Microsoft Excel - Advanced level

With our "advanced" tutorials, discover and master the full potential of Excel, using complex functions, macros and the power of DCTs and Power Pivot, combined with the Power Query tool.

Your trainer for this course
Cours E-learning

165 vues

ALL COURSES