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.

9:30
2391 views

Available in Microsoft365

Objectifs :

This tutorial aims to demonstrate the advanced settings related to the HLOOKUP function in Excel, specifically in the context of managing loan data and administrative expense calculations. It will cover how to apply the HLOOKUP function effectively to retrieve information from a dataset, handle errors, and utilize advanced features such as wildcards and transposing data.

Chapitres :

Introduction to HLOOKUP Function
In this tutorial, we will explore the advanced settings of the HLOOKUP function within a loan chart. The goal is to apply an administrative expense fee based on varying loan amounts. We will also address how to efficiently retrieve multiple pieces of information related to loans using HLOOKUP.
Setting Up the Administrative Expense Column
To begin, we will add a new column titled 'Administrative Expense' to our dataset. This column will calculate the administrative fee based on the loan amount. For example, if we take a loan amount of 14,770, we will use the HLOOKUP function to search for this amount in the loan amount list. The function will also allow us to specify a custom message if no match is found, which we will leave blank as it is optional.
Understanding HLOOKUP Parameters
The HLOOKUP function requires several parameters: - **Lookup Value**: The loan amount we are searching for. - **Lookup Array**: The range where the function will search for the loan amount. - **If Not Found**: A custom message if no match is found (optional). - **Match Mode**: Set to -1 for an exact match or the next smaller item. - **Search Mode**: Not set in this case. After entering the formula, remember to use F4 to lock the values for copying the formula across other cells.
Applying the HLOOKUP Function
For instance, if we input a loan amount of 13,000, the formula will return a fee of 160, which corresponds to the nearest lower value in the specified range. This method is particularly useful for creating a structured system without relying on traditional conditional functions.
Retrieving Multiple Loan Information
Next, we will enhance our checking sheet by using HLOOKUP to retrieve four pieces of information (name, amount, length, and monthly payment) from the customer list based on a loan number. First, we will convert our list into a table format and rename it 'Loan'. Then, we will enter the HLOOKUP formula in the first cell of the four information cells, specifying the loan number and the corresponding columns to retrieve data.
Handling Errors and Wildcards
In case of a mismatch, we can set the function to return a message like 'Unknown loan number'. By using the match mode set to zero, we ensure that only exact matches are considered. If we partially enter a loan number, we can utilize wildcards (e.g., 'F*') to retrieve the first loan that matches the criteria.
Finding Recent Loans
We will also explore how to find the most recent loans with a specific length (e.g., 24 months) using HLOOKUP. By specifying the length in the function arguments, we can retrieve the loan number corresponding to the most recent entry. The match mode will again be set to zero for exact matches.
Conclusion
In summary, the HLOOKUP function offers advanced capabilities that enhance data retrieval and management in Excel. By understanding its parameters and utilizing features like wildcards and error handling, users can efficiently work with loan data and streamline their processes compared to traditional methods like VLOOKUP.

FAQ :

What is the HLOOKUP function used for in Excel?

The HLOOKUP function is used to search for a value in the top row of a table and return a value from a specified row in the same column.

How do I apply an administrative expense fee using HLOOKUP?

You can apply an administrative expense fee by using the HLOOKUP function to find the loan amount in a specified range and return the corresponding fee based on the defined parameters.

What does the match mode parameter do in HLOOKUP?

The match mode parameter specifies how the function should match the lookup value. You can set it to find an exact match or the next smaller item.

Can I use wild card characters in HLOOKUP?

Yes, you can use wild card characters in HLOOKUP to allow for flexible matching of text. For example, using an asterisk (*) can match any number of characters.

What is the difference between HLOOKUP and VLOOKUP?

HLOOKUP searches for values in a horizontal array (top row), while VLOOKUP searches in a vertical array (leftmost column).

How can I retrieve multiple pieces of information using HLOOKUP?

You can retrieve multiple pieces of information by using HLOOKUP in combination with the TRANSPOSE function, which allows you to display the results in a different orientation.

Quelques cas d'usages :

Applying Administrative Fees to Loans

In a financial institution, the HLOOKUP function can be used to automatically apply administrative expense fees to various loan amounts based on a predefined fee structure, improving efficiency in loan processing.

Automating Customer Information Retrieval

A loan officer can use HLOOKUP to quickly retrieve customer information such as name, loan amount, length, and monthly payment by entering a loan number, streamlining the customer service process.

Analyzing Loan Data

Data analysts can utilize HLOOKUP to analyze loan data by retrieving the most recent loans of a specific length, allowing for better decision-making and reporting.

Creating Dynamic Reports

Using HLOOKUP in conjunction with other Excel functions, businesses can create dynamic reports that automatically update based on user input, enhancing data visualization and accessibility.

Handling Partial Loan Number Entries

In a customer service setting, representatives can use HLOOKUP with wild card characters to handle partial loan number entries, ensuring they can still retrieve relevant information even if the full number is not provided.

Glossaire :

HLOOKUP

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

Administrative Expense Fee

A fee applied to loans that covers administrative costs associated with processing the loan.

Match Mode

An optional parameter in the HLOOKUP function that specifies how Excel should match the lookup value. It can be set to find an exact match or the next smaller item.

Search Mode

An optional parameter in the HLOOKUP function that determines the order in which Excel searches for the lookup value. It can be set to search from the first or last item.

Transpose

A function in Excel that allows users to switch the rows and columns of a range of cells.

Wild Card Character

A special character used in search functions to represent one or more characters, allowing for flexible matching of text.

Return Array

The range of cells from which the HLOOKUP function retrieves the value corresponding to the lookup value.

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.

00:00:03

Bem-vindo. Neste tutorial vou tentar

00:00:05

mostrar-lhe como utilizar as definições avançadas

00:00:07

relacionada com a função HLOOK UP

00:00:09

no gráfico de empréstimos que temos aqui.

00:00:11

Como você pode ver, os valores do empréstimo variam.

00:00:15

Queremos aplicar um

00:00:17

taxa de despesa relacionada com as tabelas de

00:00:20

a pequena mesa aqui à direita.

00:00:22

Veja que nem todos os valores na coluna

00:00:24

são empréstimos, encontram-se no índice como estamos

00:00:27

trabalhando com um sistema de blocos, se quiser.

00:00:30

Assim, significa que todos os empréstimos entre 0

00:00:33

e 6000 terão o mesmo índice

00:00:36

neste teoricamente aplicado,

00:00:38

Vou tomar como exemplo a taxa de 125 euros.

00:00:42

Vou definir uma fórmula PROCH.

00:00:46

Mas primeiro, vamos adicionar uma coluna para fazer isso,

00:00:49

e chamemos-lhe despesa administrativa.

00:00:54

O elemento que definirá o

00:00:56

o montante das taxas de candidatura

00:00:58

seja o valor do empréstimo aqui.

00:01:03

14.770 ou vender os oito.

00:01:06

Vou pedir a minha função para pesquisar

00:01:08

esse montante na lista do montante do empréstimo.

00:01:13

E vou especificar também o valor

00:01:17

e matriz de pesquisa que eu procuraria.

00:01:20

O próximo parâmetro será.

00:01:22

O se não encontrado.

00:01:24

Isso permitir-me-á personalizar

00:01:28

um texto se houver uma correspondência.

00:01:30

Gostaria de deixá-lo em branco, uma vez que é opcional.

00:01:34

Você pode reconhecê-lo porque

00:01:35

está entre parênteses retos.

00:01:37

Por outro lado

00:01:39

o parâmetro match mode,

00:01:41

que também é opcional,

00:01:43

será ativado especificando

00:01:45

o valor menos um.

00:01:46

Isso significa correspondência exata ou próximo item menor

00:01:50

que permitirá que o Excel saiba que se o

00:01:53

quantidade exata não é encontrada na lista,

00:01:56

então é o mais próximo inferior

00:01:59

valor que deve ser recuperado.

00:02:01

O último, modo de pesquisa,

00:02:03

também não será definido de momento,

00:02:06

por isso fecho o parêntese,

00:02:08

validar e seremos capazes de

00:02:11

copiar a minha fórmula.

00:02:12

Lembre-se de usar F4 para bloquear os valores.

00:02:19

Poderemos ver que, de facto,

00:02:22

se eu tomar um empréstimo aleatório,

00:02:24

por exemplo, 13.000.

00:02:27

Que aplicou uma taxa de €160,00.

00:02:32

Estamos bem na gama

00:02:34

entre 11.000 e 20.000.

00:02:38

Portanto, não há realmente um valor de 13 encontrado,

00:02:41

mas o valor mais próximo mais baixo foi o

00:02:45

um dado pela fórmula e por si mesmo.

00:02:48

A opção que acabámos de discutir é

00:02:51

muito útil para criar uma espécie de fatia

00:02:54

e evite passar pelo

00:02:56

funções condicionais clássicas de si mesmo.

00:02:59

Agora vamos passar para um segundo

00:03:01

utilização do PROCV

00:03:03

Quero poder utilizar a seringa

00:03:07

verificando a folha de forma mais eficiente.

00:03:09

Uma maneira é inserindo um número de empréstimo.

00:03:13

E com isso automaticamente

00:03:15

Recuperando 4 informações

00:03:18

armazenados na lista de clientes.

00:03:21

O nome, montante, duração e pagamento mensal.

00:03:25

Poderei através do PROCH

00:03:28

para gerar uma fórmula que alimentará

00:03:31

as quatro informações sem

00:03:34

fazendo 4 fórmulas individualmente.

00:03:37

Mas primeiro terei que me transformar

00:03:39

minha lista em um modo de tabela.

00:03:41

Vou aproveitar esta oportunidade

00:03:44

para renomear meu empréstimo de mesa.

00:03:46

E depois colocar-me no primeiro

00:03:49

das quatro células, que está aqui.

00:03:52

Vou introduzir o meu PROCH e

00:03:56

então eu vou dizer que eu preciso do

00:03:59

valor que vou colocar em C6.

00:04:02

E que a informação será

00:04:05

encontrado na minha folha de lista de clientes.

00:04:08

Na minha coluna número Leona.

00:04:13

Com a matriz de retorno.

00:04:15

Poderei especificar a recuperação

00:04:18

dos dados encontrados no nome

00:04:21

para as colunas de pagamento mensal.

00:04:24

Em caso de incompatibilidade,

00:04:26

Posso muito bem informar o sistema

00:04:29

para fornecer uma resposta como desconhecida

00:04:32

número de empréstimo com o modo de correspondência.

00:04:38

Dessa forma, evitarei ter a resposta

00:04:41

não aplicável se introduzir um número baixo

00:04:44

Isso não existe com o modo de correspondência.

00:04:47

Vou pedir o exato usando zero.

00:04:51

Voltarei à verificação

00:04:54

e obtenha automaticamente um

00:04:56

Resposta de número solitário desconhecido.

00:04:59

Se eu inserir um número de empréstimo encontrado

00:05:01

na lista, como B para 1:50.

00:05:08

Eu receberia todos os itens correspondentes,

00:05:11

mas haverá um problema

00:05:13

com a colocação.

00:05:14

Vou aproveitar esta oportunidade para mostrar

00:05:17

e use uma fórmula chamada =TRANSPOSE

00:05:20

que permite transformar elementos que

00:05:22

são mostrados em linha na forma de coluna.

00:05:30

Portanto, isso funcionará como uma fórmula matricial

00:05:33

que me permite com uma única fórmula

00:05:35

para recuperar mais informações.

00:05:37

Digamos que no caso.

00:05:39

Insiro parcialmente um número de empréstimo.

00:05:42

Por exemplo, F1.

00:05:43

Inevitavelmente teremos a mesma mensagem,

00:05:46

número de empréstimo desconhecido.

00:05:50

Há um parâmetro adicional a definir

00:05:52

Na função PROCH

00:05:54

Adicione o modo de correspondência.

00:05:56

Como você pode ver, você pode ativar diferentes

00:05:59

opções para obter resultados diferentes.

00:06:02

Por exemplo, se eu colocar um F e uma estrela,

00:06:04

o sistema recuperará o primeiro

00:06:06

empréstimo respondendo à minha entrada.

00:06:08

Assim, o F e a estrela poderiam ser os primeiros

00:06:11

número de crédito com F assim 157 ou 196.

00:06:15

Como chego lá?

00:06:17

Gostaria de informar

00:06:20

#2 Correspondência de personagens Wild Card.

00:06:23

Então vamos tentar e vamos aqui, Simon,

00:06:28

Pronto

00:06:30

F 157 Simão.D. Portanto, este é outro

00:06:34

característica interessante com o

00:06:36

PROCH eles usam a opção genérica.

00:06:39

Agora vamos passar para outro exemplo de

00:06:42

as opções avançadas encontradas na

00:06:45

Função PROCH na minha folha de lista de clientes.

00:06:48

A lista de números baixos é

00:06:51

organizados cronologicamente a partir de

00:06:53

do mais antigo ao mais recente.

00:06:55

Os empréstimos com os seus montantes variados têm

00:06:59

com 24, 26 ou 48 meses.

00:07:05

Quero na minha folha de consulta

00:07:07

para recuperar o número da seringa

00:07:09

empréstimos mais recentes entre todos os

00:07:12

empréstimos no prazo de 24 meses.

00:07:14

Vou usar a mesma fórmula.

00:07:16

Então vamos aos checkins e vamos aqui.

00:07:19

No entanto, vou passar pela caixa de diálogo.

00:07:23

Então eu vou aqui.

00:07:25

Os argumentos da função e eu serei

00:07:27

capaz de especificar isso a partir do comprimento,

00:07:30

O Excel terá que pesquisar em todos os

00:07:32

comprimento na coluna E e envie-me o

00:07:35

número do empréstimo que figura na coluna A.

00:07:40

O caso não for encontrado não é obrigatório.

00:07:43

Se eu deixar em branco, então

00:07:45

o valor do erro será Na.

00:07:47

Em termos de modo de jogo.

00:07:49

Vou pedir-lhe que me encontre

00:07:50

o exato com zero.

00:07:52

No entanto, no modo de correspondência, se você

00:07:55

pode ver que eu não tenho as escolhas

00:07:58

disponível como fazemos com a barra de fórmulas.

00:08:01

Então eu vou aconselhá-lo a passar

00:08:03

a ajuda nesta função assim

00:08:05

você pode ter a explicação.

00:08:07

Vou usar menos um.

00:08:10

Porquê clicar aqui?

00:08:13

O sistema diz-me que eu

00:08:14

terá uma correspondência exata.

00:08:15

Vou ter uma correspondência exata.

00:08:17

Se nenhum for encontrado,

00:08:18

devolver o próximo item menor,

00:08:20

o que usamos antes,

00:08:22

e lembre-se que os dois são os

00:08:24

um que usamos como curinga

00:08:26

para fins genéricos.

00:08:30

Não definimos a matriz de retorno

00:08:32

e isso é porque eu fiz uma bobagem

00:08:35

erro e me desculpe eu só vou aqui.

00:08:39

Eu vou aqui e o valor de pesquisa é

00:08:43

na verdade, aquele que fica com 24 anos,

00:08:46

então será B18.

00:08:51

Pronto. Portanto, a resposta é

00:08:57

B590. Então, se eu recapitular isso, o baixo

00:09:01

número que recebo por um empréstimo de 24 meses,

00:09:04

ou o mais recente para

00:09:06

essa matéria, é realmente ser 590.

00:09:14

Pronto. 24 então o PROCH

00:09:19

a função é, portanto, um verdadeiro avanço

00:09:21

em termos de funcionalidade comparada

00:09:24

ao PROCV que utilizámos anteriormente.

Mandarine AI: CE QUI POURRAIT VOUS INTÉRESSER

RELATED COURSES This tutorial is part of the following courses

1943 Views

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 Po...

Cours E-learning

ALL COURSES