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.

Practice

1. What is the main purpose of using a lookup function like VLOOKUP in Google Sheets?

easy

A. To sort data alphabetically

B. To find and retrieve matching data from another table

C. To add numbers in a column

D. To change the font style of cells

Solution

1. Step 1: Understand what lookup functions do

   Lookup functions like VLOOKUP search for a value in one table and return related information from another table.

2. Step 2: Compare options to the purpose

   Only To find and retrieve matching data from another table describes finding and retrieving matching data, which is what VLOOKUP does.

3. Final Answer:

   To find and retrieve matching data from another table -> Option B

4. Quick Check:

   Lookup = find matching data [OK]

Hint: Lookups find matching info across tables fast [OK]

Common Mistakes:

• Thinking lookup sorts data
• Confusing lookup with math functions
• Assuming lookup changes cell formatting

2. Which of the following is the correct syntax for VLOOKUP in Google Sheets?

easy

A. =VLOOKUP(range, search_key, index, [is_sorted])

B. =VLOOKUP(search_key, index, range, [is_sorted])

C. =VLOOKUP(index, search_key, range, [is_sorted])

D. =VLOOKUP(search_key, range, index, [is_sorted])

Solution

1. Step 1: Recall VLOOKUP syntax

   The correct order is search_key, range, index, and optional is_sorted.

2. Step 2: Match options to syntax

   Only =VLOOKUP(search_key, range, index, [is_sorted]) matches this order exactly.

3. Final Answer:

   =VLOOKUP(search_key, range, index, [is_sorted]) -> Option D

4. Quick Check:

   Syntax order = search_key, range, index [OK]

Hint: Remember: search_key first, then range [OK]

Common Mistakes:

• Swapping search_key and range
• Putting index before range
• Forgetting optional is_sorted argument

3. Given the table below in range A2:B5 where column A has product IDs and column B has prices:

A2: 101  B2: 5.00
A3: 102  B3: 7.50
A4: 103  B4: 6.25
A5: 104  B5: 8.00

If cell D2 contains 102, what will the formula =VLOOKUP(D2, A2:B5, 2, FALSE) return?

medium

A. Error

B. 102

C. 7.50

D. 6.25

Solution

1. Step 1: Identify the search_key and range

   The search_key is 102 from D2, and the range is A2:B5.

2. Step 2: Find 102 in column A and return column 2 value

   102 is in A3, so return B3 which is 7.50.

3. Final Answer:

   7.50 -> Option C

4. Quick Check:

   Lookup 102 price = 7.50 [OK]

Hint: Match search_key in first column, return value from index column [OK]

Common Mistakes:

• Returning the search_key instead of the price
• Confusing column index number
• Using TRUE instead of FALSE causing wrong match

4. A user wrote the formula =VLOOKUP(A2, B2:C10, 3, FALSE) but it returns an error. What is the most likely reason?

medium

A. The column index 3 is outside the range B2:C10

B. The search key A2 is missing

C. The range B2:C10 is not sorted

D. The FALSE argument is invalid

Solution

1. Step 1: Check the range columns

   Range B2:C10 has only 2 columns (B and C).

2. Step 2: Check the column index

   Column index 3 asks for a third column, which does not exist in the range.

3. Final Answer:

   The column index 3 is outside the range B2:C10 -> Option A

4. Quick Check:

   Column index must be ≤ number of columns [OK]

Hint: Column index cannot exceed range width [OK]

Common Mistakes:

• Assuming FALSE causes error
• Thinking range must be sorted with FALSE
• Ignoring column count in range

5. You have two tables: one with employee IDs and names (A2:B6), and another with employee IDs and sales numbers (D2:E6). How can you create a new column next to the names that shows each employee's sales using VLOOKUP?

hard

A. Use =VLOOKUP(A2, D2:E6, 2, FALSE) next to each name

B. Use =VLOOKUP(B2, A2:B6, 1, TRUE) next to each name

C. Use =VLOOKUP(E2, A2:B6, 2, FALSE) next to each name

D. Use =VLOOKUP(D2, A2:B6, 2, TRUE) next to each name

Solution

1. Step 1: Identify the lookup key and range

   The employee ID in A2 is the key to find sales in D2:E6.

2. Step 2: Use VLOOKUP with correct parameters

   Formula =VLOOKUP(A2, D2:E6, 2, FALSE) looks up ID in D2:E6 and returns sales from column 2.

3. Final Answer:

   Use =VLOOKUP(A2, D2:E6, 2, FALSE) next to each name -> Option A

4. Quick Check:

   Lookup ID in sales table to get sales [OK]

Hint: Lookup ID in sales table, return sales column [OK]

Common Mistakes:

• Using name instead of ID as search key
• Wrong range or column index
• Using TRUE causing approximate match errors