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.