VLOOKUP function in Google Sheets - 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 is the result of the formula =VLOOKUP("Banana", A2:B4, 2, FALSE)?

A 0.5

B Banana

C #N/A

D 1.2

💡 Hint

Look for the value in the first column and return the value from the second column.

VLOOKUP searches for "Banana" in the first column of the range A2:B4. It finds it in A3 and returns the value from the second column in the same row, which is 0.5.

❓ Function Choice

intermediate

Which VLOOKUP formula returns the price of 'Cherry'?

Using the same table as above, which formula correctly returns the price of 'Cherry'?

A =VLOOKUP("Cherry", A2:B4, 1, FALSE)

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

C =VLOOKUP("Cherry", B2:A4, 2, FALSE)

D =VLOOKUP("Cherry", A1:B4, 2, TRUE)

💡 Hint

Remember the lookup range must include the lookup column first and the column index must be correct.

Option B correctly looks up 'Cherry' in the first column of A2:B4 and returns the second column value with exact match (FALSE).

📊 Formula Result

advanced

What is the output of this VLOOKUP with approximate match?

Given the table:

A1: Score
A2: 0
A3: 50
A4: 70
A5: 90

B1: Grade
B2: F
B3: C
B4: B
B5: A

What is the result of =VLOOKUP(85, A2:B5, 2, TRUE)?

A B

B C

C A

D #N/A

💡 Hint

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

85 is between 70 and 90, so VLOOKUP returns the grade for 70, which is 'B'.

🎯 Scenario

advanced

Why does this VLOOKUP formula return #N/A?

You have this table:

A1: ID
A2: 101
A3: 102
A4: 103

B1: Name
B2: John
B3: Jane
B4: Joe

You use the formula =VLOOKUP(104, A2:B4, 2, FALSE).
Why does it return #N/A?

A Because the range A2:B4 is missing headers

B Because the column index 2 is invalid

C Because 104 is not found in the first column of the range

D Because FALSE is not a valid argument for VLOOKUP

💡 Hint

Check if the lookup value exists in the first column of the range.

VLOOKUP with FALSE requires an exact match. Since 104 is not in the first column, it returns #N/A.

❓ data_analysis

expert

How many items will this VLOOKUP formula find?

Given the table:

A1: Item
A2: Pen
A3: Pencil
A4: Pen
A5: Eraser

B1: Price
B2: 1.5
B3: 0.5
B4: 1.7
B5: 0.8

What value will =VLOOKUP("Pen", A2:B5, 2, FALSE) return?

A 1.7

B #N/A

C 0.5

D 1.5

💡 Hint

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

VLOOKUP finds the first 'Pen' in A2 and returns the corresponding price 1.5 from B2.

Practice

1.

What does the VLOOKUP function do in Google Sheets?

easy

A. It looks for a value in the first column of a range and returns a value from another column in the same row.

B. It adds all numbers in a column.

C. It sorts data alphabetically.

D. It counts the number of cells with numbers.

Solution

1. Step 1: Understand the purpose of VLOOKUP

   VLOOKUP searches for a value in the first column of a given range.

2. Step 2: Identify what it returns

   It returns a value from another column in the same row where the match is found.

3. Final Answer:

   It looks for a value in the first column of a range and returns a value from another column in the same row. -> Option A

4. Quick Check:

   VLOOKUP finds and returns matching row data [OK]

Hint: Remember: VLOOKUP searches first column, returns from another [OK]

Common Mistakes:

• Thinking VLOOKUP sums or counts data
• Confusing VLOOKUP with sorting functions
• Assuming it searches all columns, not just the first

2.

Which of the following is the correct syntax for an exact match VLOOKUP in Google Sheets?

=VLOOKUP(lookup_value, range, col_index, FALSE)

easy

A. =VLOOKUP(range, lookup_value, col_index, FALSE)

B. =VLOOKUP(lookup_value, range, col_index, TRUE)

C. =VLOOKUP(lookup_value, range, col_index, FALSE)

D. =VLOOKUP(lookup_value, col_index, range, FALSE)

Solution

1. Step 1: Recall VLOOKUP syntax order

   The correct order is lookup_value, range, column index, and match type.

2. Step 2: Identify exact match parameter

   FALSE is used for exact match, TRUE for approximate.

3. Final Answer:

   =VLOOKUP(lookup_value, range, col_index, FALSE) -> Option C

4. Quick Check:

   Exact match uses FALSE as last argument [OK]

Hint: Exact match always ends with FALSE in VLOOKUP [OK]

Common Mistakes:

• Swapping lookup_value and range
• Using TRUE instead of FALSE for exact match
• Mixing order of parameters

3.

Given this table in cells A1:B4:

A1: Product  B1: Price
A2: Apple    B2: 1.5
A3: Banana   B3: 0.75
A4: Cherry   B4: 2.0

What will =VLOOKUP("Banana", A1:B4, 2, FALSE) return?

medium

A. Banana

B. 0.75

C. 1.5

D. 2.0

Solution

1. Step 1: Locate "Banana" in first column

   Banana is found in cell A3.

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

   The second column in row 3 is B3, which is 0.75.

3. Final Answer:

   0.75 -> Option B

4. Quick Check:

   VLOOKUP returns price 0.75 for Banana [OK]

Hint: Find lookup in first column, return from given column index [OK]

Common Mistakes:

• Returning the lookup value instead of the result column
• Using approximate match causing wrong results
• Confusing column index numbers

4.

What is wrong with this formula if you want an exact match lookup?

=VLOOKUP("Orange", A2:B5, 2)

medium

A. Using quotes around Orange

B. Missing the column index parameter

C. Missing the range parameter

D. Missing the FALSE parameter for exact match

Solution

1. Step 1: Check parameters in formula

   The formula has lookup_value, range, and column index but no match type.

2. Step 2: Identify default match behavior

   Without FALSE, VLOOKUP uses approximate match (TRUE) by default, which may give wrong results.

3. Final Answer:

   Missing the FALSE parameter for exact match -> Option D

4. Quick Check:

   Exact match requires FALSE as last argument [OK]

Hint: Always add FALSE for exact match in VLOOKUP [OK]

Common Mistakes:

• Assuming exact match is default
• Forgetting to add FALSE parameter
• Thinking quotes cause error

5.

You have a product list in A2:C6 with columns: Product, Category, Price.

You want to find the category of "Mango" using VLOOKUP. Which formula correctly returns the category?

hard

A. =VLOOKUP("Mango", A2:C6, 2, FALSE)

B. =VLOOKUP("Mango", B2:C6, 2, FALSE)

C. =VLOOKUP("Mango", A2:C6, 1, FALSE)

D. =VLOOKUP("Mango", A2:C6, 3, FALSE)

Solution

1. Step 1: Identify the column index for Category

   Columns are: 1=Product, 2=Category, 3=Price. Category is column 2.

2. Step 2: Check the range and lookup value

   Range A2:C6 includes Product in first column, so lookup works correctly.

3. Final Answer:

   =VLOOKUP("Mango", A2:C6, 2, FALSE) -> Option A

4. Quick Check:

   Category is column 2, so col_index=2 [OK]

Hint: Match column index to desired return column in VLOOKUP [OK]

Common Mistakes:

• Using wrong column index for category
• Using a range that does not start with lookup column
• Confusing column numbers with letters