XLOOKUP function in Google Sheets - Cell-by-Cell Formula Trace

Sample Data

This table lists products in column A and their prices in column B. Cell D2 contains the product name to look up. We want to find the price of the product named 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 inside the range A2:A4 and returns the corresponding value 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 formula in E2 shows 0.5, which is the price of the product "Banana" found by XLOOKUP.

Sheet Trace Quiz - 3 Questions

What value does the formula look for in the lookup array?

A "Banana"

B "Apple"

C 0.5

D "Cherry"

Key Result

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) searches lookup_array for lookup_value and returns corresponding value from return_array or if_not_found if not found.