As the name suggests, the HLookup function searches in data that is arranged horizontally in rows or in a range of rows. The constraint is that the topmost row must contain the item we are searching information about. In our case the topmost row must contain the individual part numbers.
To use HLookup, locate an empty cell preferably below the range of data in your spreadsheet. This is the cell where you will input the name of your part (note – the parts are in the topmost row of your spreadsheet).
Now in another empty cell preferably the adjacent cell on the same row you need to use the HLookup function that will extract the sales details for your part from your data sheet. You could also have one HLookup function for every month of the year so that you could see the sales for that part for every month of that year.
Suppose the cells A2, A3, till A13 contain the names of the months January to December, cells B1, C1, D1 till CZ contain the part numbers. The range B2 till CZ13 contains the sales details of all the parts of all the months. So the data range from B1 till CZ13 is the table array i.e. including the topmost row that contains the part numbers where the sales figures will be extracted.
Now let us assume B15 is the empty cell where you will input the part number. In D15 we use HLookup to extract the sales for January, E15 for February and so on for the various months till December.
Now let us take a look at the format of the HLook function which is HLookup (“text” or cell reference, table array, row index number, range lookup value).
Let’s look at the various parameters one by one.
1. Text value or cell number – Use the text option when we already know what part we are searching for we could input that value in quotes. e.g. HLookup (“NY-001″….). In our case since we will be inputting a value in the cell B15 we will be using the cell option as HLookup (B15…)
2. Next the table array. We have already defined this to be B1 till CZ13. Note that while creating the formula in a cell we can use the mouse pointer to drag and define the array too and it is automatically inserted into the function. The function then becomes HLookup (B15, B1 CZ13,…)
3. Row index number – This number is relative to the table array. For e.g. suppose we want sales of January which is row number 2, i.e. the sales of parts in January are in the second row of the table array B1 to CZ13. Sales for February in the third row and so on. The function now becomes HLookup (B15, B1 CZ13, 2..) for January, HLookup (B15, B1 CZ13, 3…) for February and so on.
4. Range lookup value (True/False) – This parameter is optional. When omitted it defaults to True.
This parameter checks to see if we need to match in our case exact names of parts. So we need to use the “False” parameter since we will also have text in our part-names. True can be used if we had only numbers.
So the final form of our HLookup function will be HLookup (B15, B1 CZ13, 2, False) for cell D15
for sales for January, HLookup (B15, B1 CZ13, 3, False) in cell E15 for sales for February and so on. Insert them into the cells accordingly.
To find out the sales for any part simply enter the part in the B15 cell and the sales is automatically computed in the fields D15, E15 and the others for the months January till December.
Note: From version 2007 onwards instead of inserting the HLookup function directly into a cell you can also do it via the menu. Click Formulas and click on Lookup and Reference from the ribbon. A drop-down menu opens from where you can select the HLookup function. Selecting it will open a dialog where you can enter or select values for each of the parameters discussed earlier.
Article Source: http://EzineArticles.com/6471706
Recent Comments