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.