The HLOOKUP formula is perhaps not as famous and widely used as its counterpart, VLOOKUP formula. But, this too serves similar purpose, albeit in a transposed form. It stands for ‘Horizontal Lookup’. This searches for a key value in the first row of the input range. And, it returns the value of a specified cell from the column where it finds the key. We can expect an error if the key doesn’t exist.
HLOOKUP(search_key, range, index, [is_sorted])
- search_key – is the value that the HLOOKUP formula uses to search.
- range – is the reference to the range of cells that we are performing a search on. Google Sheets application looks for the key in the first row of the range.
- index – this is the row index of the cell within the range, whose value the formula returns. The first row’s index within the range is 1, the second in the range is 2 and so on. For example, if we input 3 against this parameter, the HLOOKUP formula returns the cell value from the third row and the column in which it finds the search_key.
- [is_sorted] – is an optional parameter which is TRUE by default. This indicates whether the first row in the range is sorted in ascending order or not. If not, we should specify the value as FALSE.
Usage: HLOOKUP Formula
Use case: Numbers in the first row
Let us try out on a few examples. Here is a test data with sales figures that a set of salespersons generated. We tried to answer various business case scenarios, using this formula. You’ll observe how the index parameter value affects the outcome.
Here’s an interesting thing to note in the last case (row # 11 above). The formula returned an #N/A error. Why? That is, as explained in the error description, because the $15000 sales figure does not exist in the first row. Therefore, it cannot return an appropriate value.
The curious case of [is_sorted]
In the examples, we have chosen FALSE for the last parameter if the first row within the input range is not sorted in ascending order. We chose TRUE otherwise. Now, what if we tried to do the opposite?
The second case worked out well. But the first one, not as much! Why is that? Here is a note from the official documentation of HLOOKUP formula:
Using an incorrect sort type may cause incorrect values to be returned.
So, there, they said it themselves! It will not give us expected results when we go with the TRUE option when the first row is not sorted. However, if we have sorted values in the first row, using TRUE for the last parameter will result in a much better performance.
Use case: Sorted strings in the first row
Now that we got our hands greased with numbers, let us try with an example data set, in which the first row has sorted string values, as shown below. There are two examples each for all the three cases available, but with one distinction of is_sorted parameter. Please observe how the HLOOKUP formula is behaving.
Apparently, the TRUE or FALSE value for is_sorted parameter doesn’t really affect the behavior when it finds the search_key within the first row of the range. But an interesting development happens when it doesn’t find a match for the search_key. In the case of FALSE (row # 12), it was looking for an exact match. But in the case of TRUE (row # 13), it apparently is looking for a near match. This, obviously, may not be desirable in many cases.
Use case: Unsorted strings in the first row
The data we are considering for this use case is essentially the same as that used above. Except, we have non-sorted strings in the first row.
Unlike the previous example, the use of TRUE and FALSE values for the fourth parameter is generating different results. And we can deduce that the results are correct only when we used FALSE for is_sorted.
So, what is the bottom line?
Never lie to Google Sheets in the is_sorted parameter! Seemingly, it returns the favor!
Use case: Multiple matches in the first row
There will be instances where we may encounter multiple instances of the same value in the first row. In the example below, Barry occurred twice. What happens if we use HLOOKUP formula in such scenario? Let’s find out.
You may have noticed, it picks up the first Barry it encounters in the list. And it doesn’t consider the second Barry within the row.
Use case: Drawbacks with the HLOOKUP formula
There are two problems with this formula:
- For looking up search_key, it always uses the first row within the input range. So it is not possible with HLOOKUP formula to fetch a cell value that is above the lookup row.
- The formula is not dynamic enough, in that, inserting a row between the input range doesn’t automatically update the row index.
There’s an alternative that solves the above two problems. Please go through the explanation for the INDEX and MATCH formula combination here. It is explained in the context of VLOOKUP formula, but is equally applicable as a HLOOKUP alternative.