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
  • 1034 views
résultat(s)
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.

No elements match your search in this video....
Do another search or back to content !

 

résultat(s)
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.

No elements match your search in this video....
Do another search or back to content !

 

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
  • 787 Views
ALL COURSES

Reminder

Show

/

Join Live Training Session