XLOOKUP function in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to look up the value in cell A2 within the range B2:B10.

Google Sheets

=XLOOKUP([1], B2:B10, C2:C10)

Drag options to blanks, or click blank then click option'

AD2

BB2

CC2

DA2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the lookup value.

Confusing the lookup array with the return array.

The first argument in XLOOKUP is the value to search for. Here, we want to find the value in cell A2.

2fill in blank

medium

Complete the formula to return the value from the range C2:C10 that matches the lookup value in A2.

Google Sheets

=XLOOKUP(A2, B2:B10, [1])

Drag options to blanks, or click blank then click option'

AC2:C10

BB2:B10

CA2:A10

DD2:D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using the lookup array again as the return array.

Choosing a range that does not align with the lookup array.

The third argument is the return array. We want to return values from C2:C10 that match the lookup.

3fill in blank

hard

Fix the error in the formula to return "Not found" if the lookup value in A2 is missing.

Google Sheets

=XLOOKUP(A2, B2:B10, C2:C10, [1])

Drag options to blanks, or click blank then click option'

A0

B"Not found"

CFALSE

DTRUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or FALSE which are not descriptive messages.

Forgetting to put text in quotes.

The fourth argument is the value to return if no match is found. We want to show "Not found" as text.

4fill in blank

hard

Fill both blanks to make the formula search for the value in A5, look in B5:B15, and return from C5:C15.

Google Sheets

=XLOOKUP([1], [2], C5:C15)

Drag options to blanks, or click blank then click option'

AA5

BB5:B15

CC5:C15

DA1

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the lookup value and lookup array.

Choosing ranges that do not match in size.

The first blank is the lookup value cell A5. The second blank is the lookup array B5:B15.

5fill in blank

hard

Fill all three blanks to create a formula that looks up the value in A3, searches in B3:B12, returns from C3:C12, and shows "Missing" if not found.

Google Sheets

=XLOOKUP([1], [2], [3], "Missing")

Drag options to blanks, or click blank then click option'

AA3

BB3:B12

CC3:C12

DD3:D12

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the lookup and return ranges.

Forgetting to match the sizes of lookup and return arrays.

The blanks correspond to lookup value A3, lookup array B3:B12, and return array C3:C12.

Practice

1. What does the XLOOKUP function do in Google Sheets?

easy

A. It searches for a value in one column and returns a matching value from another column.

B. It adds up all numbers in a range.

C. It sorts data alphabetically.

D. It creates a chart from selected data.

Solution

1. Step 1: Understand the purpose of XLOOKUP

   XLOOKUP is designed to find a value in one list and return a related value from another list.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions unrelated to XLOOKUP.

3. Final Answer:

   It searches for a value in one column and returns a matching value from another column. -> Option A

4. Quick Check:

   XLOOKUP finds and returns matching values [OK]

Hint: XLOOKUP matches and returns related data easily [OK]

Common Mistakes:

• Confusing XLOOKUP with SUM or SORT functions
• Thinking XLOOKUP creates charts
• Assuming XLOOKUP only works with numbers

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

easy

A. =XLOOKUP(search_key, return_range, lookup_range)

B. =XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

C. =XLOOKUP(lookup_range, search_key, return_range)

D. =XLOOKUP(return_range, search_key, lookup_range, [if_not_found])

Solution

1. Step 1: Recall XLOOKUP syntax

   The correct syntax is =XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]).

2. Step 2: Check each option

   =XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]) matches the correct order and optional arguments. Options A, B, and D have incorrect argument orders.

3. Final Answer:

   =XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]) -> Option B

4. Quick Check:

   Correct XLOOKUP syntax = =XLOOKUP(search_key, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]) [OK]

Hint: Remember: search_key first, then lookup and return ranges [OK]

Common Mistakes:

• Swapping lookup_range and return_range
• Omitting required arguments
• Placing optional arguments in wrong order

3. Given the data:

A2:A5 = {101, 102, 103, 104}
B2:B5 = {"Apple", "Banana", "Cherry", "Date"}

What will =XLOOKUP(103, A2:A5, B2:B5) return?

medium

A. "Banana"

B. "Date"

C. "Cherry"

D. Error

Solution

1. Step 1: Identify the search key and lookup range

   The search key is 103, and the lookup range is A2:A5 containing {101, 102, 103, 104}.

2. Step 2: Find matching value and return corresponding value

   103 is found at the third position in A2:A5. The return range B2:B5 has "Cherry" at the third position.

3. Final Answer:

   "Cherry" -> Option C

4. Quick Check:

   103 matches Cherry [OK]

Hint: Match position of search key in lookup range to return range [OK]

Common Mistakes:

• Returning wrong position value
• Confusing lookup and return ranges
• Expecting error when value exists

4. What is wrong with this formula?

=XLOOKUP(105, A2:A5, B2:B5)

Given:
A2:A5 = {101, 102, 103, 104}
B2:B5 = {"Apple", "Banana", "Cherry", "Date"}

medium

A. The ranges have different sizes.

B. The lookup range and return range are swapped.

C. The formula syntax is incorrect.

D. The search key 105 is not found and no if_not_found argument is provided, so it returns an error.

Solution

1. Step 1: Check if search key exists in lookup range

   105 is not in A2:A5, so XLOOKUP cannot find a match.

2. Step 2: Understand behavior without if_not_found

   Without the optional if_not_found argument, XLOOKUP returns an error when no match is found.

3. Final Answer:

   The search key 105 is not found and no if_not_found argument is provided, so it returns an error. -> Option D

4. Quick Check:

   Missing if_not_found causes error on no match [OK]

Hint: Add if_not_found to avoid errors when no match [OK]

Common Mistakes:

• Assuming XLOOKUP returns blank instead of error
• Swapping lookup and return ranges
• Ignoring optional arguments

5. You have two columns:

Product IDs (A2:A6): {201, 202, 203, 204, 205}
Prices (B2:B6): {10, 15, 20, 25, 30}

You want to find the price for Product ID 206, but it does not exist. Which formula will return "Not Found" instead of an error?

hard

A. =XLOOKUP(206, A2:A6, B2:B6, "Not Found")

B. =XLOOKUP(206, A2:A6, B2:B6)

C. =XLOOKUP(206, B2:B6, A2:A6, "Not Found")

D. =XLOOKUP("Not Found", A2:A6, B2:B6, 206)

Solution

1. Step 1: Identify the correct syntax to handle missing values

   To avoid errors when the search key is missing, use the if_not_found argument with the desired message.

2. Step 2: Check each option

   =XLOOKUP(206, A2:A6, B2:B6, "Not Found") correctly uses "Not Found" as the if_not_found argument. =XLOOKUP(206, A2:A6, B2:B6) will return an error. =XLOOKUP(206, B2:B6, A2:A6, "Not Found") swaps lookup and return ranges incorrectly. =XLOOKUP("Not Found", A2:A6, B2:B6, 206) uses wrong argument order.

3. Final Answer:

   =XLOOKUP(206, A2:A6, B2:B6, "Not Found") -> Option A

4. Quick Check:

   Use if_not_found to show message on no match [OK]

Hint: Use 4th argument to show message if no match found [OK]

Common Mistakes:

• Swapping lookup and return ranges
• Omitting if_not_found argument
• Placing arguments in wrong order