HLOOKUP function in Google Sheets - Step-by-Step Guide

Introduction

The HLOOKUP function helps you find information in a table by looking across the top row. It is useful when your data is organized horizontally and you want to find a value below a matching header.

When you have a table with months as headers in the first row and want to find sales numbers for a specific month.

When you want to get the price of a product listed in the top row of a price list.

When you need to find a student's grade from a row of subjects listed horizontally.

When you want to retrieve data from a horizontal list without rearranging your table.

When you want to quickly match a category name in the first row and get related data below it.

Steps

Step 1: Click

- the cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the formula bar

The formula starts appearing in the selected cell

💡 Start typing =HLOOKUP to see the function suggestion

Step 3: Enter

- the formula bar

The formula syntax appears: =HLOOKUP(search_key, range, index, [is_sorted])

Step 4: Replace

- search_key in the formula

The value you want to find in the top row is set

💡 You can type a value in quotes like "January" or select a cell with that value

Step 5: Replace

- range in the formula

The table range including the top row and rows below is set

💡 Select the cells that include headers and data

Step 6: Replace

- index in the formula

The row number below the top row to get the value from is set

💡 Use 2 for the first row below headers, 3 for the second, and so on

Step 7: Optionally type

- is_sorted in the formula

Set TRUE for approximate match or FALSE for exact match

💡 Usually use FALSE to find exact matches

Step 8: Press

- Enter key

The cell shows the value found below the matching header in the top row

Before vs After

Before

A table with months in row 1: January, February, March; and sales numbers in row 2: 100, 150, 120

After

Using =HLOOKUP("February", A1:C2, 2, FALSE) returns 150 in the selected cell

Settings Reference

search_key

📍 first argument in the HLOOKUP formula

The value to find in the top row of the table

Default: none

range

📍 second argument in the HLOOKUP formula

The table where the function looks for the search_key and returns data

Default: none

index

📍 third argument in the HLOOKUP formula

The row number in the range to return data from, counting from the top row as 1

Default: none

is_sorted

📍 fourth optional argument in the HLOOKUP formula

Defines if the top row is sorted for approximate match (TRUE) or exact match (FALSE)

Default: TRUE

Common Mistakes

Using the wrong row number for the index argument

The index counts from the top row as 1, so using 1 returns the header, not the data

Use 2 to get the first row of data below the headers

Not setting is_sorted to FALSE when data is not sorted

If is_sorted is TRUE or omitted, HLOOKUP may return wrong or approximate results

Always set is_sorted to FALSE for exact matches unless you know the top row is sorted

Selecting a range that does not include the top row

HLOOKUP looks only in the first row of the range for the search_key

Make sure the range includes the top row with headers

Summary

HLOOKUP finds data by searching the top row of a table and returning a value from a specified row below.

Use it when your data is arranged horizontally with headers in the first row.

Remember to set the correct row index and use FALSE for exact matches to avoid errors.

Practice

1. What does the HLOOKUP function do in Google Sheets?

easy

A. Searches for a value in the first column and returns a value from a specified column to the right

B. Searches for a value in the top row and returns a value from a specified row below

C. Adds all numbers in a horizontal range

D. Sorts data horizontally in ascending order

Solution

1. Step 1: Understand HLOOKUP's search direction

   HLOOKUP searches horizontally in the top row of a range, not vertically.

2. Step 2: Understand the return value

   It returns a value from a specified row below the found column, matching the search value.

3. Final Answer:

   Searches for a value in the top row and returns a value from a specified row below -> Option B

4. Quick Check:

   HLOOKUP = horizontal search and return [OK]

Hint: HLOOKUP looks across the top row, not down a column [OK]

Common Mistakes:

• Confusing HLOOKUP with VLOOKUP
• Thinking it searches vertically
• Assuming it sums values

2. Which of the following is the correct syntax for the HLOOKUP function?

easy

A. HLOOKUP(search_key, range, row_index, [is_sorted])

B. HLOOKUP(range, search_key, row_index, [is_sorted])

C. HLOOKUP(search_key, row_index, range, [is_sorted])

D. HLOOKUP(row_index, search_key, range, [is_sorted])

Solution

1. Step 1: Recall HLOOKUP syntax order

   The correct order is search_key first, then range, then row_index, and optional is_sorted last.

2. Step 2: Match options to syntax

   HLOOKUP(search_key, range, row_index, [is_sorted]) matches this order exactly, others mix parameters incorrectly.

3. Final Answer:

   HLOOKUP(search_key, range, row_index, [is_sorted]) -> Option A

4. Quick Check:

   Syntax order = search_key, range, row_index, is_sorted [OK]

Hint: Remember: search_key, range, row_index, then is_sorted [OK]

Common Mistakes:

• Swapping range and search_key positions
• Putting row_index before range
• Omitting optional is_sorted parameter

3. Given the table below in cells A1:D3:

| A   | B    | C    | D    |
|-----|------|------|------|
| ID  | 101  | 102  | 103  |
| Name| John | Mary | Alex |
| Age | 25   | 30   | 22   |

What is the result of =HLOOKUP(102, A1:D3, 3, FALSE)?

medium

A. 22

B. Mary

C. 30

D. 102

Solution

1. Step 1: Locate search_key 102 in top row

   Top row is A1:D1: ID, 101, 102, 103. The value 102 is found in cell C1 (3rd column).

2. Step 2: Return value from row 3 in the same column

   Row 3 is A3:D3: Age, 25, 30, 22. The 3rd column value is 30.

3. Final Answer:

   30 -> Option C

4. Quick Check:

   HLOOKUP finds 102, returns 30 [OK]

Hint: Find value in top row, then pick from given row below [OK]

Common Mistakes:

• Returning value from wrong row
• Confusing columns and rows
• Using approximate match instead of exact

4. You wrote the formula =HLOOKUP(105, A1:D3, 2, FALSE) but it returns #N/A. What is the likely cause?

medium

A. Row index 2 is invalid

B. You used TRUE instead of FALSE for exact match

C. Range A1:D3 is empty

D. 105 is not found exactly in the top row

Solution

1. Step 1: Check if 105 exists in top row

   Since 105 is not present in the top row of A1:D1, exact match fails.

2. Step 2: Understand #N/A error meaning

   #N/A means the lookup value was not found when exact match (FALSE) is used.

3. Final Answer:

   105 is not found exactly in the top row -> Option D

4. Quick Check:

   #N/A means no exact match found [OK]

Hint: Exact match needs value present in top row [OK]

Common Mistakes:

• Assuming approximate match works with FALSE
• Using wrong row index
• Ignoring #N/A meaning

5. You have this table in A1:E3:

| A    | B     | C     | D     | E     |
|------|-------|-------|-------|-------|
| Code | A101  | A102  | A103  | A104  |
| Qty  | 10    | 15    | 20    | 25    |
| Price| 5.00  | 7.50  | 6.00  | 8.00  |

You want to find the price of code A103 using HLOOKUP. Which formula correctly returns the price?

hard

A. =HLOOKUP("A103", A1:E3, 3, FALSE)

B. =HLOOKUP("A103", A1:E3, 2, FALSE)

C. =HLOOKUP("A103", A1:E3, 4, FALSE)

D. =HLOOKUP("A103", A2:E3, 3, FALSE)

Solution

1. Step 1: Identify the row number for Price

   Price is in row 3 of the range A1:E3.

2. Step 2: Use HLOOKUP with correct row index and exact match

   Formula should search for "A103" in top row and return value from row 3, with FALSE for exact match.

3. Final Answer:

   =HLOOKUP("A103", A1:E3, 3, FALSE) -> Option A

4. Quick Check:

   Price is row 3, use row_index=3 [OK]

Hint: Match code in top row, return price from 3rd row [OK]

Common Mistakes:

• Using wrong row index for price
• Using incomplete range missing header row
• Confusing quantity row with price row