Tom’s Tutorials For Excel: The HLOOKUP Function
HLOOKUP is a function not as easily grasped nor widely used as its VLOOKUP counterpart, probably for good reason as most lists are vertical in nature. The picture shows an example of using the HLOOKUP function to find a lookup value (“Mar” in this case) along row 1, hence the “H” in "HLOOKUP" referring to a horizontal lookup action.
The arguments in the HLOOKUP function can be translated as questions:
(1) What value do you want to lookup?
(2) What is the range of the table?
(3) Which row of the table do you want to return a value for?
(4) Do you want to find an exact match or an approximate match of the lookup value?
Questions 1 and 2 are no-brainers.
Question 3 requires a tiny bit of thought, in that you need to enter the row index of the table, not necessarily the row of the worksheet. In the pictured example, the row index and the row of the worksheet happen to be the same, because the table starts on row 1. If the table occupied, say, range A8:M15 and you were interested in the third row of that table, you would still enter a 3 to fulfill the row_index argument, even though the actual worksheet row number of that table would be 10. Just remember that HLOOKUP does not care about the worksheet row number, it only cares about the row in reference to the table itself…in this example the third row of the table.
As for the final argument called range_lookup to find an exact match, you know in this pictured example that “Mar” exists as a column header because you have the table arranged by months. Therefore, you set the fourth argument to FALSE (or to 0 which I did, meaning the same as FALSE). When I use HLOOKUP I always set the range_lookup argument to FALSE because I build my tables with simple headers and I would have (in this case) cell B8 data-validated for a drop down list of months Jan, Feb, Mar, and so on. That way, an exact match is assured and the proper number (9111 in the example) would be returned.
Nice