February 23, 2015
While working on Excel we usually make a lot of vertical tables and therefore end up using VLOOKUP. But there are times when we do have horizontal tables as well, so today we shall learn the function of HLOOKUP.
HLOOKUP is generally used when your data is in horizontal format. HLOOKUP searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array.
The HLOOKUP() function has the following syntax:
=HLOOKUP(lookup_value, table_array, row_index_number,[range_lookup])
Follow these simple steps for learning the function of HLOOKUP
1. In our example, we want to know the location of all the employees in row 11 as well, so we shall use the HLOOKUP formula to know the same.
2. The first thing that you need to input in the formula of HLOOKUP is lookup_value. It is generally the data that you want to look up. It can be a value or a cell reference; in our case it will be cell reference that is B10.
3. The next thing that you need to input in the formula is table_array. Table_array is the range of cells from which we require our data. In our case it is B1:G2. While inputting the table array you should see to it that your cells are locked otherwise when you drag your fill handle to fill the data in other rows it may show N/A (not applicable).
4. Next thing in the formula is row_index_num. It is the row number where our value is situated. In our case we have selected two rows that is 1 and 2. So Row 1 remains 1 and Row 2 remains 2. Since the locations are mentioned in Row 2, we shall input 2 as row_index_num.
5. The next in the formula is range_lookup. This determines that whether you are looking for an exact match (False) or an approximate one (true). This is optional and in our case we shall use False as we are searching for an exact value.
6. When you press enter you will see that you have received the desired result.
7. To fill the data in other rows as well just drag the fill handle towards your right and your data shall be filled. Make sure that your table array cells are locked (see step 3) otherwise this function may not work.
If you think that this article has helped you in some way or the other, then don’t forget to share it with your friends and if you have any suggestions or queries then you can let us know in the comments box below.