INDEX and MATCH combination in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to find the value in the first column of the range A2:B6 that matches the lookup value in D2.

Google Sheets

=INDEX(A2:A6, MATCH([1], B2:B6, 0))

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

AC2

BD2

CB2

DA2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the lookup value.

Confusing the ranges in INDEX and MATCH.

The MATCH function looks for the value in cell D2 within the range B2:B6. INDEX then returns the value from A2:A6 at the position MATCH finds.

2fill in blank

medium

Complete the formula to find the price of the item named in cell E3 from the table in A2:B6.

Google Sheets

=INDEX(B2:B6, MATCH([1], A2:A6, 0))

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

AE3

BB3

CA3

DC3

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the ranges in INDEX and MATCH.

Using the wrong cell reference for the lookup value.

MATCH finds the position of the item name in E3 within the list A2:A6. INDEX returns the price from B2:B6 at that position.

3fill in blank

hard

Fix the error in the formula to correctly find the quantity of the product in cell F4 from the table in A2:C6.

Google Sheets

=INDEX(C2:C6, MATCH([1], A2:A6, 0))

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

AA4

BC4

CF4

DB4

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong cell reference for the lookup value.

Mixing up the ranges for INDEX and MATCH.

The MATCH function should look for the product name in F4 within A2:A6. INDEX returns the quantity from C2:C6 at that position.

4fill in blank

hard

Fill both blanks to create a formula that finds the department name in column B for the employee ID in cell G5 from the table A2:C6.

Google Sheets

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

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

AB2:B6

BA2:A6

CC2:C6

DD2:D6

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the ranges for INDEX and MATCH.

Using a range outside the table.

INDEX uses the department names in B2:B6. MATCH finds the position of the employee ID in G5 within A2:A6.

5fill in blank

hard

Fill all three blanks to create a formula that finds the salary in column C for the employee name in cell H6 from the table A2:C6.

Google Sheets

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

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

AC2:C6

BH6

CB2:B6

DA2:A6

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong column for salary.

Looking up employee ID instead of name.

INDEX returns the salary from C2:C6. MATCH finds the position of the employee name in H6 within B2:B6.