Excel - XLOOKUP (Basic metrics) Video

In this video, you will learn about how to search and find what you need using Microsoft 365. The video covers the usage of OneDrive, Yammer, and Delve to help you locate and access documents, collaborate with colleagues, and stay updated on the latest information.
This will help you improve your productivity and efficiency in finding relevant content within the Microsoft 365 environment.

4:19
2099 views

Objectifs :

This tutorial aims to teach users how to implement the HLOOKUP function in Excel, highlighting its advantages over the traditional VLOOKUP function. Users will learn to retrieve data based on a common item found in another table, specifically focusing on how to search for quantities using order numbers.

Chapitres :

Introduction to HLOOKUP
Welcome to this tutorial on the HLOOKUP function in Excel. In this session, we will explore how to effectively implement this function, which has advantageously replaced the historical VLOOKUP. The primary goal of any lookup function is to retrieve necessary data that is not readily available.
Understanding the Lookup Process
In our example, we will search for the quantity of an item using a common identifier, the order number. This common item is located in the order details, specifically in column E. We aim to transport the quantity data to complete column H.
Differences Between VLOOKUP and HLOOKUP
Previously, when using VLOOKUP, the common column had to precede the column from which data was being retrieved. For instance, the order number had to be positioned before the quantity column. However, with HLOOKUP, this restriction no longer applies, allowing for greater flexibility in data retrieval.
Implementing the HLOOKUP Function
To begin using the HLOOKUP function, navigate to the orders table. You can find the HLOOKUP function either through the formula bar or by using the insert function button. It can be located in the 'most recently used' or 'lookup and reference' categories. Once selected, you will need to fill in the various parameters.
Filling in Parameters
The first parameter is the search value, which in this case is the order number (e.g., 10,954). The lookup array parameter should specify the column containing the order numbers, which is column E in our example. The returned array parameter will indicate the column from which we want to retrieve the quantity data.
Handling Errors and Match Mode
The fourth parameter, 'if not found', allows you to specify a message that will be displayed in case of a mismatch. For this example, we will personalize it to say 'item not found'. The match mode is crucial and should typically be set to zero, indicating that we are looking for an exact match. Additional options for match mode will be covered in a more advanced tutorial.
Conclusion
After filling in all the parameters and validating the function, the results will display, including the personalized text for any mismatches. This tutorial has demonstrated how to set up the HLOOKUP function simply and effectively, enhancing your data retrieval capabilities in Excel.

FAQ :

What is the HLOOKUP function in Excel?

The HLOOKUP function is used to search for a value in the first row of a table and return a value from a specified row in the same column. It is particularly useful for horizontal data arrangements.

How does HLOOKUP differ from VLOOKUP?

HLOOKUP searches horizontally across rows, while VLOOKUP searches vertically down columns. HLOOKUP is advantageous when your data is organized in rows rather than columns.

What parameters do I need to fill in for HLOOKUP?

You need to specify the search value, the lookup array (the row to search), the returned array (the row to retrieve data from), and optionally, the 'if not found' message and match mode.

What should I enter for the match mode in HLOOKUP?

In most cases, you should enter '0' for the match mode to indicate that you are looking for an exact match of the search value.

What happens if HLOOKUP cannot find the search value?

If HLOOKUP cannot find the search value, it will return an error message unless you have specified a custom message in the 'if not found' parameter.

Can I use HLOOKUP for any type of data?

HLOOKUP is best used for data organized in rows. It is suitable for retrieving information such as quantities, prices, or other related data based on a common identifier.

Quelques cas d'usages :

Inventory Management

In an inventory management system, HLOOKUP can be used to quickly retrieve the quantity of items based on their order numbers. This allows for efficient tracking of stock levels and order fulfillment.

Sales Reporting

Sales teams can use HLOOKUP to generate reports that summarize sales data by order number. By linking order details with sales figures, teams can analyze performance and make informed decisions.

Order Processing

During order processing, HLOOKUP can help staff quickly find the quantity of items ordered by referencing the order number. This speeds up the fulfillment process and reduces errors.

Data Analysis

Analysts can utilize HLOOKUP to cross-reference data from different tables, such as linking customer orders with product availability. This enhances data analysis and reporting capabilities.

Customer Service

Customer service representatives can use HLOOKUP to retrieve order details based on customer inquiries. This allows them to provide accurate information quickly, improving customer satisfaction.

Glossaire :

HLOOKUP

A function in Excel that searches for a value in the first row of a table and returns a value in the same column from a specified row. It is used for horizontal lookups.

VLOOKUP

A historical Excel function that searches for a value in the first column of a table and returns a value in the same row from a specified column. HLOOKUP is considered an improvement over VLOOKUP for certain use cases.

Lookup Array

The range of cells that contains the data to be searched. In the context of HLOOKUP, it refers to the row where the function will look for the search value.

Returned Array

The range of cells from which the function retrieves the value to return. In HLOOKUP, this is the row from which the corresponding value is taken based on the search value.

Match Mode

A parameter in the HLOOKUP function that specifies how the function should match the search value. A value of zero indicates that an exact match is required.

Error Message

A notification that appears when a function cannot find the specified value. In HLOOKUP, this can be customized using the 'if not found' parameter.

Order Number

A unique identifier assigned to a specific order, used as a reference in order details and lookup functions.

Quantity

The amount of items associated with a specific order, which can be retrieved using the HLOOKUP function.

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.

00:00:03

Bem-vindo. Neste tutorial vamos

00:00:05

Veja em conjunto como implementar

00:00:07

a função PROCH.

00:00:09

Para sua informação,

00:00:10

O Excel substituiu vantajosamente o

00:00:13

vlookup histórico com este.

00:00:16

Em termos gerais, o objetivo de qualquer pesquisa

00:00:18

função é recuperar os dados de que precisamos,

00:00:21

mas não temos. No nosso exemplo,

00:00:24

Vamos procurar a quantidade.

00:00:27

Através de um item comum encontrado em outra tabela.

00:00:31

Este será o número da ordem.

00:00:34

Este ponto comum é apresentado em

00:00:36

os detalhes da ordem, como eu disse antes,

00:00:39

na coluna E.

00:00:42

Assim, nestas duas tabelas

00:00:46

para ser ligado aqui, na coluna E queremos

00:00:49

para transportar a quantidade para que possamos

00:00:52

coluna completa H. Como você pode perguntar?

00:00:55

Se você já usou o vlookup antigo,

00:00:59

era obrigatório que na tabela onde

00:01:02

você deseja recuperar as informações,

00:01:04

Detalhes da encomenda neste caso.

00:01:07

A coluna comum teve de ser colocada

00:01:11

antes do transporte da coluna.

00:01:13

Então isso significa aqui. Além disso,

00:01:17

algum tempo atrás no Excel o número da ordem,

00:01:21

tinham de ser posicionados antes da quantidade

00:01:24

coluna de modo que a coluna A ou B para

00:01:26

ser capaz de a função funcionar e

00:01:29

transportar os dados com o Hlookup

00:01:31

Isto já não é um problema,

00:01:33

então para passar pelo Hlookup I

00:01:36

vai para a mesa, a tabela de pedidos.

00:01:38

Então vou procurar o

00:01:41

Função de proch.

00:01:42

Usando a barra de fórmulas

00:01:45

ou o botão Inserir função.

00:01:48

Eu poderia encontrar o Hlookup

00:01:51

No período utilizado mais recentemente ou na seringa

00:01:55

a categoria de pesquisa e referência.

00:01:58

Como eu usei recentemente.

00:02:00

Eu posso encontrá-lo no mais recentemente usado.

00:02:03

Eu valido.

00:02:06

E eu só tenho que preencher

00:02:08

os diferentes parâmetros.

00:02:09

Irá notar que a seringa

00:02:11

os parâmetros são bastante numerosos.

00:02:12

Vamos nos concentrar nas características básicas

00:02:14

do PROCH neste tutorial.

00:02:17

O primeiro passo será

00:02:19

especificar qual seria o elemento

00:02:21

considerado como o valor da pesquisa.

00:02:23

Como mencionado anteriormente,

00:02:24

será o número da encomenda

00:02:27

como é o elemento comum.

00:02:29

Por conseguinte, quero recuperar a quantidade

00:02:33

referente ao número de ordem 10.954.

00:02:36

O parâmetro da matriz de pesquisa

00:02:39

é preenchido especificando a seringa

00:02:41

da tabela a ser vinculada.

00:02:44

Ou seja, aquele que contém

00:02:47

todos os números de ordem.

00:02:48

Assim é a coluna E no nosso

00:02:52

exemplo de detalhes da ordem.

00:02:53

O parâmetro de matriz retornado

00:02:55

será sempre o único a contar

00:02:57

eu na mesa em que eu tenho

00:02:59

recuperado a informação,

00:03:00

qual é a coluna a ser

00:03:03

transportado para a outra mesa.

00:03:05

Como estabelecemos

00:03:06

antes será quantidade.

00:03:08

Por isso, volto aos detalhes da minha encomenda

00:03:11

e clico em quantidade.

00:03:13

O parâmetro if not found ou 4th

00:03:15

permite especificar o texto ou

00:03:18

a informação que irá

00:03:21

ser mostrada em caso de incompatibilidade.

00:03:23

Se você não sentir isso,

00:03:24

Haverá uma mensagem de erro

00:03:27

e um que já conhecemos.

00:03:30

Vou personalizá-lo neste

00:03:33

exemplo colocando item não encontrado.

00:03:37

O modo de partida é um elemento importante

00:03:41

parâmetro em que você deve absolutamente

00:03:44

e, na maioria dos casos, preencher com um zero

00:03:47

como diz para a função que você

00:03:49

estão à procura de uma correspondência exata.

00:03:52

As outras opções adicionais em

00:03:54

O modo de jogo será explicado

00:03:56

na função de pesquisa avançada

00:03:59

tutorial uma vez que eu validá-lo.

00:04:04

Tudo será mostrado,

00:04:06

incluindo o texto personalizado.

00:04:10

E é assim que você pode de forma simples e

00:04:13

definir efetivamente uma função PROCH.

Mandarine AI: CE QUI POURRAIT VOUS INTÉRESSER

RELATED COURSES This video is part of the following courses

1815 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

Cours E-learning

ALL COURSES