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.