Why lookups connect related data in Excel - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to find the price of an item using VLOOKUP.

Excel

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

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

APrices!A:B

BA2:B10

CSheet1!A1:C5

DData!B:C

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range that does not include the lookup column

Using a range from the wrong sheet

The table range Prices!A:B correctly points to the columns where the lookup value and price are stored.

2fill in blank

medium

Complete the formula to get the employee's department using INDEX and MATCH.

Excel

=INDEX(B2:B10, MATCH([1], A2:A10, 0))

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

AB3

BD5

CC2

DA5

Attempts:

3 left

💡 Hint

Common Mistakes

Using a cell outside the lookup range

Using a cell from the wrong column

The lookup value is in cell A5, which contains the employee ID to find.

3fill in blank

hard

Fix the error in the formula to correctly retrieve the product name.

Excel

=VLOOKUP(B2, Products!A:C, [1], FALSE)

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

A1

B0

C2

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 returns the lookup value itself

Using 0 is invalid

Using 3 returns price instead of name

The product name is in the second column of the range, so the column index should be 2.

4fill in blank

hard

Fill both blanks to create a formula that finds the sales amount for a given ID.

Excel

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

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

ASales!C2:C100

BSales!A2:A100

CSales!B2:B100

DSales!D2:D100

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the lookup and result ranges

Using wrong columns for sales or IDs

The sales amount is in column C, so Sales!C2:C100 is the array. The IDs are in column A, so Sales!A2:A100 is the lookup range.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps product names to prices for products costing more than $20.

Excel

{ [1]: [2] for [3] in products if [2] > 20 }

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

Aproduct['name']

Bproduct['price']

Cproduct

Ditem

Attempts:

3 left

💡 Hint

Common Mistakes

Using inconsistent variable names

Using wrong keys for name or price

Using a variable name not defined in the loop

We use product['name'] as the key, product['price'] as the value, and iterate over each product in the list.