HLOOKUP function in Excel - 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:D3:

A1: Year | B1: 2020 | C1: 2021 | D1: 2022
A2: Sales | B2: 100 | C2: 150 | D2: 200
A3: Profit | B3: 20 | C3: 30 | D3: 40

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

A 150

B 30

C 40

D 20

💡 Hint

Remember, HLOOKUP searches the first row and returns value from the specified row number.

The formula looks for 2021 in the first row (B1:D1). It finds it in C1. Then it returns the value from the 3rd row in the same column, which is C3 = 30.

❓ Function Choice

intermediate

Which formula correctly returns the profit for year 2022?

Using the same table as above, which formula will return the profit value for 2022?

A =INDEX(A1:D3, 3, MATCH(2022, A1:D1, 0))

B =VLOOKUP(2022, A1:D3, 3, FALSE)

C =HLOOKUP(2022, A1:D3, 3, FALSE)

D =HLOOKUP(2022, A1:D3, 2, FALSE)

💡 Hint

HLOOKUP searches horizontally in the first row and returns value from the specified row.

Option C correctly uses HLOOKUP to find 2022 in the first row and returns the 3rd row value (Profit). Option C uses VLOOKUP which searches vertically and won't work here. Option C returns Sales (2nd row). Option C is correct but uses INDEX/MATCH, not HLOOKUP.

📊 Formula Result

advanced

What is the output of this HLOOKUP with approximate match?

Given the table:

A1: Score | B1: 0 | C1: 50 | D1: 75 | E1: 90
A2: Grade | B2: F | C2: C | D2: B | E2: A

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

A F

B C

C A

D B

💡 Hint

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

80 is between 75 and 90. HLOOKUP with TRUE finds 75 (D1) as the largest value less than or equal to 80, then returns D2 which is 'B'.

🎯 Scenario

advanced

You want to find a value but get #N/A error. What is the likely cause?

You use the formula =HLOOKUP(2023, A1:D3, 2, FALSE) on the table:

A1: Year | B1: 2020 | C1: 2021 | D1: 2022
A2: Sales | B2: 100 | C2: 150 | D2: 200
A3: Profit | B3: 20 | C3: 30 | D3: 40

But the formula returns #N/A. Why?

A The lookup value 2023 is not found in the first row.

B The row index 2 is invalid for the table.

C The range A1:D3 is missing headers.

D The formula needs TRUE instead of FALSE for exact match.

💡 Hint

Check if the lookup value exists in the first row when using exact match.

Since 2023 is not in the first row, and exact match (FALSE) is used, HLOOKUP returns #N/A because it cannot find the value.

❓ data_analysis

expert

How many items does this HLOOKUP formula return when used in an array formula?

Given the table:

A1: Month | B1: Jan | C1: Feb | D1: Mar
A2: Sales | B2: 100 | C2: 120 | D2: 130
A3: Profit | B3: 20 | C3: 25 | D3: 30

What is the result of the array formula =HLOOKUP({"Jan","Mar"}, A1:D3, 3, FALSE) entered with Ctrl+Shift+Enter in Excel?

A An array with two values: {20, 30}

B A single value: 20

C An error because HLOOKUP does not support arrays

D An array with three values: {20, 25, 30}

💡 Hint

HLOOKUP can return multiple values when given an array of lookup values.

The formula looks up 'Jan' and 'Mar' in the first row and returns the 3rd row values for each: 20 and 30 respectively, producing an array {20, 30}.