XLOOKUP function in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the price of a product using XLOOKUP

You have a list of products in column A and their prices in column B. What is the result of this formula if you look up "Banana"?

=XLOOKUP("Banana", A2:A5, B2:B5, "Not found")

Given:
A2:A5 = {"Apple", "Banana", "Cherry", "Date"}
B2:B5 = {1.2, 0.5, 2.5, 3.0}

A 0.5

B "Not found"

C 1.2

D 2.5

💡 Hint

XLOOKUP finds the matching value in the lookup array and returns the corresponding value from the return array.

The formula looks for "Banana" in A2:A5 and returns the price from B2:B5 at the same position. "Banana" is second in the list, so it returns 0.5.

❓ Function Choice

intermediate

Choose the correct XLOOKUP formula to find a student's score

You have student names in C2:C6 and their scores in D2:D6. Which formula correctly finds the score for "John"?

A =XLOOKUP("John", D2:D6, C2:C6)

B =XLOOKUP("John", C2:C6, D2:D6)

C =XLOOKUP(C2:C6, "John", D2:D6)

D =XLOOKUP(D2:D6, "John", C2:C6)

💡 Hint

Remember the order: lookup value, lookup array, return array.

The correct order is lookup value ("John"), lookup array (names), then return array (scores). Only option B follows this order.

🎯 Scenario

advanced

Handle missing lookup values with XLOOKUP

You want to find the price of "Orange" in a list of fruits and prices. If "Orange" is not found, you want to show "Price not available". Which formula does this correctly?

A =XLOOKUP(A2:A5, "Orange", B2:B5, "Price not available")

B =XLOOKUP("Orange", A2:A5, B2:B5)

C =XLOOKUP("Orange", B2:B5, A2:A5, "Price not available")

D =XLOOKUP("Orange", A2:A5, B2:B5, "Price not available")

💡 Hint

Use the 4th argument to specify the value if not found.

Option D uses the 4th argument to return "Price not available" if "Orange" is not found. Other options either miss this or have wrong argument order.

📊 Formula Result

advanced

Find the last matching value using XLOOKUP

Given a list of dates in E2:E7 and sales in F2:F7, what does this formula return?

=XLOOKUP(DATE(2023,5,10), E2:E7, F2:F7, "No sale", 0, -1)

Data:
E2:E7 = {2023-05-01, 2023-05-10, 2023-05-10, 2023-05-15, 2023-05-20, 2023-05-10}
F2:F7 = {100, 200, 150, 300, 400, 250}

A 250

B 200

C 150

D "No sale"

💡 Hint

The 6th argument -1 tells XLOOKUP to find the last match.

The formula looks for the last occurrence of 2023-05-10 in E2:E7 and returns the corresponding sales value. The last match is in row 7 with 250.

❓ data_analysis

expert

Analyze XLOOKUP with approximate match and sorted data

You have sorted scores in G2:G6 and grades in H2:H6:
G2:G6 = {50, 60, 70, 80, 90}
H2:H6 = {"F", "D", "C", "B", "A"}

What grade does this formula return for a score of 75?

=XLOOKUP(75, G2:G6, H2:H6, "No grade", 1)

A "D"

B "B"

C "C"

D "No grade"

💡 Hint

With match_mode 1, XLOOKUP finds the next smaller item if exact match is missing.

75 is not in G2:G6. With match_mode 1, XLOOKUP finds the largest value less than or equal to 75, which is 70, corresponding to grade "C".

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