There are times when you would want to get specific data from a given row in a table. Doing this manually can be hard and tedious, and time-consuming at the same time. You need a formula that will help you easily lookup and retrieve the data, and the Excel HLOOKUP function is the formula that will help you with this. If you are not privy to the use of the Excel HLOOKUP function, then this post is for you.
Excel HLOOKUP Function
=HLOOKUP(value, row_index, [range_lookup])
The formula has four arguments, and let us look at each of the argument and what it means in the function:
· Value: this specifies the value to look up;
· Row_index: Number of a row from which we want to retrieve the data;
· Table: the table where the data is to be retrieved;
· Range_lookup: this indicates the exact match or approximate match. This argument is not mandatory in the function as you will still get the right answer even without specifying it. if you want to get an exact match of what you are looking for, you can use FALSE or 0 in this argument. But if you just want to get an approximate value, you should use TRUE or 1. Ensure that you sort the lookup value row in an ascending order.
The Excel HLOOKUP Function works by looking for a specified value in the first row of a given table. It then returns another value in the same column in the same table.
This function is critical especially for people that deal with large volumes of data, and want to retrieve specific data for a certain date. To understand how the Excel HLOOPUP works better, we should consider a few examples.
The first example that we are going to look at is the sales total of a given region. The table below contains data for the regions under study.
Figure 1. Using Excel HLOOKUP function to find specific sales
In the diagram above, we can find the exact sales for Indiana using the Excel HLOOKUP as below:
Note that B5 is the cell that contains the value we are looking for, B2:F2 indicates the table from which we want to retrieve our data, 2 shows the row number while the FALSE is to help us get the exact match of the sales we are looking for.
Figure 1. When you press “Enter,” you will get the exact sales for Indiana.
There are several cases of usage of this function. The most common cases are when one wants to find the sales of a given region as we have done in the above example. Also, this formula can be used if one wants to find the interest rate effected on a specified date.
While this function is incredibly important, it also has some traps. The most common disadvantage of this function is that there is a time when it can be too slow. This makes the reason using it in order to quickly locate a certain value meaningless. But for better results, you can apply the following measures:
· Use the approximate match instead of exact match with a row that has been sorted in an ascending order.
· Deploy the MATCH or COUNTIF function to establish whether or not the value is in the first row first, before using the Excel HLOOKUP function.
In conclusion, the use of the Excel HLOOKUP is very essential in many cases, and thus one has to know how it is used. If you have read this article to this point, there is no doubt that you can now use this function without much trouble.