Understanding Hlookup – A Beginner’s Guide
If you are interested in integrating a reference or lookup table in your spreadsheet application but you need guidance on how to effectively use it, this article is the right source of information. It is intended for the beginners who need to implement a way to search for data from a table given a particular lookup value. For your appreciation of one of the features of Microsoft Excel, the Hlookup function will be discussed below.
Hlookup follows the syntax – Hlookup(lookup value, range of cells, row index, range lookup). This function instructs Microsoft Excel to retrieve the exact or approximate data located in the range of cells, particularly at row index num, corresponding to the lookup value specified. The exact or approximate value is determined by the fourth parameter in the function. A true value means you are looking for an approximation while a false parameter means you need an exact match.
For better appreciation of the Hlookup function, you can open a spreadsheet and name it Lookup. We will store the lookup table in this spreadsheet. Put the row titles as Product Code for the first row, January for the second row, February for the third row, and so forth and so on, until you reach the last month, December. This table will then have 13 rows. Then, in the first row on the succeeding columns, write down some random product codes like bev12, milk01, etc. Write five different product codes. This means you will have six columns. After this, write down some data in the January row. These values will be for the sales of the product for the month of January. Fill up the cells until the last product code. Do the same for the succeeding months. Once you have completed the lookup table, open another spreadsheet and name it Sales.
Your Sales spreadsheet will now enumerate in the first column all the Product codes. Write down the product codes that you listed in the Lookup spreadsheet in this column. The other columns will be the months of the year. What you will do to apply the Hlookup function is to position the cell at the month of January at the row where the first product code is entered. At this position, enter the equal sign followed by the function Hlookup, an open parenthesis and the cell location of the first product code, followed by the range of cells for the lookup table, then the row index of January which is 2 and the word false as the last parameter. This will return the sale for the month of January for the product code presented in that row. You do not need to do the same for the rest. All you have to do is to copy the formula to the other cells. You have to note thought that there are permanent cell locations in the formula. Thus, a dollar sign ($) should be inserted in the cell location so that the location will not change.
If you still need to better appreciate the use of Hlookup, you can always look for other useful resources online and explore the many features and functions of Microsoft Excel.
