HLOOKUP function in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to look up the value in the first row and return the value from the second row.

Excel

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

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

A3

B1

C0

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 as the row index returns the lookup value itself, not the result.

Using 0 or a number larger than the number of rows causes errors.

The HLOOKUP function looks for a value in the first row of a range and returns a value from the specified row number. Here, to get the value from the second row, use 2.

2fill in blank

medium

Complete the formula to perform a case-insensitive exact match lookup.

Excel

=HLOOKUP("Apple", A1:D2, 2, [1])

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

AFALSE

BTRUE

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE causes approximate matches which may return wrong results.

Using 0 or 1 instead of TRUE or FALSE can cause confusion.

The last argument in HLOOKUP specifies if the match should be approximate (TRUE) or exact (FALSE). For exact match, use FALSE.

3fill in blank

hard

Fix the error in the formula to correctly return the value from the third row.

Excel

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

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

A2

B3

C1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using 2 returns the second row, not the third.

Using 0 or 1 returns wrong or the lookup row.

The row index must be 3 to get the value from the third row in the range.

4fill in blank

hard

Fill both blanks to create a formula that looks up the value in cell C1, searches the range A1:E3, and returns the value from the second row with an exact match.

Excel

=HLOOKUP([1], [2], 2, FALSE)

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

AC1

BA1:E3

CB1:D2

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong range that does not include the lookup value.

Using TRUE instead of FALSE for exact match.

The first blank is the lookup value cell C1. The second blank is the range A1:E3 where the lookup happens.

5fill in blank

hard

Fill all three blanks to create a formula that looks up the value in cell B2, searches the range A1:D4, and returns the value from the fourth row with an exact match.

Excel

=HLOOKUP([1], [2], [3], FALSE)

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

AB2

BA1:D4

C4

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong row index like 3 instead of 4.

Using a range that does not include the fourth row.

The lookup value is cell B2, the range is A1:D4, and the row index is 4 to get the fourth row's value.

Practice

1. What does the HLOOKUP function do in Excel?

easy

A. It adds all numbers in a horizontal range.

B. It searches for a value in the first column and returns a value from a specified column to the right.

C. It searches for a value in the top row and returns a value from a specified row below.

D. It sorts data horizontally in ascending order.

Solution

1. Step 1: Understand the function purpose

   The HLOOKUP function looks horizontally in the top row of a table to find a match.

2. Step 2: Identify the return value

   Once it finds the match, it returns a value from a specified row below that top row.

3. Final Answer:

   It searches for a value in the top row and returns a value from a specified row below. -> Option C

4. Quick Check:

   HLOOKUP = horizontal lookup [OK]

Hint: HLOOKUP looks horizontally in the top row for a match [OK]

Common Mistakes:

• Confusing HLOOKUP with VLOOKUP which searches vertically
• Thinking it sums values instead of looking up
• Assuming it sorts data

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

easy

A. HLOOKUP(lookup_value, row_index_num, table_array, [range_lookup])

B. HLOOKUP(table_array, lookup_value, row_index_num, [range_lookup])

C. HLOOKUP(row_index_num, lookup_value, table_array, [range_lookup])

D. HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Solution

1. Step 1: Recall the function parameter order

   The correct order is: lookup_value, table_array, row_index_num, and optional range_lookup.

2. Step 2: Match with options

   HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) matches this order exactly.

3. Final Answer:

   HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) -> Option D

4. Quick Check:

   Syntax order matters in HLOOKUP [OK]

Hint: Remember: lookup_value first, then table, then row number [OK]

Common Mistakes:

• Swapping lookup_value and table_array
• Putting row_index_num before table_array
• Omitting required parameters

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

	Jan	Feb	Mar
Sales	100	150	200
Profit	30	50	70

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

medium

A. 70

B. 150

C. 50

D. Error

Solution

1. Step 1: Locate "Feb" in the top row

   "Feb" is found in cell C1, which is the third column.

2. Step 2: Return value from row 3 in that column

   Row 3, column C contains 70 (Profit for Feb).

3. Final Answer:

   70 -> Option A

4. Quick Check:

   HLOOKUP finds Feb then returns row 3 value 70 [OK]

Hint: Match top row, then pick value from given row number [OK]

Common Mistakes:

• Returning value from wrong row (e.g., row 2 instead of 3)
• Confusing columns and rows
• Using approximate match instead of exact

4. You wrote the formula =HLOOKUP("Q2", A1:D3, 2) but it returns incorrect data. What is the likely problem?

medium

A. You forgot to set the fourth argument to FALSE for exact match.

B. The lookup_value "Q2" is not in the top row of the table.

C. The row_index_num is too high and exceeds the table rows.

D. The table_array is missing dollar signs ($) for absolute reference.

Solution

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

   If "Q2" is not found in the top row, HLOOKUP cannot find a match.

2. Step 2: Understand impact of missing lookup_value

   Without the value, the function returns wrong or #N/A error regardless of other parameters.

3. Final Answer:

   The lookup_value "Q2" is not in the top row of the table. -> Option B

4. Quick Check:

   Lookup value must be in top row [OK]

Hint: Ensure lookup_value is exactly in the top row [OK]

Common Mistakes:

• Assuming approximate match works without FALSE
• Using wrong row_index_num
• Thinking absolute references fix lookup errors

5. You have a table with months in the top row (A1:D1) and sales data in rows below. You want to find the sales for "Mar" but the months are not sorted alphabetically. Which formula will always return the exact sales value for "Mar"?

hard

A. =HLOOKUP("Mar", A1:D3, 2, FALSE)

B. =HLOOKUP("Mar", A1:D3, 2)

C. =HLOOKUP("Mar", A1:D3, 3)

D. =HLOOKUP("Mar", A1:D3, 2, TRUE)

Solution

1. Step 1: Understand the range_lookup argument

   TRUE or omitted means approximate match, which requires sorted top row.

2. Step 2: Choose exact match for unsorted data

   FALSE forces exact match, so it works even if months are not sorted.

3. Step 3: Verify row_index_num

   Row 2 contains sales data, so row_index_num should be 2.

4. Final Answer:

   =HLOOKUP("Mar", A1:D3, 2, FALSE) -> Option A

5. Quick Check:

   Use FALSE for exact match with unsorted data [OK]

Hint: Use FALSE for exact match when data is unsorted [OK]

Common Mistakes:

• Omitting FALSE and getting wrong approximate results
• Using wrong row_index_num
• Assuming TRUE works for unsorted data