HLOOKUP function in Excel – complete instructions for use with examples

Many Excel users know the function VPR, which is designed to search for the necessary content in an Excel document or sheet and display it in a separate cell. But few people know that there is another function that has similar functionality, but a number of features that distinguish it from other similar functions. This is a function GPR. Let’s look at it in more detail.

HLOOKUP function – detailed description

According to the syntax and principle of operation of the function VPR и GPR very similar. It searches for a value in a certain data array, and returns the desired value, which is a few lines below. In simple words, it is a mirror analogue of the function VPR, which views the data horizontally rather than vertically. 

Let’s say we have a spreadsheet that has two columns named “Name” and “Date of Birth”. We are faced with the task, based on these data, to obtain the date of birth of the third employee from the top. In this case, the syntax would be:

=GPR(«Date of birth»;A1:B10;4)

Naturally, you need to put your own name for columns, columns, and so on. The only thing you need is to leave the last argument if you want to search for a specific account employee. 

Syntax APR functions

Since the function GPR not as popular as VPR due to the fact that the usual form of data presentation is vertical, many do not know the syntax either. But it is not much different from the one that can be found in the function VPR.

=HLOOKUP(lookup_value, table, line_number, [scan_lookup])

Let’s look at it in more detail: by arguments, and also consider some of the nuances when using this function. 

Arguments

Let’s take a closer look at each of these arguments:

1. The desired value. This argument must be specified without fail. This is the data that we need to search horizontally. Formats can be different: text, link or number.
2. Table. In simple words, an array of data to be searched. There are several options for how this parameter can be written: as text, a number, or a boolean value. You can also specify both a range name and a reference. It is important to consider that if a text format string is used, then it does not take into account whether the letter is large or small. Sorting values ​​in the desired range is carried out in the same way as we read a book. That is, from left to right. 
3. Line number. This specifies the line number from which the value will be obtained. The minimum value of the row number that can be used in this formula is 1. If you specify a smaller value, the #VALUE! error will be returned. You also need to make sure that the number of lines in the last argument is not less than in the third argument. If allowed, the result will be #REF!
4. Interval viewing. This argument is optional. Contains a Boolean value that tells Excel whether to use an exact match or an approximate match. In the case of a true value, the function looks for similar matches, but not complete ones. If the value is false, then the function searches for exactly the same value that was specified by the user. If the lookup fails, a #N/A error is returned.

Notes

When working with the function GPR you need to pay attention to the following nuances:

1. If the function fails to successfully search for the desired value, and the interval lookup argument is TRUE, then it searches for the largest value that is less than or equal to the desired value. 
2. If you specify a search value that is less than the minimum value in the specified range, the function returns a #N/A error.
3. If the “interval scan” argument matches the value LYING along with the text format of the “value to look up” argument, it is possible to use wildcards to indicate an approximate match. For example, a question mark replaces one character, and an asterisk replaces any number of characters. And the tilde sign allows you to find these characters.

Features of using the HLOOKUP function

In general, the main nuances of using this function have been described above. I would like to dwell in more detail on the differences from the function VPR. How to understand which function should be used? If it is necessary to compare the values ​​in the left column from the desired data, then the function is intended for this purpose. VPR. If in the lines below – GPR. As can be understood from the abbreviation, the first letter indicates the type of view. V is vertical, D is horizontal.

In fact, each search function is unique and designed to perform different tasks. That is, the functions GPR и VPR are not the only ones. Let’s take a closer look at what search functions exist in general and compare them with each other. All of them are similar in purpose, but may differ in syntax and features that they provide. They can also be combined in various combinations, giving similar functionality, but with some features. 

1. SEARCH. This is the simplest function that searches for a given string in a case sensitive manner. At the same time, it is not able to find, for example, the second, third, and so on occurrences, limiting itself exclusively to the first. There is also a similar function called SEARCHB. Its main difference is that it focuses not on the number of characters, but on the number of bytes. But in Cyrillic languages ​​there is no such need. Also in this function it is possible to use wildcards like ?, *. If you need to search for a specific question mark or asterisk, then you need to put a tilde sign in front of this character. If the desired value cannot be found, then the #VALUE!
2. TO FIND. Essentially, a function duplicating SEARCH with the only exception that it is case sensitive and does not support wildcards. 
3. INDEX. The syntax of this function is very different from how we will use it. And the range of possibilities is truly enormous. This includes obtaining the necessary data by number, and extracting the necessary information from a two-dimensional range, and the ability to search exclusively in a specific table, if there are several of them. In addition, it makes it possible to directly link to the desired column or row, as well as a cell.
4. MORE EXPOSED. This is another function that can be used for different purposes with different syntax. It is used to search for the position of a cell with a specific text. The user can specify exactly what text will be searched. In addition, this function is often used to search for the first or last cell, as well as search by date. For example, in order to determine the stage of the project.
   Often a function MORE EXPOSED used in conjunction with INDEX. In this case, it acts very similar to the function VLOOKUP (not APR) way, searching for the desired data vertically. In particular, it repeats the interval lookup functionality as in VPRAnd in GPR, so it is often used to replace their functionality. At the same time, the set of features is even wider compared to GPR, since it allows you to choose the largest or smallest closest match (when it comes to searching for numbers). True, the syntax of this function is also more complicated.

A detailed consideration of each of these functions is the topic of a separate article. But in general, we see that each of the seemingly similar features actually deserves to be in a completely different niche. 

HLOOKUP for selection by several conditions

Suppose we have such a beautiful plate, and we need to get data about the client and phone, based on his ID. 

 To make it easier to use the function, let’s create a drop-down list in cell E2. To do this, you need to use the tool “Working with data – Data validation”. It can be found under the “Data” tab. 

Next, we need to make a list in the appropriate places (where the client ID will be indicated). To do this, you need to find the “List” item in the list with data types. And in the field signed as “Source” write the range of addresses of the cells of the first column. 

As we can see, in the column where customers are described, the following formula was written:

=ГПР(B1;A1:C11;E2+1)

With its help, the program automatically issues a client based on its identification number. 

To search for a customer’s phone number based on their ID information, you can use this formula. As you can see, it allows you not only to determine the phone number, but also to check if it is listed in the database.

=ЕСЛИ(ГПР(C1;A1:C11;E2+1)=»»;»Не указан»;ГПР(C1;A1:C11;E2+1))

Let’s write this formula in more detail. Using the function IF we are checking the value that is returned in the first cell (the one where the identifier is written). If it turns out that there is no number, then a message is returned that the client did not specify it. 

Here are some simple examples that you can use for more clarity.

Interactive report for profit and loss analysis in Excel

Suppose we have a table containing information about income and expenses for different months. We have been tasked by management with adding a small form to this document that can be used to determine the trade balance. In simple words, we made a profit or loss for this period. 

We have created a table where the number of the month can be selected using the drop-down list. You can learn more about how to create a drop-down list in the instructions above. 

After that, we write the following sequence of commands in cell F2 (it is displayed in the screenshot in the formula bar).

Using the ABS function, we got the absolute value. Its value is the same as the difference between the results of the two formulas GPRthat have been returned.

Now let’s write down the formula from the screenshot below.

As you can see, the formula contains a function IF, which compares the results of calculating two formulas, and if the first one is greater, it writes that the day ended with a profit.

Examples of using the HLOOKUP function

For clarity, let’s give another example, a little simpler. Suppose we were given the task of finding the number of a customer numbered 5. This table with the initial data that will be used in the calculations looks like this.

Next, we use our formula with the arguments given in this screenshot.

Let’s describe in more detail what each of the above arguments means.

1. A1 is a link to a cell that contains the name of the table field.
2. A1:C11 is the source table that is used to retrieve the information.
3. E2+1 – digital designation of the table row, taking into account the header. If you do not add one, then data will be displayed that are based on the wrong ID, which will be one less. The thing is that the first row of our table is reserved for a header containing the names of the columns.

As a result, we get such a table. We see that in this example, the ID number is cell F2. That is, the result corresponding to the number of this client is displayed in this cell.

Thus, there are really a huge number of opportunities for finding the desired values ​​in Excel. And with each new version of the office suite, this set only increases. What’s next for us? Unknown. But you definitely need to be sure that everything will be fine. After all, as finance experts say, Excel and Powerpoint are the two most popular and effective programs for generating money. If you learn how to use spreadsheets correctly, then a specialist will be incredibly in demand, regardless of the area in which he works.