Why lookups connect datasets in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this VLOOKUP formula?

Given the table below in cells A1:B4:

A1: ID
A2: 101
A3: 102
A4: 103

B1: Name
B2: Alice
B3: Bob
B4: Carol

What is the result of the formula =VLOOKUP(102, A2:B4, 2, FALSE)?

Google Sheets

=VLOOKUP(102, A2:B4, 2, FALSE)

A Bob

B 102

C Alice

D #N/A

💡 Hint

VLOOKUP finds a value in the first column and returns a value from the same row in another column.

The formula looks for 102 in the first column (A2:A4). It finds it in A3, then returns the value from the second column (B3), which is 'Bob'.

❓ Function Choice

intermediate

Which function correctly connects two datasets by matching IDs?

You have two tables:

Table 1 (A1:B3):
ID | Score
1 | 85
2 | 90

Table 2 (D1:E3):
ID | Name
1 | John
2 | Jane

You want to add the Name from Table 2 next to the Score in Table 1 by matching IDs. Which formula should you use in cell C2?

A =MATCH(A2, D$2:D$3, 0)

B =INDEX(E$2:E$3, MATCH(A2, D$2:D$3, 0))

C =HLOOKUP(A2, D$2:E$3, 2, FALSE)

D =VLOOKUP(A2, D$2:E$3, 2, FALSE)

💡 Hint

You want to find the Name by matching the ID in Table 1 with the ID in Table 2.

VLOOKUP searches for the ID in A2 within the first column of D2:E3 and returns the corresponding Name from the second column.

❓ data_analysis

advanced

How many rows will the formula return a match for?

You have a dataset in A2:B6:

ID | Product
1 | Apple
2 | Banana
3 | Cherry
4 | Date
5 | Elderberry

And a lookup list in D2:D4:
2
4
6

If you use the formula =COUNTIF(A2:A6, D2) copied down from E2 to E4, how many of these will return a count greater than zero?

Google Sheets

=COUNTIF(A2:A6, D2)

A 1

B 3

C 2

D 0

💡 Hint

COUNTIF counts how many times the lookup value appears in the ID column.

IDs 2 and 4 exist in A2:A6, so COUNTIF returns counts > 0 for those two. ID 6 does not exist, so count is 0.

🎯 Scenario

advanced

You want to combine two datasets with different column orders. Which formula correctly matches data?

Dataset 1 (A1:C3):
ID | Name | Age
1 | Anna | 30
2 | Ben | 25

Dataset 2 (E1:F3):
Age | ID
30 | 1
25 | 2

You want to find the Name from Dataset 1 for each ID in Dataset 2. Which formula in G2 will do this correctly?

A =VLOOKUP(F2, A$2:C$3, 2, FALSE)

B =INDEX(B$2:B$3, MATCH(F2, A$2:A$3, 0))

C =INDEX(B$2:B$3, MATCH(F2, C$2:C$3, 0))

D =VLOOKUP(E2, A$2:C$3, 2, FALSE)

💡 Hint

VLOOKUP needs the lookup value in the first column of the range.

VLOOKUP(F2, A$2:C$3, 2, FALSE) looks for the ID in F2 in the first column (A2:A3) and returns the Name from the second column.

🧠 Conceptual

expert

Why is it important to use absolute references in lookup formulas when connecting datasets?

You have a lookup formula in cell C2:

=VLOOKUP(A2, $E$2:$F$10, 2, FALSE)

What is the main reason for using absolute references ($E$2:$F$10) in the formula?

A To make the formula calculate faster

B To keep the lookup range fixed when copying the formula to other cells

C To allow the lookup range to change dynamically when copying the formula

D To prevent errors from incorrect data types

💡 Hint

Think about what happens when you copy a formula down a column.

Using absolute references with $ signs keeps the lookup range fixed, so the formula always looks in the correct table when copied to other rows.