The Hlookup function allows you to scan a row from left to right in search of a value and then return the contents of a cell that is in the same column but below that row.
This is a "horizontal lookup" and works like the Vlookup or "vertical lookup" except that the Hlookup searches left to right instead of up and down.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Argument |
Description |
Lookup_value |
The value that you search for in the first row of the table. This is what you use to locate the correct column from which to retrieve the data. This can be text, numbers, a cell reference, or basically whatever you want. |
Table_array |
This is the range reference where all of your data is located. Note that the first row of this range is where the Hlookup function will search for the lookup_value. |
Row_index_num |
The number of the row from which you want to return a value. 1 is the very first row in the table_array. |
[Range_lookup] |
Optional argument. Can be either TRUE or FALSE. This says if the Hlookup function should look for an exact match to the lookup_value or an approximate match. TRUE is the default value if you don't input anything here and it means an approximate match. FALSE is for an exact match. Exact match means that, well, an exact match to the lookup_value is the only thing that will return a result. If it is TRUE, an approximate match, the Hlookup function will try to find the lookup_value but, if it can't, then the next highest value will be matched - think about numbers that are sorted lowest to highest (example below). |
[] means the argument is optional.
Here, we have a basic store sales setup and we want to get the January sales in cell B7.
(don't forget to download the accompanying workbook so you can follow along)
If you want to search a list of numbers and return a specific number or the next highest value, you can do this with the Hlookup function.
Here is our sample data set for this example:
This sample will use the Hlookup to find the price of an item, let's say for shipping, based on its weight. If the item is between 0 and 5 pounds, it costs $2.50 to ship; between 5 and 10 it costs $7.75 to ship; and 10 pounds and above it costs $15 to ship.
In order for this to work, the list must be sorted in ascending order with the lowest number on the left and the highest number on the right.
We input the Hlookup function just like above, except, this time, we either input TRUE for the last argument or leave it blank:
If we use nothing for the weight, we get 2.5:
This happens here instead of an error because we did not put FALSE for the last argument of the Hlookup function and because the value in cell B3 is 0, which is considered the same as being empty in this case.
Now let's try some numbers in cell B6 and see what happens:
This should give you an example of how the Hlookup function will perform in this situation.
The Hlookup function works just like the Vlookup function except that the Hlookup goes left to right instead of up and down.
This function seems confusing at first but, trust me, once you use it a few times, it will be as easy as using the SUM function.
Make sure to download the accompanying workbook so you can follow this tutorial and see these Hlookup functions in action in the spreadsheet.