Hlookup in Google Sheets | Quick Guide

Hlookup in Google Sheets | Quick Guide
Reading Time: 4 minutes
Hlookup in Google Sheets
Hlookup in Google Sheets

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: Syntax

The syntax for HLOOKUP in Google Sheets is 

=HLOOKUP(search_key, range, index, [is_sorted])

  • Search_key: The value to search for, can be a number or a string
  • Range: The range for the search, the first row in the range is specified in the search key
  • Index: The row number of the row to be returned, the first row in the range is numbered 1.
  • Is_sorted: It is an optional argument that is set TRUE by default. It indicates whether the first column of the specified range (the column to be searched is sorted or not). It is recommended to keep this false to avoid any errors.
    • If set to FALSE- If set to FALSE, an exact match is returned. If there are multiple matching values, the first matched cell is returned and #N/A is returned if no value is found
    • If set to TRUE or omitted- If the search array is not sorted then it might return a wrong value, but if it is correctly used then the nearest match (less than or equal to the search key) is returned. Set it to true only if you have large data and want to optimize your searches.

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)

Usage of the HLOOKUP function
Figure 1: Hlookup- Basic Usage

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.

Search key referencing in HLOOKUP
Figure 2: Hlookup- Basic Usage search_key in reference

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))

Returning an entire row using HLOOKUP
Figure 3: Hlookup- returning entire row

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.

Using wildcard characters in HLOOKUP
Figure 4: Using Wildcard characters in HLOOKUP

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])

HLOOKUP in Google Sheets with reference from another sheet
Figure 5: HLOOKUP in Google Sheets with reference from another sheet

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.

One response to “Hlookup in Google Sheets | Quick Guide”

Leave a Reply