How to Use HLOOKUP Function in Google Sheets (User Guide)

how to Use HLOOKUP Function in Google Sheets 8

Hi, in this article, we will learn how to use the HLOOKUP Function in google sheets.

Many of you have already heard and or used the similar term VLOOKUP in google sheets, but what is HLOOKUP? Is it something like VLOOKUP? Yes, you can say it is the sibling of the VLOOKUP, HLOOKUP, and VLOOKUP both are from the LOOKUP family that is made for search for a key island lookup them in a specified range. VLOOKUP stands for vertical lookup; which means that it looks for the values to be searched in vertical order, and HLOOKUP stands for horizontal lookup; which means that it looks for the values to be searched in horizontal order.

Now VLOOKUP is undoubtedly the most common and most used in google sheets. the reason is the default format of google sheets. Google sheets data is by default set to vertical order, we barely see some data in horizontal order, but it does exist so we need to know to work on it. To perform the same search function on horizontally written data we use HLOOKUP instead of VLOOKUP.

See also  How to Use Google Finance Function in Google Sheets (Beginner's Guide)

HLOOKUP Function in Google Sheets

We use large data sets on google sheets and we often need to search the values in the specified range or section in our google sheets file. Normally we use VLOOKUP to search for the key in column-wise format, but we now have data that is written in row-wise order so what we will do? Surely, VLOOKUP will not work on it, so here comes the HLOOKUP, we need to learn how to use HLOOKUP to search the key in a horizontally written dataset. We need to learn how to HLOOKUP in google sheets to search the key_value smoothly in the absence of VLOOKUP, HLOOKUP helps us to perform a search on horizontal data and get the corresponding value.

  • To perform a key_value search on a vertically aligned dataset
  • To deal with row-wise data
  • To use the VLOOKUP functionalities in a transposed manner.
See also  How to Insert Radio Button in Google Sheets (Best Practice)

How to Use HLOOKUP Function in Google Sheets

To learn how to use the HLOOKUP function in google sheets, we need some examples to implement and learn the procedure.

I have an example of vertically written data on which I will apply the HLOOKUP formula and we will see how it works.

After that, we will see various use cases of HLOOKUP along with other simple functions.

We will also try to use a VLOOKUP on this data so you can understand why we need HLOOKUP, and why VLOOKUP failed for horizontally align data.

How to Use the HLOOKUP Function?

Syntax

=HLOOKUP(search-key, range, index, sorted)

Here,

search-key: is the value to be searched

range: is the range in which we are looking to search the search key

index: is the index number for the row, for which value we want to retrieve against the corresponding search-key value

sorted: is to specify that the data range is sorted or not

I have a dataset written horizontally, I have top cars, their top speed, and price. We will use HLOOKUP on the data range and see if we can retrieve the corresponding car name by searching for the top speed, or price as the search key. Follow the below step-by-step procedure to understand every detail.

Step 1

Similar sample data to follow the example

how to Use HLOOKUP Function in Google Sheets 1

Step 2

Below is an empty cell start writing the HLOOKUP function starting with an = sign.

how to Use HLOOKUP Function in Google Sheets 2

Step 3

Pass the first parameter (search_key, which we are looking to find in the data range)

how to Use HLOOKUP Function in Google Sheets 3

Step 4

Pass the second parameter (data_range, in which we want to search the search key)

how to Use HLOOKUP Function in Google Sheets 4

Step 5

Pass the row index (row number from which we want to retrieve the corresponding value of the search key), we provide column index in VLOOKUP as it works column-wise data.

how to Use HLOOKUP Function in Google Sheets 5

Step 6

Pass False for is_sorted (as we have an unsorted range, pass true if you have a sorted range)

how to Use HLOOKUP Function in Google Sheets 6

Step 7

The formula is completed, close the final bracket, and hit Enter

how to Use HLOOKUP Function in Google Sheets 7

Step 8

Use $ Notation if you want to drag or copy the formula to further columns

how to Use HLOOKUP Function in Google Sheets 8

This is how to use the HLOOKUP Function in google sheets, below we will see some more variations that can be done using HLOOKUP along with other functions.

See also  How to Automatically Sort Dataset in Google Sheets

How to Use HLOOKUP Function in Google Sheets With Other Functions – HLOOKUP with MIN Function

Syntax

=HLOOKUP(MIN(B2:F2),B2:F3,1,False)

Here,

Min: is the function to find the minimum value

B2:F2: is the range in which we want to get the min value

B2:F3: is the range in which we want to search for the value

1: is the row number relative to the formula

False: is-sorted? false for unsorted range

We have the same data set but this time we have to find the top speed of the car that has the minimum price. This is how we can use this formula

 

Step 1

Find the top speed of the car with the minimum price.

how to Use HLOOKUP Function in Google Sheets 9

Step 2

Adding MIN function

how to Use HLOOKUP Function in Google Sheets 10

Step 3

Pass the arguments for HLOOKUP Function

how to Use HLOOKUP Function in Google Sheets 11

Step 4

Final Formula

how to Use HLOOKUP Function in Google Sheets 12

Step 5

The result

how to Use HLOOKUP Function in Google Sheets 13

See also  How to Indent in Google Sheets [4 Methods]

How to Use HLOOKUP Function in Google Sheets With Other Functions – HLOOKUP with MAX Function

Syntax

=HLOOKUP(MAX(B2:F2),B2:F3,2,False)

Here,

Max: is the function to find the maximum value

B2:F2: is the range in which we want to get the max value

B2:F3: is the range in which we want to search for the value

2: is the row number relative to the formula

False: is-sorted? false for unsorted range.

 

Step 1

Find the price of the car with the max speed.

how to Use HLOOKUP Function in Google Sheets 14

Step 2

Adding MAX function

how to Use HLOOKUP Function in Google Sheets 15

Step 3

Pass the arguments for HLOOKUP Function and you have got the result

how to Use HLOOKUP Function in Google Sheets 16

Step 4

Final Formula

how to Use HLOOKUP Function in Google Sheets 17

Step 5

The result

how to Use HLOOKUP Function in Google Sheets 18

See also  Google Sheets Share & Notification Rules Guide (Best Guide Ever)

How to Use HLOOKUP Function in Google Sheets – Difference between VLOOKUP and HLOOKUP

VLOOKUP and HLOOKUP are from the same LOOKUP family, VLOOKP is for vertical looking up the data, and HLOOKUP is for the horizontal lookup of data. We use VLOOKUP for the data that is aligned column-wise and we use HLOOKUP for the data that is aligned horizontally. This is the only difference, in syntax, we have no difference both works the same just with a difference in data orientation.

how to Use HLOOKUP Function in Google Sheets 19

how to Use HLOOKUP Function in Google Sheets 20

See also  How to use Color Scale in Google Sheets [Complete Guide]

Cons of the HLOOKUP Function in Google Sheets

Two common problems with the HLOOKUP function

  1. For search_key, HLOOKUP always uses the first row only within the given range. So it is not possible with the HLOOKUP Function to retrieve a value that is above this row.
  2. HLOOKUP function does not have the power to auto-update itself when some changes are made to rows, such as inserting a row or two between the specified cell range, it will not automatically update the row index, we have to update it manually inside the formula.

Notes

  • HLOOKUP functions are very powerful and can be used along with many formulas and functions.
  • HLOOKUP can be named as a transposed VLOOKUP function
  • We cannot get the custom row value against a search key other than the first row we selected as the entire range
  • HLOOKUP, as well as VLOOKUP, are not powerful enough to auto-update the live changes, if a row is added or removed, or a column is added or removed, they cannot auto-detect and update themselves, we have to manually change the data range as per the new rows.
  • Remember that the last argument (is-sorted), is important when you are working on sorted ranges. You can easily get the sorted data if you tell the formula that you have a sorted list.

See also  How to Use Absolute and Relative Cell References in Google Sheets

FAQs

How to use VLOOKUP in google sheets?

We have discussed, how to use the HLOOKUP Function in google sheets in the very first section. To use the VLOOKUP function in google sheets you should have horizontally aligned data and you can use the formula directly on it similarly to VLOOKUP, if you have any problems, please refer to the above section where I discussed everything in detail with screenshots of practical implementation.

What is the difference between HLOOKUP and VLOOKUP?

The only difference between HLOOKUP and VLOOKUP is the orientation of the data, you may have a google sheet file in which you have multiple data tables, some can be vertically aligned and some can be horizontally aligned you can do the same for both of these data using VLOOKUP for vertically aligned data, and HLOOKUP for horizontally aligned data. There is no syntactical difference.

How to retrieve the corresponding value using VLOOKUP?

We can define the row index, as we define the column index in VLOOKUP, here we have the row index in HLOOKUP. The third argument is to define what to retrieve corresponding to the searched value. Here you can pass row indexes such as 1,2,3. Remember that they are not the actual row number, they are indexed within the context of the HLOOKUP formula for example if your data is in rows 1-10 and you have applied the formula on the range from rows 3-5, so your indexing will be 1 for row 3, 2 for row 4, and 3 for row 5.

Conclusion

Wrapping up how to use the HLOOKUP Function in Google Sheets, we have learned step by step about the uses of this function and how it is different from the VLOOUP function, we saw why we need to learn how to use HLOOKUP in google sheets. Then we learned the complete method to practically implement the HLOOKUP function on horizontally aligned data, then we made some combinations, and we saw how to use the HLOOKUP function with MIN Function, and MAX Function. We saw the difference between VLOOKUP and HLOOKUP and this is how you can do it. You can become the master of HLOOKUP by following this article with step-by-step implementation.

That’s from How to use HLOOKUP Function in Google Sheets, see you soon with another helping tutorial, till then take care. Don’t forget to like share and subscribe to our Blog. Thank you. Keep learning with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general