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.

Practice

1. What does merging queries (joins) in Excel usually help you do?

easy

A. Combine data from two tables based on a common value

B. Sort data alphabetically

C. Create charts from data

D. Format cells with colors

Solution

1. Step 1: Understand the purpose of merging queries

   Merging queries means joining data from two tables using a common value to combine related information.

2. Step 2: Identify what merging queries does in Excel

   It helps to combine and compare data easily without manual copying or sorting.

3. Final Answer:

   Combine data from two tables based on a common value -> Option A

4. Quick Check:

   Merging queries = Combine data [OK]

Hint: Merging joins tables by matching common values [OK]

Common Mistakes:

• Confusing merging with sorting
• Thinking it creates charts
• Assuming it changes cell colors

2. Which of these is the correct syntax to merge two tables using XLOOKUP in Excel?

easy

A. =XLOOKUP(A2, Table2[Value], Table2[ID])

B. =XLOOKUP(Table2[Value], A2, Table2[ID])

C. =XLOOKUP(Table2[ID], Table2[Value], A2)

D. =XLOOKUP(A2, Table2[ID], Table2[Value])

Solution

1. Step 1: Recall XLOOKUP syntax

   XLOOKUP(lookup_value, lookup_array, return_array) finds lookup_value in lookup_array and returns corresponding value from return_array.

2. Step 2: Match syntax to merging tables

   We want to find A2 (key) in Table2[ID] and return Table2[Value], so =XLOOKUP(A2, Table2[ID], Table2[Value]) is correct.

3. Final Answer:

   =XLOOKUP(A2, Table2[ID], Table2[Value]) -> Option D

4. Quick Check:

   XLOOKUP syntax = lookup_value, lookup_array, return_array [OK]

Hint: XLOOKUP: lookup_value, lookup_array, return_array order [OK]

Common Mistakes:

• Swapping lookup_array and return_array
• Using wrong column references
• Putting return_array before lookup_array

3. Given these tables:

Table1:
ID | Name
1 | Alice
2 | Bob

Table2:
ID | Score
1 | 85
3 | 90

If you use =XLOOKUP(A2, Table2[ID], Table2[Score], "Not Found") in Table1 where A2=1, what is the result?

medium

A. 85

B. 90

C. Not Found

D. 1

Solution

1. Step 1: Understand the lookup

   The formula looks for A2=1 in Table2[ID]. Table2 has ID 1 with Score 85.

2. Step 2: Return the matching Score

   Since ID 1 is found, XLOOKUP returns 85 from Table2[Score].

3. Final Answer:

   85 -> Option A

4. Quick Check:

   Lookup 1 returns 85 [OK]

Hint: XLOOKUP returns matching value or fallback if none [OK]

Common Mistakes:

• Choosing score for ID 3 instead of 1
• Confusing fallback value with result
• Returning ID instead of Score

4. You wrote this formula to merge data:
=XLOOKUP(A2, Table2[Value], Table2[ID])
But it returns wrong results. What is the likely error?

medium

A. Used wrong function name

B. Forgot to lock cell references

C. Swapped lookup_array and return_array columns

D. Used incorrect table name

Solution

1. Step 1: Check XLOOKUP parameters

   lookup_array should be the column with keys to find (e.g., IDs), return_array the column with values to return.

2. Step 2: Identify swapped columns

   Using Table2[Value] as lookup_array and Table2[ID] as return_array is reversed; it should be lookup ID, return Value.

3. Final Answer:

   Swapped lookup_array and return_array columns -> Option C

4. Quick Check:

   Lookup and return arrays must be in correct order [OK]

Hint: Lookup array must be key column, return array the value column [OK]

Common Mistakes:

• Mixing lookup and return columns
• Assuming function name is wrong
• Ignoring cell reference locking

5. You have two tables:

Employees:
ID | Name
101 | John
102 | Mary
103 | Steve

Salaries:
ID | Salary
101 | 5000
103 | 4500

You want to create a merged table showing all employees and their salaries, showing "No Data" if salary is missing. Which formula in cell C2 (next to Name) correctly merges salaries?

hard

A. =XLOOKUP(Name, Salaries[ID], Salaries[Salary], "No Data")

B. =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data")

C. =XLOOKUP(A2, Salaries[Salary], Salaries[ID], "No Data")

D. =XLOOKUP(A2, Employees[ID], Salaries[Salary], "No Data")

Solution

1. Step 1: Identify lookup value and arrays

   We want to find employee ID in Salaries[ID] and return Salaries[Salary]. If not found, show "No Data".

2. Step 2: Check formula correctness

   =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data") uses =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data"), which matches the requirement perfectly.

3. Final Answer:

   =XLOOKUP(A2, Salaries[ID], Salaries[Salary], "No Data") -> Option B

4. Quick Check:

   Lookup ID in Salaries, return Salary or fallback [OK]

Hint: Use XLOOKUP with fallback to handle missing data [OK]

Common Mistakes:

• Using wrong lookup or return columns
• Referencing Name instead of ID
• Mixing tables in lookup arrays