HLOOKUP function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this HLOOKUP formula?

Given the table below in cells A1:D2:

A1: "Item"  B1: "Pen"  C1: "Pencil"  D1: "Eraser"
A2: 10       B2: 5       C2: 7         D2: 3

What is the result of the formula =HLOOKUP("Pencil", A1:D2, 2, FALSE)?

A 5

B 7

C 10

D 3

💡 Hint

HLOOKUP searches the first row for the lookup value and returns the value from the specified row number.

The formula looks for "Pencil" in the first row (A1:D1). It finds it in C1, then returns the value from the second row in the same column, which is C2 = 7.

❓ Function Choice

intermediate

Which formula correctly finds the price of "Eraser" in the table?

Using the same table as before:

A1: "Item"  B1: "Pen"  C1: "Pencil"  D1: "Eraser"
A2: 10       B2: 5       C2: 7         D2: 3

Which formula will correctly return the price of "Eraser"?

A =HLOOKUP("Eraser", A1:D2, 2, TRUE)

B =HLOOKUP("Eraser", A1:D2, 1, FALSE)

C =HLOOKUP("Eraser", A1:D2, 2, FALSE)

D =HLOOKUP("Eraser", A2:D3, 2, FALSE)

💡 Hint

Remember the row index number should point to the row with prices, and exact match is needed.

Option C correctly looks for "Eraser" in the first row (A1:D1) and returns the value from the second row (row 2) with exact match (FALSE).

📊 Formula Result

advanced

What is the output of this HLOOKUP with approximate match?

Given this table in A1:E2:

A1: 50  B1: 60  C1: 70  D1: 80  E1: 90
A2: "F" B2: "D" C2: "C" D2: "B" E2: "A"

What is the result of =HLOOKUP(75, A1:E2, 2, TRUE)?

A "C"

B "D"

C "B"

D "A"

💡 Hint

With approximate match TRUE, HLOOKUP finds the largest value less than or equal to the lookup value.

75 is between 70 and 80. HLOOKUP with TRUE returns the value for 70, which is "C".

🎯 Scenario

advanced

You want to find a value but the lookup row is not the first row. What should you do?

You have a table where the first row contains prices and the second row contains product names:

A1: 5  B1: 7  C1: 3
A2: "Pen" B2: "Pencil" C2: "Eraser"

You want to find the price of "Pencil" using HLOOKUP. Which approach will work?

A Use MATCH("Pencil", A2:C2, 0) to find the column, then INDEX(A1:C1, column) to get price

B Use HLOOKUP("Pencil", A2:C2, 1, FALSE)

C Use HLOOKUP("Pencil", A2:C2, 2, FALSE)

D Use HLOOKUP("Pencil", A1:C2, 1, FALSE)

💡 Hint

HLOOKUP always searches the first row of the range for the lookup value.

Since product names are in the second row, HLOOKUP won't find "Pencil" in the first row. Using MATCH to find the column of "Pencil" in row 2, then INDEX to get the price from row 1 works correctly.

📊 Formula Result

expert

What error does this formula produce?

Given the table:

A1: "Name"  B1: "Age"  C1: "City"
A2: "Alice" B2: 30     C2: "NY"

What error does the formula =HLOOKUP("Bob", A1:C2, 2, FALSE) produce?

A #REF! error

B Empty string

C 0

D #N/A error

💡 Hint

When HLOOKUP cannot find an exact match with FALSE, it returns a specific error.

Since "Bob" is not found in the first row, and exact match is requested, HLOOKUP returns #N/A error.

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