Merging queries (joins) in Excel - Cell-by-Cell Formula Trace

Sample Data

Two tables: Left table with IDs and Names (A1:B4), Right table with IDs and Scores (D1:E4). We want to merge them by ID.

Cell	Value
A1	ID
B1	Name
A2	1
B2	Alice
A3	2
B3	Bob
A4	3
B4	Charlie
D1	ID
E1	Score
D2	2
E2	85
D3	3
E3	90
D4	4
E4	75

Formula Trace

=IFERROR(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), "No Score")

Step 1: VLOOKUP(1, $D$2:$E$4, 2, FALSE)

Step 2: IFERROR(#N/A, "No Score")

Cell Reference Map

    A       B       C       D       E
1 | ID    | Name  |       | ID    | Score
2 | 1     | Alice |       | 2     | 85
3 | 2     | Bob   |       | 3     | 90
4 | 3     | Charlie|      | 4     | 75

Arrows: A2 -> VLOOKUP lookup value
Range $D$2:$E$4 -> VLOOKUP table array
Column 2 -> VLOOKUP returns Score

Formula in cell C2 uses A2 as lookup value and searches in D2:E4 for matching ID to get Score.

Result

    A       B       C
1 | ID    | Name  | Score
2 | 1     | Alice | No Score
3 | 2     | Bob   | 85
4 | 3     | Charlie| 90

Column C shows Scores matched by ID from right table. If no match, shows 'No Score'.

Sheet Trace Quiz - 3 Questions

What does the formula return for ID 1?

A No Score

B 85

C 90

D 75

Key Result

VLOOKUP looks for a value in the first column of a range and returns a value from a specified column in the same row.

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