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.