HLOOKUP function in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to look up the value in cell A2 in the first row of the table in range B1:D3.

Google Sheets

=HLOOKUP([1], B1:D3, 2, FALSE)

Drag options to blanks, or click blank then click option'

ATRUE

BB2

C1

DA2

Attempts:

3 left

💡 Hint

Common Mistakes

Using a cell outside the lookup range.

Using a number instead of a cell reference.

The first argument of HLOOKUP is the value to find. Here, it should be the value in cell A2.

2fill in blank

medium

Complete the formula to return the value from the third row of the table in range A1:C3 for the lookup value in cell B1.

Google Sheets

=HLOOKUP(B1, A1:C3, [1], FALSE)

Drag options to blanks, or click blank then click option'

A3

B2

C1

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a row number outside the table range.

Confusing row and column numbers.

The third argument is the row index from which to return the value. To get the third row, use 3.

3fill in blank

hard

Fix the error in the formula to correctly find the value in the first row of range A1:D2 for the lookup value in cell C1.

Google Sheets

=HLOOKUP(C1, A1:D2, [1], TRUE)

Drag options to blanks, or click blank then click option'

A1

B3

C2

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 as the row index causes an error.

Using a row index greater than the number of rows in the range.

The row index must be at least 1. Using 1 returns values from the first row.

4fill in blank

hard

Fill both blanks to create a formula that looks up the value in cell A1 in the first row of range B1:E3 and returns the value from the second row, using exact match.

Google Sheets

=HLOOKUP([1], B1:E3, [2], FALSE)

Drag options to blanks, or click blank then click option'

AA1

BB1

C2

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong cell reference for lookup value.

Using a row index outside the table range.

The first blank is the lookup value cell A1. The second blank is the row index 2 to get the second row's value.

5fill in blank

hard

Fill all three blanks to create a formula that looks up the value in cell B2 in the first row of range A1:D4, returns the value from the fourth row, and uses approximate match.

Google Sheets

=HLOOKUP([1], A1:D4, [2], [3])

Drag options to blanks, or click blank then click option'

AB2

B4

CTRUE

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using FALSE instead of TRUE for approximate match.

Using a wrong row index.

The lookup value is B2, the row index is 4 to get the fourth row, and TRUE means approximate match.