Why lookups connect datasets in Google Sheets - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to find the price of product ID 101 using VLOOKUP.

Google Sheets

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

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

A2

BB2

C101

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column reference instead of the lookup value

Using TRUE instead of FALSE for exact match

The first argument in VLOOKUP is the value to search for. Here, we want to find product ID 101.

2fill in blank

medium

Complete the formula to return the employee name from the ID in cell D2 using INDEX and MATCH.

Google Sheets

=INDEX(B2:B20, MATCH([1], A2:A20, 0))

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

AD2

BB2

C0

DA2

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column range instead of a single cell for lookup value

Using 1 instead of 0 for exact match

MATCH needs the lookup value, which is the employee ID in cell D2.

3fill in blank

hard

Fix the error in this formula to correctly retrieve the city for customer ID in E3.

Google Sheets

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

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

ACustomers!B:D

BCustomers!A:C

CCustomers!C:E

DCustomers!A:B

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a range that does not include the ID column

Choosing a range that excludes the city column

The lookup range must include the ID column and the city column. Columns A to C cover ID, Name, and City.

4fill in blank

hard

Fill both blanks to create a formula that returns the department name for employee ID in F4.

Google Sheets

=INDEX([1], MATCH(F4, [2], 0))

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

ADepartments!B2:B50

BEmployees!A2:A50

CEmployees!B2:B50

DDepartments!A2:A50

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the ranges

Using wrong columns for lookup or return

The INDEX range is the department names in Departments sheet column B. MATCH looks for employee ID in Employees sheet column A.

5fill in blank

hard

Fill all three blanks to create a dictionary-like formula that maps product names to prices, only for prices above 20.

Google Sheets

=[1]: [2] for [3] in Products!A2:B20 if [2] > 20}

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

Aproduct

Bprice

Ditem

Attempts:

3 left

💡 Hint

Common Mistakes

Using the same variable name for key and loop

Not matching variable names in all parts

We loop over each item in Products!A2:B20. 'item' is the loop variable. 'product' is the product name key, and 'price' is the value used and checked.

Practice

1. What is the main purpose of using a lookup function like VLOOKUP in Google Sheets?

easy

A. To sort data alphabetically

B. To find and retrieve matching data from another table

C. To add numbers in a column

D. To change the font style of cells

Solution

1. Step 1: Understand what lookup functions do

   Lookup functions like VLOOKUP search for a value in one table and return related information from another table.

2. Step 2: Compare options to the purpose

   Only To find and retrieve matching data from another table describes finding and retrieving matching data, which is what VLOOKUP does.

3. Final Answer:

   To find and retrieve matching data from another table -> Option B

4. Quick Check:

   Lookup = find matching data [OK]

Hint: Lookups find matching info across tables fast [OK]

Common Mistakes:

• Thinking lookup sorts data
• Confusing lookup with math functions
• Assuming lookup changes cell formatting

2. Which of the following is the correct syntax for VLOOKUP in Google Sheets?

easy

A. =VLOOKUP(range, search_key, index, [is_sorted])

B. =VLOOKUP(search_key, index, range, [is_sorted])

C. =VLOOKUP(index, search_key, range, [is_sorted])

D. =VLOOKUP(search_key, range, index, [is_sorted])

Solution

1. Step 1: Recall VLOOKUP syntax

   The correct order is search_key, range, index, and optional is_sorted.

2. Step 2: Match options to syntax

   Only =VLOOKUP(search_key, range, index, [is_sorted]) matches this order exactly.

3. Final Answer:

   =VLOOKUP(search_key, range, index, [is_sorted]) -> Option D

4. Quick Check:

   Syntax order = search_key, range, index [OK]

Hint: Remember: search_key first, then range [OK]

Common Mistakes:

• Swapping search_key and range
• Putting index before range
• Forgetting optional is_sorted argument

3. Given the table below in range A2:B5 where column A has product IDs and column B has prices:

A2: 101  B2: 5.00
A3: 102  B3: 7.50
A4: 103  B4: 6.25
A5: 104  B5: 8.00

If cell D2 contains 102, what will the formula =VLOOKUP(D2, A2:B5, 2, FALSE) return?

medium

A. Error

B. 102

C. 7.50

D. 6.25

Solution

1. Step 1: Identify the search_key and range

   The search_key is 102 from D2, and the range is A2:B5.

2. Step 2: Find 102 in column A and return column 2 value

   102 is in A3, so return B3 which is 7.50.

3. Final Answer:

   7.50 -> Option C

4. Quick Check:

   Lookup 102 price = 7.50 [OK]

Hint: Match search_key in first column, return value from index column [OK]

Common Mistakes:

• Returning the search_key instead of the price
• Confusing column index number
• Using TRUE instead of FALSE causing wrong match

4. A user wrote the formula =VLOOKUP(A2, B2:C10, 3, FALSE) but it returns an error. What is the most likely reason?

medium

A. The column index 3 is outside the range B2:C10

B. The search key A2 is missing

C. The range B2:C10 is not sorted

D. The FALSE argument is invalid

Solution

1. Step 1: Check the range columns

   Range B2:C10 has only 2 columns (B and C).

2. Step 2: Check the column index

   Column index 3 asks for a third column, which does not exist in the range.

3. Final Answer:

   The column index 3 is outside the range B2:C10 -> Option A

4. Quick Check:

   Column index must be ≤ number of columns [OK]

Hint: Column index cannot exceed range width [OK]

Common Mistakes:

• Assuming FALSE causes error
• Thinking range must be sorted with FALSE
• Ignoring column count in range

5. You have two tables: one with employee IDs and names (A2:B6), and another with employee IDs and sales numbers (D2:E6). How can you create a new column next to the names that shows each employee's sales using VLOOKUP?

hard

A. Use =VLOOKUP(A2, D2:E6, 2, FALSE) next to each name

B. Use =VLOOKUP(B2, A2:B6, 1, TRUE) next to each name

C. Use =VLOOKUP(E2, A2:B6, 2, FALSE) next to each name

D. Use =VLOOKUP(D2, A2:B6, 2, TRUE) next to each name

Solution

1. Step 1: Identify the lookup key and range

   The employee ID in A2 is the key to find sales in D2:E6.

2. Step 2: Use VLOOKUP with correct parameters

   Formula =VLOOKUP(A2, D2:E6, 2, FALSE) looks up ID in D2:E6 and returns sales from column 2.

3. Final Answer:

   Use =VLOOKUP(A2, D2:E6, 2, FALSE) next to each name -> Option A

4. Quick Check:

   Lookup ID in sales table to get sales [OK]

Hint: Lookup ID in sales table, return sales column [OK]

Common Mistakes:

• Using name instead of ID as search key
• Wrong range or column index
• Using TRUE causing approximate match errors