By making use of the Microsoft Excel program we can use a variety of functions that allow us to enjoy this tool to the fullest while making our work easier. If you are looking for a little help with Excel and want to know more about these functions, this article is ideal for you, because today in Learn How to Do It we will teach you how to use the ‘HLOOKUP’ function.
How to Use the ‘HLOOKUP’ Function in Excel and Make My Work Easier? – Using Functions in Excel
Index( )
- What are the limitations of the ‘HLOOKUP’ function in Excel?
- You can only use it to search for data in a selected row
- Searching on numeric values that are out of order is not allowed
- Characters having a certain pattern are prohibited
- How do I use the ‘HLOOKUP’ function in Excel?
- To select the range of row items
- Check the characters to search for to optimize the function
- What are the reasons why I can’t find anything in my search?
- Syntax error of the data or in the writings in the search
- Check the indices of the range in which you search
- Some numeric data is repeated or out of order
What are the limitations of the ‘HLOOKUP’ function in Excel?
As these are Excel functions, we will find many specifically designed for specific purposes and limited to a single use, such as inserting an image into a cell. It is no different in the case of the HLOOKUP function, which works with the following:
You can only use it to search for data in a selected row
The HLOOKUP function can only be used to search and find values in a certain row of bristles, arranged horizontally in the table to which they belong.
Searching on numeric values that are out of order is not allowed
To use the function correctly you must ensure that the numeric values in the range are in the proper order, that is, starting with the smallest and ending with the largest digits . Otherwise, HLOOKUP will return an error code.
Characters having a certain pattern are prohibited
Excel contains some special characters, such as wildcards, that are used within specific patterns for various functions, such as Query. But the HLOOKUP function is not part of these cases, since it strictly prohibits such characters.
How do I use the ‘HLOOKUP’ function in Excel?
The HLOOKUP function must be used precisely, or else it will not work as it should, throwing errors or incorrect results . Thus, the correct forms would be:
To select the range of row items
HLOOKUP is frequently used to locate, for example, values of certain products organized together with many more in large tables , with the aim of finding them in simple steps without the need to waste time locating them manually.
Likewise, it can be used to select a range of elements and transfer them to another list, avoiding the process of creating it all over again. This is different than exchanging data between two rows.
Suppose you have to work based on a horizontal list of products that records the code, description and existence , in that same order, of each of them and your goal is to create a vertical list with them.
First, you must locate yourself in the second cell of the first row of the vertical list that you are going to create, which will be designated to the description of the product , which in the horizontal list corresponds to the code of the first product.
Continue typing HLOOKUP and wait for the syntax of this function to appear, which would be (lookup_value, lookup_array_in, row_indicator, [sorted]) , where the lookup value would be the product code, so you must select the cell of the vertical list where find.
Then, you’ll have to add the array_look_in element , for which you’ll have to fully select the first list and press F4 to fix its cells. This step is necessary so that the function does not fail when applied.
The next element is the row indicator. As its name indicates, it is time to select the row of the list that you have as a base, which would be the horizontal one, where the product description is located, because to search for it is that you will use the function.
The syntax requires that after indicating the row number and placing another comma, comes a [computer] , which in the HLOOKUP function refers to the true value or the false value. You will have to type FALSE, to get the exact match you are looking for, close with a parenthesis and complete the function with an Enter.
This will give you an Excel formula, which is nothing more than the description corresponding to the product of the code you selected earlier. And to extract the description of the other products, all you have to do is copy this formula , select the other cells in the column and paste it over them.
To continue with the existence of these products, you will have to apply the formula again, replacing the row indicator with the number corresponding to the row where these values are located (in this case, it would be a three), and everything else would follow the same. same way.
Check the characters to search for to optimize the function
Remember that for the function to work correctly and give you the most accurate results, it is necessary that all the characters of the syntax are located correctly.
From the lookup value and cell fixing, to the number of the selected row and the false value at the end, all need to be checked and applied perfectly, or you’ll risk getting wrong values and ruining your work .
What are the reasons why I can’t find anything in my search?
If the search in Excel does not give you any results, it is most likely that you have made mistakes that are preventing you from taking advantage of this function, such as:
Syntax error of the data or in the writings in the search
As we already mentioned, an error in the syntax data or the values that are part of the search can give you erroneous results and even prevent them from being obtained. To fix this, you can also try the following:
Check the indices of the range in which you search
Make sure that all the indices in the syntax are correct. The commas and parentheses , in this function, are the ones you should check for. Also, it can happen that:
Some numeric data is repeated or out of order
Another fact to take into account is that for the HLOOKUP function, numerical values must not be repeated, so you will have to eliminate them if they are duplicates , and as we have already said, they must be ordered from least to greatest, so that the searches are execute in the best way.