The HLOOKUP searches across the first row of the input range and returns the corresponding value of the specified cell or search key from the column found.
Syntax=HLOOKUP(search_key, range, index, [is_sorted])search_key: The value to search for, can be a number or a stringrange: The range for the search, the first row in the range is specified in the search keyindex: The row number of the row to be returned, the first row in the range is numbered 1.is_sorted: [Optional] It indicates whether the column is to be sorted. If omitted or set to TRUE, the nearest match is returned; if set to FALSE, the exact match is returned.
Sample Usage=HLOOKUP("Chandler", B1:G5, 3, FALSE)
//Searches for “Chandler” from the first row of the range B1:G5, and returns the corresponding value from the third column.
How to use the HLOOKUP in Google Sheets
We will try to learn about Hlookup in google sheets and try some examples for the same. We will also see some advanced features that you can use in Hlookup.
To perform vertical lookups, see Vlookup.
Why Need Hlookup In Google Sheets
Hlookup means horizontal lookup. It is a function that allows searching in a row and can return any selected value from the same column. It is one of the more advanced features in Google Sheets and is very useful when you have a large set of data and want to quickly access data for a specific key.
HLOOKUP in Google Sheets: Basic Usage
Suppose we have a Table of sales by different people and we want to fetch the sales made by an individual, let’s say, Chandler in Q2. To achieve the same, we can do the following.
=HLOOKUP(“Chandler”, B1:G5, 3, false)
Here our range is B1:G5 covering the entire table. The function would search in the third row, i.e. the “Name” section, and return the 3rd-row data(i.e. Q2) in the resultant column. Hence our function returns the value 76.
We can also add references to the search_key so that you can change them dynamically. For example, here we added search_key as a reference for the value of the A10 cell so that you can search for them dynamically.
Return the entire column
If you want to return an entire column for the search_key instead of just one field, you can use the ArrayFormula function. An example for the same would look something like this.
=ArrayFormula(HLOOKUP(A10, B1:G5, {2,3,4,5}, false))
We used ARRAYFORMULA and iterated over {2,3,4,5}, i.e. the indices we wanted to return.
Using Wildcard Characters in Hlookup
If you want to use wildcard characters, you can do so too whilst using Hlookup. For example in the following, just searching for Mo* would return us results for “Monica”. Also, one thing to notice here is, if it matches with more than one entry. It would return the first entry in the search range.
We used the ‘*’ special character here, which means, it can be replaced by any number of characters. You can also use ‘?’(replace by 1 character) or ‘~’(escape character for ‘*’ and ‘?’).
Hlookup from other sheets
In current scenarios, often can happen that the lookup table resides in a different tab, to refer to lookup from another sheet, you can use the following syntax.
=HLOOKUP(search_key, sheet_name!range, index, [is_sorted])
As you can see, we are referencing the data from another sheet, namely “sheet1”.
If your data is laid out in rows then you need to perform vertical lookups. To learn about it, see Vlookup.
Conclusion
We learned about Hlookup in Google Sheets and its example uses. We also saw some scenarios in which you can modify this versatile function. You can also check out something called index matching, which is much more convenient to use. You can see why index matching is better than Hlookup here.
See Also
Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!
Using Vlookup and Hlookup together: Learn how to reference data from other sheets in Google Sheets including sheets from a different spreadsheet file.
Vlookup in Google Sheets: We will learn about Vlookup in Google Sheets with several examples. We will also learn some advanced Vlookup features.