Excel - XLOOKUP (Advanced metrics) Tutorial

9:30
87 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.

Excel - XLOOKUP (Advanced metrics) Tutorial

RELATED COURSES This tutorial is part of the following courses

Microsoft Excel Video Training – Intermediate