Merging queries (joins) in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of VLOOKUP for matching and non-matching keys

You have two tables in Excel. Table A has IDs in column A and names in column B. Table B has IDs in column D and scores in column E.

You use this formula in cell C2: =VLOOKUP(A2, D:E, 2, FALSE). What will be the output if A2 contains an ID not present in column D?

A #N/A error

B 0

C Empty cell

D The closest matching score

💡 Hint

Think about what happens when VLOOKUP cannot find an exact match and the last argument is FALSE.

When VLOOKUP with FALSE as the last argument does not find the lookup value, it returns the #N/A error indicating no exact match.

❓ Function Choice

intermediate

Best function to perform a left join in Excel

You want to merge two tables by matching IDs, keeping all rows from the first table and adding matching data from the second. Which Excel function is best suited for this?

A SUMIF

B VLOOKUP

C COUNTIF

D CONCATENATE

💡 Hint

Think about a function that looks up a value in another table and returns related data.

VLOOKUP is commonly used to perform left joins by looking up matching keys and returning corresponding values.

🎯 Scenario

advanced

Combining data with INDEX and MATCH for a two-way lookup

You have a table with months in row 1 (B1:E1) and products in column A (A2:A5). You want to find the sales for a specific product and month using formulas. Which formula correctly returns the sales value?

A =MATCH("Product2", A2:A5, 0) + MATCH("March", B1:E1, 0)

B =INDEX(A2:A5, MATCH("March", B1:E1, 0), MATCH("Product2", B2:E5, 0))

C =VLOOKUP("Product2", A2:E5, MATCH("March", B1:E1, 0), FALSE)

D =INDEX(B2:E5, MATCH("Product2", A2:A5, 0), MATCH("March", B1:E1, 0))

💡 Hint

INDEX returns a value at a row and column. MATCH finds the position of a value in a range.

Option D uses INDEX with MATCH to find the row for the product and the column for the month, returning the correct sales value.

❓ data_analysis

advanced

Result count after merging two tables with XLOOKUP

You have two tables: Table1 with 10 rows of unique IDs and Table2 with 7 rows of unique IDs. You use XLOOKUP in Table1 to get matching values from Table2. How many rows will have a non-error result?

A 7 rows

B 10 rows

C 3 rows

D 0 rows

💡 Hint

Think about how many IDs from Table1 exist in Table2.

Since Table2 has 7 unique IDs, only those 7 matching IDs in Table1 will return values; the rest will return errors.

🧠 Conceptual

expert

Understanding the difference between inner join and left join in Excel formulas

Which statement correctly describes the difference between an inner join and a left join when merging tables using Excel formulas?

A Inner join returns all rows from the second table; left join returns all rows from both tables combined.

B Inner join returns all rows from the first table; left join returns only rows with matching keys in both tables.

C Inner join returns only rows with matching keys in both tables; left join returns all rows from the first table and matching rows from the second, filling blanks if no match.

D Inner join and left join are the same when using VLOOKUP.

💡 Hint

Think about which rows appear in the result for each join type.

Inner join keeps only rows with keys in both tables. Left join keeps all rows from the first table and adds matching data from the second, leaving blanks if no match.

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