VLOOKUP function in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to look up the value in cell A2 in the range B2:D10.

Google Sheets

=VLOOKUP(A2, [1], 2, FALSE)

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

AA2:D10

BB2:D10

CB2:C10

DA1:D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range that does not include the lookup column.

Including the lookup value cell in the range.

The range B2:D10 is where the lookup will happen. It must include the lookup column and the return column.

2fill in blank

medium

Complete the formula to return the value from the 3rd column of the range when looking up the value in A5.

Google Sheets

=VLOOKUP(A5, C2:E20, [1], FALSE)

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

A2

B1

C4

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using the actual spreadsheet column number instead of the relative column index in the range.

Using a column index outside the range.

The third argument is the column index in the range from which to return the value. Since the range is C2:E20, column 3 corresponds to column E.

3fill in blank

hard

Fix the error in the formula to perform an exact match lookup for the value in B3.

Google Sheets

=VLOOKUP(B3, A1:C15, 2, [1])

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

A1

BTRUE

CFALSE

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving the last argument empty or TRUE for exact match needs.

Using 0 or 1 which are not valid boolean values here.

The last argument must be FALSE to perform an exact match lookup in VLOOKUP.

4fill in blank

hard

Fill both blanks to create a formula that looks up the value in D4 in the range A2:C12 and returns the value from the 3rd column with an exact match.

Google Sheets

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

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

AD4

BA2:C12

CB2:D12

DC4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range that does not include the lookup column.

Using a wrong cell reference for the lookup value.

The first blank is the lookup value cell D4. The second blank is the range A2:C12 where the lookup happens.

5fill in blank

hard

Fill all four blanks to create a formula that looks up the value in cell B1 in the range A1:D20, returns the value from the 4th column, and performs an exact match.

Google Sheets

=VLOOKUP([1], [2], [3], [4])

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

AB1

BA1:D20

C4

DFALSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using TRUE or omitting the last argument for exact match.

Using a column index outside the range.

The lookup value is B1, the range is A1:D20, the column index is 4, and FALSE ensures exact match.

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