XLOOKUP function in Excel - Cell-by-Cell Formula Trace

Sample Data

This table lists products and their prices. We want to find the price of a product typed in D2 using XLOOKUP.

Cell	Value
A1	Product
B1	Price
A2	Apple
B2	1.2
A3	Banana
B3	0.5
A4	Cherry
B4	2
D1	Lookup Product
D2	Banana
E1	Price Found

Formula Trace

=XLOOKUP(D2, A2:A4, B2:B4, "Not Found")

Step 1: D2

Step 2: A2:A4

Step 3: B2:B4

Step 4: Find 'Banana' in ["Apple", "Banana", "Cherry"]

Step 5: Return value at position 2 in [1.2, 0.5, 2.0]

Step 6: Final result

Cell Reference Map

    A       B       C       D       E
1 |Product | Price |       |Lookup  |Price
  |        |       |       |Product |Found
2 |Apple   | 1.2   |       |Banana  | 
3 |Banana  | 0.5   |       |        | 
4 |Cherry  | 2.0   |       |        | 

Arrows:
D2 --> A2:A4 (lookup array)
D2 --> B2:B4 (return array)

The formula looks up the value in D2 ('Banana') in the product list A2:A4 and returns the matching price from B2:B4.

Result

    A       B       C       D       E
1 |Product | Price |       |Lookup  |Price
  |        |       |       |Product |Found
2 |Apple   | 1.2   |       |Banana  | 0.5
3 |Banana  | 0.5   |       |        | 
4 |Cherry  | 2.0   |       |        | 

The cell E2 shows 0.5, which is the price found by XLOOKUP for the product 'Banana' typed in D2.

Sheet Trace Quiz - 3 Questions

What value does XLOOKUP search for in the lookup array?

A "Apple"

B "Banana"

C 0.5

D "Cherry"

Key Result

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) returns the matching value from return_array for lookup_value in lookup_array.

Practice

1. What does the XLOOKUP function do in Excel?

easy

A. It creates a chart from data.

B. It adds up all numbers in a column.

C. It finds a value in a range and returns a corresponding value from another range.

D. It sorts data alphabetically.

Solution

1. Step 1: Understand the purpose of XLOOKUP

   XLOOKUP searches for a value in one range and returns a matching value from another range.

2. Step 2: Compare with other options

   The other options describe creating charts, adding up numbers, or sorting data, which are different Excel functions unrelated to XLOOKUP.

3. Final Answer:

   It finds a value in a range and returns a corresponding value from another range. -> Option C

4. Quick Check:

   XLOOKUP finds and returns matching values [OK]

Hint: XLOOKUP matches a value and returns related data [OK]

Common Mistakes:

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

2. Which of these is the correct syntax for the XLOOKUP function?

easy

A. XLOOKUP(return_array, lookup_value, lookup_array)

B. XLOOKUP(lookup_value, return_array, lookup_array)

C. XLOOKUP(lookup_array, lookup_value, return_array)

D. XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Solution

1. Step 1: Recall XLOOKUP syntax

   The correct order is lookup_value, lookup_array, return_array, then optional arguments.

2. Step 2: Check each option

   Only XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) follows this correct order and includes optional parameters correctly.

3. Final Answer:

   XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) -> Option D

4. Quick Check:

   Correct syntax order = XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) [OK]

Hint: Remember: lookup_value, lookup_array, return_array order [OK]

Common Mistakes:

• Swapping lookup_array and return_array
• Omitting required arguments
• Using wrong argument order

3. Given the table below, what will =XLOOKUP(102, A2:A5, B2:B5, "Not found") return?

ID	Name
101	Alice
102	Bob
103	Charlie
104	Diana

medium

A. Alice

B. Bob

C. Not found

D. Charlie

Solution

1. Step 1: Identify lookup_value and lookup_array

   Lookup value is 102, lookup array is A2:A5 containing IDs 101, 102, 103, 104.

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

   102 matches second row, so return value from B2:B5 second row is 'Bob'.

3. Final Answer:

   Bob -> Option B

4. Quick Check:

   Lookup 102 returns Bob [OK]

Hint: Match lookup value in first column, return from second [OK]

Common Mistakes:

• Returning wrong row's value
• Confusing lookup and return arrays
• Expecting 'Not found' when value exists

4. What is wrong with this formula? =XLOOKUP(105, A2:A5, B2:B5) given the same table as above.

medium

A. It will return an error because 105 is not found and no if_not_found argument is given.

B. The lookup_array and return_array are reversed.

C. The formula syntax is missing parentheses.

D. It will return the last value in B2:B5 by default.

Solution

1. Step 1: Check if lookup_value exists in lookup_array

   105 is not in A2:A5 (101-104), so no match found.

2. Step 2: Understand default behavior without if_not_found

   Without the optional if_not_found argument, XLOOKUP returns #N/A error if no match.

3. Final Answer:

   It will return an error because 105 is not found and no if_not_found argument is given. -> Option A

4. Quick Check:

   No match without if_not_found causes error [OK]

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

Common Mistakes:

• Assuming it returns last value by default
• Thinking syntax is incorrect
• Swapping lookup and return arrays

5. You want to find the price of a product by its code in column A and return the price from column C. If the product code is not found, you want to show "Price not available". Which formula correctly does this using XLOOKUP?

hard

A. =XLOOKUP(E2, A2:A10, C2:C10, "Price not available")

B. =XLOOKUP(A2:A10, E2, C2:C10, "Price not available")

C. =XLOOKUP(E2, C2:C10, A2:A10, "Price not available")

D. =XLOOKUP(E2, A2:A10, C2:C10)

Solution

1. Step 1: Identify lookup_value and arrays

   Lookup value is in E2 (product code), lookup array is A2:A10 (product codes), return array is C2:C10 (prices).

2. Step 2: Check for if_not_found argument

   =XLOOKUP(E2, A2:A10, C2:C10, "Price not available") includes "Price not available" for missing codes, fulfilling the requirement.

3. Final Answer:

   =XLOOKUP(E2, A2:A10, C2:C10, "Price not available") -> Option A

4. Quick Check:

   Correct lookup and return arrays with if_not_found [OK]

Hint: Use if_not_found to show message when no match [OK]

Common Mistakes:

• Swapping lookup and return arrays
• Omitting if_not_found argument
• Using wrong cell references