Merging queries (joins) in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to merge two tables by matching IDs using VLOOKUP.

Excel

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

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

ATable2!A:B

BTable1!A:B

CSheet1!C:D

DData!B:C

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong table range that does not contain the lookup values.

Forgetting to set the last argument to FALSE for exact match.

The formula uses VLOOKUP to find the value in A2 within the first column of Table2 (columns A and B) and returns the matching value from the second column.

2fill in blank

medium

Complete the formula to merge two tables using INDEX and MATCH to find the price for a product ID.

Excel

=INDEX(Table2!B:B, MATCH([1], Table2!A:A, 0))

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

AC2

BA2

CB2

DD2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the lookup value.

Not using 0 as the third argument in MATCH for exact match.

MATCH looks for the value in A2 within Table2 column A, then INDEX returns the corresponding value from column B.

3fill in blank

hard

Fix the error in the formula to merge two tables by correcting the range reference.

Excel

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

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

ATable2!A:C

BTable2!B:C

CTable1!A:C

DSheet1!B:D

Attempts:

3 left

💡 Hint

Common Mistakes

Starting the range from column B excludes the lookup column.

Using a range from the wrong sheet or table.

The lookup range must start with the column containing the lookup value (column A). Using Table2!A:C ensures the first column is the lookup column and includes the third column for the return value.

4fill in blank

hard

Fill both blanks to create a formula that merges two tables and returns the product name for a matching ID.

Excel

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

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

ATable2!B:B

BTable2!A:A

CTable1!A:A

DTable1!B:B

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the INDEX and MATCH ranges.

Using ranges from the wrong table.

INDEX returns the product name from Table2 column B. MATCH finds the position of A2 in Table2 column A to align the lookup.

5fill in blank

hard

Fill all three blanks to create a formula that merges two tables and returns the price if the quantity is greater than 10.

Excel

=IF(B2 [1] 10, INDEX([2], MATCH(A2, [3], 0)), "")

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

A>

BTable2!B:B

CTable2!A:A

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong comparison operator.

Mixing up the INDEX and MATCH ranges.

Forgetting to handle the false condition in IF.

The IF checks if quantity in B2 is greater than 10. If true, INDEX and MATCH return the price from Table2 column B matching the ID in A2 found in Table2 column A. Otherwise, it returns an empty string.