XLOOKUP

01The problem

The director asks: "How much did we sell in Lagos? And how much in Beverages?" The orders table has customer_id and product_id, but no region and no category. Those live in customers.csv and products.csv. You need to bring them across, row by row. That's a lookup.

02The concept

XLOOKUP finds a value in one column and returns the matching value from another:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

• lookup_value: what you're looking for (this order's customer_id)
• lookup_array: where to look for it (the customer_id column of Customers)
• return_array: what to bring back (the region column of Customers)
• if_not_found: optional text to show instead of #N/A

Region for each order line:

=XLOOKUP([@customer_id], Customers[customer_id], Customers[region], "Not found")

XLOOKUP matches exactly by default, which is what you want for IDs.

In older files you'll meet two other methods:

=VLOOKUP(C2, Customers!A:H, 4, FALSE)
=INDEX(Customers!D:D, MATCH(C2, Customers!A:A, 0))

• VLOOKUP needs the ID in the first column and a column number (4). Insert a column and the number silently points at the wrong data. Always use FALSE for exact match; the default (TRUE) returns wrong values on unsorted data.
• INDEX/MATCH was the robust choice before XLOOKUP and still works everywhere.

Google Sheets supports XLOOKUP too.

03Example

Order line 10001 has customer_id 27 and product_id 3.

• XLOOKUP(27, Customers[customer_id], Customers[region]) returns the region of customer 27.
• XLOOKUP(3, Products[product_id], Products[category]) returns Beverages (product 3 is Orange juice 1L).

After the walkthrough below, the Orders table has three looked-up columns:

04Walkthrough

1. Load customers.csv and products.csv into the same workbook as Tables named Customers and Products (Data → From Text/CSV, as in lesson 2).
2. In the Orders table, add a column region: =XLOOKUP([@customer_id], Customers[customer_id], Customers[region], "Not found")
3. Add a column category: =XLOOKUP([@product_id], Products[product_id], Products[category], "Not found")
4. Filter each new column for "Not found". There should be none; if there are, some IDs don't match.
5. Now combine with SUMIFS from the last lesson: =SUMIFS(Orders[revenue], Orders[region], "Lagos")

05Practice

06Check your understanding