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".