HLOOKUP, VLOOKUP and XLOOKUP, essential Excel functions

Today we’re tackling the most famous duo since the sickle and the hammer: the HLOOKUP, VLOOKUP functions in Excel. Widely used in business, they must be perfectly mastered. Used correctly, they allow certain tasks to be automated and therefore offer considerable time savings.

Why are Excel’s HLOOKUP, VLOOKUP and XLOOKUP functions essential?

XLOOKUP and VLOOKUP

HLOOKUP and VLOOKUP functions in Excel are useful functions for searching and finding specific values ​​in data tables. The HLOOKUP function searches for a value in a vertical array of data, while the VLOOKUP function searches for a value in a horizontal array of data.

Using these Excel functions can be useful in various situations, such as finding a specific product in a product list or finding an employee in an employee list. These functions can also be used to check if a specific value exists in a data table, which can be useful in avoiding errors in calculations and data analyses. Using these functions, you can easily search and find specific values ​​in your data tables, which can be helpful in simplifying and improving your analyzes and calculations.

SEARCHX

Excel’s XLOOKUP function is essential because it allows you to search for values ​​in a table or range of data and return a matching value from a specified column. It’s useful for many common Excel tasks, such as managing databases, creating dashboards, creating reports and analyzing data… In short, this feature will be useful to you whatever as part of your internship or job.

By using the XLOOKUP function in Excel, you can quickly and easily find values ​​that match specific criteria, which can save you a lot of time compared to a manual search, as you can imagine! Additionally, the XLOOKUP function can also be combined with other Excel functions to create complex formulas that can be used to perform advanced calculations on your data. For example, you can use the XLOOKUP function to find an interest rate in a table, then combine that value with other data to calculate a monthly loan payment.

The HLOOKUP function in Excel

SEARCHH (HLOOKUP for English speakers) is an Excel function that searches for a value A in the first row of a table and displays the value B of a cell located in the same column as the value A, but in an umpteenth row of this painting. This is a horizontal search in the sense that it allows you to search for a value in the first row of a table and then extract a value from the same column.

The function is written

=SEARCHH(searched value A; table; line number where the value B is found; [valeur proche]).

Example of HLOOKUP function in Excel

From the table above, a horizontal search can, for example, from a manager’s name, return their age or salary.

So, if we want to display Lucie’s company, we will write: =SEARCHH(E1; A1:E7; 5; 0) :

• E1 corresponds to the sought value (Lucy here) ;
• A1:E7 corresponds to the table where the search is carried out (note that the value sought must necessarily be found in the first line) ;
• 5 corresponds to the line where the value we want to return is located;
• 0 corresponds to the approximation: here we are looking for an exact value.

=SEARCHH(E1; A1:E7; 5; 0) will return Hermes.

The function also makes it possible to automate this type of research by coupling it with other functions and tools of the famous Excel spreadsheet. Coupled with data validation and function EQUIVfor example, we can imagine a tool that returns any value from the table depending on the name of the manager and the requested criterion.

The VLOOKUP function in Excel

VLOOKUP (VLOOKUP for English speakers) is an Excel function that searches for a value A in the first column of a table and displays the value B of a cell located in the same row as the value A but in the nth column of this table . This is a vertical search in the sense that it allows you to search for a value in the first column of a table and then extract a value from the same row.

The function is written:

=VLOOKUP(searched value A; table; column number where the value B is found; [valeur proche]).

The fourth element, close value, is optional and can take the values ​​of 0 or 1. By default, it has the value 1. When it takes the value 0, the function will search Exactly the value A in the first row of the indicated table.

If it is left empty or takes the value 1, the function will return you the last value before the one which exceeds the value sought (that is to say as soon as the function finds a higher number or a word further away in the alphabetical order).

Example of VLOOKUP function in Excel

From the table you can display the discount applied to Toyota using the VLOOKUP function.

We will then write:

=VLOOKUP(A4; A1:E5; 3; 0) :

• A4 corresponds to the sought value (Toyota here) ;
• A1:E5 corresponds to the table where the search is carried out (note that the value sought must necessarily be found in the first column) ;
• 3 corresponds to the column where the value we want to return is located;
• 0 corresponds to the approximation: here we are looking for an exact value.

=VLOOKUP(A4; A1:E5; 3; 0) returns us 1% here.

Approximation in HLOOKUP and VLOOKUP functions

The fourth element of the function, the approximation, is optional and can take values ​​of 0 or 1 (FALSE or TRUE). By default, it has the value 1. When it takes the value 0, the function will search Exactly the value A in the first row of the indicated table.

If it is left empty or takes the value 1, the function will return you the last value before the one which exceeds the value sought (that is to say as soon as the function finds a higher number or a word which is found after in the alphabetical order).

In the example above, we have not specified the approximation: the value 1 (or TRUE) is therefore taken. Here, the HLOOKUP function will search for each of the numbers in the left column (between A4 and A14). As soon as it finds a number greater than the one sought in the search table (A1:C2), it will stop and return the value of the previous column.

So, when it looks for the value 3 in the first row of the table:

• The function starts with A1: 1 is less than 3 so it continues
• It continues with B1: 5 is greater than 3 so it stops
• Then it returns to the previous column, that is to say the first column
• Finally, it will look for the value in the 2nd line, that is to say “1 to 4”.

How to use the XLOOKUP function in Excel?

Here are the main key steps for using the XLOOKUP function in an Excel spreadsheet:

1. Select a cell where you want to display your search result.
2. Type “=XLOOKUP(” (be careful to put the quotation marks) in the cell, then add the following arguments in order:

• The value you are looking for (i.e. the value you want to find in the table).
• The table or data range in which you want to find the value.
• The column number that contains the value you want to return (for example, if you are looking for the name of an employee in a sheet that contains names, salaries and hire dates, you will need to specify that you want to return the value of the first column, which contains the names).
• Optional: Specify “FALSE” or 0 if you want to perform an exact search (i.e. find the exact value you specified) or “TRUE” or 1 if you want to perform an approximate search (i.e. i.e. find the closest value).

3. Press Enter to execute the function.

For example, if you want to find the salary of an employee named “Michael” in a table that contains names and salaries, and the table begins in cell A1, you can use the XLOOKUP function as follows: =XLOOKUP( “Michel”, A1:B10, 2, FALSE). This will look for the value “Michel” in the first column of the range A1:B10 and return the corresponding value from the second column (i.e. salary). Specifying “FALSE” means the search must be accurate.

Exercise on the HLOOKUP and VLOOKUP functions in Excel

Time to practice! To train and see if you are ready, we have put together a workout for you. You can download the exercise and its answer key here.

The HLOOKUP and VLOOKUP functions are essential. They can be coupled with other functions and tools which allow certain searches to be automated relatively quickly. However, they have many limitations:

• the value sought must be in the first row or column;
• the returned value must be in a row or column that is located After the value sought;
• We must necessarily know the reference of the row or column and adapt the formula if the reference is in the column or row of the returned value.

But don’t panic! These limits may be exceeded by the Excel Index and Equiv functions. This pair of functions is more suitable for more complex data tables and will adapt to all situations!

HLOOKUP, VLOOKUP, XLOOKUP and the need to master Excel in business

Excel is a business essential. It is therefore mandatory to master the HLOOKUP and VLOOKUP functions and other Excel functions in order to save valuable time in your daily tasks.

Some companies even require their employees to never use the mouse on Excel, but to have perfect mastery of all shortcuts and keyboard commands. This gives a more professional image, but also saves you time.

Companies also ask that you know how to freeze your shutters, insert documents in Excel, that you know how to compare two files, for example. If you are ever looking for perfect tips to shine in an internship or at a company, here are 10 Excel best practices that you absolutely must master.