VLOOKUP function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this VLOOKUP formula?

Given the table below in cells A1:B4:

A1: Product
A2: Apple
A3: Banana
A4: Cherry

B1: Price
B2: 1.2
B3: 0.5
B4: 2.0

What value will the formula =VLOOKUP("Banana", A2:B4, 2, FALSE) return?

A 0.5

B 1.2

C #N/A

D 2.0

💡 Hint

Remember that VLOOKUP searches the first column of the range for the lookup value.

The formula looks for "Banana" in the first column of A2:B4. It finds it in A3, then returns the value from the second column in the same row, which is 0.5.

📊 Formula Result

intermediate

What happens if VLOOKUP's range_lookup is TRUE?

Using the same table as before, what will =VLOOKUP("Blueberry", A2:B4, 2, TRUE) return?

Note: The table is sorted alphabetically by Product.

A 0.5

B 1.2

C 2.0

D #N/A

💡 Hint

When range_lookup is TRUE, VLOOKUP finds the closest match less than or equal to the lookup value.

Since "Blueberry" is not in the list and the table is sorted alphabetically (Apple, Banana, Cherry), VLOOKUP with TRUE finds the largest value less than or equal to "Blueberry" in the first column, which is "Banana", returning 0.5 from the second column.

📊 Formula Result

advanced

What error does this VLOOKUP formula produce?

Given the table in A1:B4 as before, what error will the formula =VLOOKUP("Cherry", A2:A4, 2, FALSE) produce?

A #N/A

B #VALUE!

C #REF!

D 2.0

💡 Hint

Check the range used for the lookup and the column index number.

The range A2:A4 has only one column. Asking for column 2 causes a #REF! error because that column does not exist in the range.

❓ Function Choice

advanced

Which formula correctly finds the price of "Cherry"?

You want to find the price of "Cherry" from the table in A1:B4. Which formula will return the correct price?

A =VLOOKUP("Cherry", A1:B4, 3, FALSE)

B =VLOOKUP("Cherry", B2:B4, 2, FALSE)

C =VLOOKUP("Cherry", A2:B4, 1, TRUE)

D =VLOOKUP("Cherry", A2:B4, 2, FALSE)

💡 Hint

Remember the lookup range must include the lookup column and the return column.

Option D correctly looks up "Cherry" in the first column of A2:B4 and returns the second column value. Others either have wrong ranges or column indexes.

🎯 Scenario

expert

How many items will this VLOOKUP formula find?

You have a table in A1:C6:

A1: ID  B1: Name   C1: Score
A2: 101 B2: John   C2: 85
A3: 102 B3: Jane   C3: 90
A4: 103 B4: John   C4: 78
A5: 104 B5: Mike   C5: 88
A6: 105 B6: John   C6: 92

You use the formula =VLOOKUP("John", B2:C6, 2, FALSE). How many times will this formula find "John" and return a score?

A 3

B 1

C 0

D 5

💡 Hint

VLOOKUP returns only the first match it finds in the lookup column.

VLOOKUP stops at the first match. Even though "John" appears 3 times, the formula returns the score for the first "John" found (85).