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).

Practice

1. What does the VLOOKUP function do in Excel?

easy

A. It sorts data alphabetically.

B. It adds up all numbers in a column.

C. It creates a chart from selected data.

D. It looks for a value in the first column of a table and returns a value from the same row.

Solution

1. Step 1: Understand VLOOKUP purpose

   VLOOKUP searches for a value in the first column of a table.

2. Step 2: Identify what it returns

   It returns a value from the same row but a different column you specify.

3. Final Answer:

   It looks for a value in the first column of a table and returns a value from the same row. -> Option D

4. Quick Check:

   VLOOKUP finds and returns matching row data [OK]

Hint: VLOOKUP always searches first column, returns from chosen column [OK]

Common Mistakes:

• Thinking VLOOKUP sums or sorts data
• Confusing VLOOKUP with chart tools
• Assuming it searches any column, not just first

2. Which of these is the correct syntax for an exact match VLOOKUP formula to find value in A2 in table B2:D10 and return column 3?

easy

A. =VLOOKUP(A2, B2:D10, 2, FALSE)

B. =VLOOKUP(A2, B2:D10, 3, TRUE)

C. =VLOOKUP(A2, B2:D10, 3, FALSE)

D. =VLOOKUP(A2, B2:D10, 3)

Solution

1. Step 1: Identify exact match argument

   For exact match, the fourth argument must be FALSE.

2. Step 2: Check column index and range

   We want column 3 from range B2:D10, so column index is 3.

3. Final Answer:

   =VLOOKUP(A2, B2:D10, 3, FALSE) -> Option C

4. Quick Check:

   Exact match needs FALSE as last argument [OK]

Hint: Use FALSE for exact match, TRUE for approximate [OK]

Common Mistakes:

• Using TRUE instead of FALSE for exact match
• Forgetting the column number or using wrong one
• Omitting the last argument defaults to TRUE (approximate)

3. Given this table in cells A1:B4:

Name | Score
John | 85
Mary | 92
Alex | 78

What is the result of =VLOOKUP("Mary", A2:B4, 2, FALSE)?

medium

A. 85

B. 92

C. Mary

D. #N/A

Solution

1. Step 1: Locate lookup value in first column

   "Mary" is found in cell A3.

2. Step 2: Return value from column 2 same row

   Column 2 in row 3 is 92.

3. Final Answer:

   92 -> Option B

4. Quick Check:

   VLOOKUP returns matching row's column 2 value [OK]

Hint: VLOOKUP returns value from specified column in matched row [OK]

Common Mistakes:

• Returning value from wrong column index
• Getting #N/A if exact match not found
• Confusing lookup value with return value

4. You wrote =VLOOKUP(B2, A2:C10, 4, FALSE) but get an error. What is the problem?

medium

A. The column index 4 is outside the table range columns.

B. The lookup value B2 is missing.

C. The range A2:C10 is not sorted.

D. The FALSE argument is invalid.

Solution

1. Step 1: Check table range columns

   Range A2:C10 has 3 columns only.

2. Step 2: Verify column index argument

   Column index 4 is invalid because it exceeds the number of columns in the range.

3. Final Answer:

   The column index 4 is outside the table range columns. -> Option A

4. Quick Check:

   Column index must be ≤ number of columns in range [OK]

Hint: Column index can't be bigger than table columns count [OK]

Common Mistakes:

• Using column index larger than table columns
• Assuming FALSE is invalid argument
• Thinking lookup value must be in range

5. You have a product list in A2:C6:

Code | Name | Price
P01 | Pen | 1.5
P02 | Book | 7.0
P03 | Bag | 12.5
P04 | Cup | 3.0
P05 | Box | 4.5

How would you write a formula to find the price of product code in E2 exactly, returning "Not found" if code is missing?

hard

A. =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found")

B. =VLOOKUP(E2, A2:C6, 3, TRUE)

C. =VLOOKUP(E2, A2:C6, 2, FALSE)

D. =IF(VLOOKUP(E2, A2:C6, 3, FALSE) = 0, "Not found", VLOOKUP(E2, A2:C6, 3, FALSE))

Solution

1. Step 1: Use VLOOKUP for exact match price

   VLOOKUP with FALSE finds exact product code and returns price from column 3.

2. Step 2: Handle missing code with IFERROR

   Wrap VLOOKUP in IFERROR to return "Not found" if code is missing.

3. Final Answer:

   =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found") -> Option A

4. Quick Check:

   IFERROR catches errors, shows custom message [OK]

Hint: Wrap VLOOKUP in IFERROR to show message if no match [OK]

Common Mistakes:

• Using TRUE instead of FALSE for exact match
• Returning wrong column index value
• Not handling missing lookup values causing errors