When you start learning about the lookup functionality of Excel, HLOOKUP will probably be one of the first functions you will stumble upon.
Practice File:
Transcript
We will continue with the HLOOKUP function. In cell area B5 to J7 we can see the gas consumption and the cost in dollars, for certain distances calculated in miles.
For example, for a 75-mile journey we need 10 liters of gasoline which cost 13 dollars.
Let’s suppose we want to look up the consumption in liters for a 100-mile journey and show it in cell I11. In such a small pool of data it is relatively easy to find the proper value and manually type it in the proper cell.
Imagine if your data spanned to thousands of rows and columns. Finding out the needed value then could be impossible.
This is where Excel’s lookup functions provide us with the solution.
So in cell I11 we will use the hlookup function to get the value we need.
The hlookup function searches the data of the first row of the cell area B5:J7 for a value, in our case it is 100, and returns the value of the cell that can be found at the same column and at a given row of the cell area. In our case the column is the second one and so the value is 15.
We select the cell and navigate to the Formulas tab. We click at insert function and set the category to Lookup & Reference. We locate the HLOOKUP function and click OK. We can of course just type the name of the function after the equals sign.
At the arguments window, we set the value that we are looking for at the “Lookup_value”. We select the cell G11, which refers to the distance that we are looking for, the 100 miles.
At the field “table_array” we need to define the cell area where Excel will perform the search; this is the cell area B5 to J7. At the argument “Row_index_num” we define the row number where hlookup will look for the result of the function, so we type the value 2.
The last field contains only two options: TRUE & FALSE. We can type the number 1 or 0. If we type the number 0, Excel will only look for an exact match. In case there is no such value, Excel will display an error message. If we type number 1 and there is not an exact match, Excel will find the lower closest match in the first column. In our case it would be 75
We need to note that in order to use this function with the last attribute set to 1, the cell area needs to be sorted in ascending order by the row in which we want to look for the value. In our case by the row “miles”.
We type 0 and click at OK. As a result, the value 15 will be shown, which corresponds to 100 miles.
If we change the value of the cell G11 to 110 miles, we can see that an error message appears because there aren’t any values available for 110 miles.
The letter H at Hlookup stands for Horizontal. So, HLOOKUP looks for a value, in our case 100, at the top row of a cell area, and when it finds it, it returns the value that is found at a cell with the same horizontal position. Which is another cell at the same column of our data area.
In our example, we have asked for the second row, that’s why we had the result 15. In case we had searched for the third row, we would have had the result 19.5