XLOOKUP function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the result of this XLOOKUP formula?

Given the table:

Product | Price
Apple | 1.2
Banana | 0.5
Cherry | 2.0

What is the output of the formula =XLOOKUP("Banana", A2:A4, B2:B4)?

A Banana

B 0.5

C #N/A

D 1.2

💡 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:A4 and returns the price from B2:B4. Banana's price is 0.5.

❓ Function Choice

intermediate

Which XLOOKUP formula returns "Not found" if the item is missing?

You want to find the price of "Orange" in the list below. If "Orange" is not found, the formula should return "Not found".

Product | Price
Apple | 1.2
Banana | 0.5
Cherry | 2.0

Which formula does this correctly?

A =XLOOKUP("Orange", A2:A4, B2:B4, "Not found")

B =XLOOKUP("Orange", A2:A4, B2:B4)

C =XLOOKUP("Orange", A2:A4, B2:B4, 0)

D =XLOOKUP("Orange", A2:A4, B2:B4, NA())

💡 Hint

The fourth argument in XLOOKUP is the value to return if no match is found.

Option A uses "Not found" as the not found value, so it returns that text if Orange is missing.

📊 Formula Result

advanced

What is the output of this XLOOKUP with approximate match?

Given the table:

Score | Grade
0 | F
60 | D
70 | C
80 | B
90 | A

What is the result of =XLOOKUP(75, A2:A6, B2:B6, "No grade", 1)?

A B

B No grade

C C

D D

💡 Hint

The 5th argument '1' means approximate match, looking for the next smaller value.

75 is between 70 and 80. With approximate match 1, XLOOKUP returns the grade for 70, which is C.

🎯 Scenario

advanced

You want to find the last matching value using XLOOKUP. Which formula works?

You have a list of dates and sales amounts. You want to find the sales amount for the last date that matches "2024-01-15".

Which XLOOKUP formula returns the last matching sales amount?

A =XLOOKUP("2024-01-15", A2:A10, B2:B10, "Not found", 1, 1)

B =XLOOKUP("2024-01-15", A2:A10, B2:B10, "Not found", -1, 1)

C =XLOOKUP("2024-01-15", A2:A10, B2:B10, "Not found", 0, 1)

D =XLOOKUP("2024-01-15", A2:A10, B2:B10, "Not found", 0, -1)

💡 Hint

The 6th argument controls search direction: -1 searches from last to first.

Option D uses exact match (0) and reverse search (-1) to find the last matching value.

❓ data_analysis

expert

How many items does this XLOOKUP formula return?

Given the table:

Item | Category
Pen | Office
Book | Office
Apple| Food
Milk | Food

What is the number of items returned by this formula?

=XLOOKUP({"Office","Food"}, B2:B5, A2:A5)

A 2

B 4

C 1

D #VALUE! error

💡 Hint

XLOOKUP with an array lookup value returns an array of results, one for each lookup value.

The formula looks up "Office" and "Food" separately and returns the first matching item for each category, so 2 items total.