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

Real data is split across tables, and for good reason. Kolanut has 90 customers and 4,266 order lines. Writing "Alhaji Musa Wholesale, Lagos, Wholesale, Tolu Adeyemi" on every one of that customer's lines would repeat the same facts hundreds of times, and a change of sales rep would mean editing hundreds of rows. So the orders keep only a key, customer_id, and the details live once, in Customers.

A lookup goes the other way: for each order line, take its customer_id, find that customer in Customers, and bring back the detail you need. It's the Excel version of a SQL JOIN.

XLOOKUP

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

Most of the time you only need the first three or four.

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

Read it aloud as: "Take this row's customer ID, find it in the Customers ID column, and give me the region on that row. If you can't find it, say Not found."

Order 10001 is for customer 27. Customer 27 is Alhaji Musa Wholesale, in Lagos, so the cell shows Lagos.

More examples

That last one is interesting: product 3's list price is ₦20,800, but order 10001 was charged ₦18,600 per pack. Comparing the two is how you'd find out how much below list price the sales team sells.

The lookup value doesn't have to come from the same row. To find one customer by hand, type the ID in a cell (say K2) and look it up:

=XLOOKUP(K2, Customers[customer_id], Customers[customer_name], "No such customer")

With 42 in K2, it shows Divine Superstore Uyo. With 999, it shows No such customer.

When there's no match: #N/A

Without if_not_found, a failed lookup shows #N/A. That's not always bad: it tells you something is missing. The common causes:

Duplicates: the silent problem

XLOOKUP returns the first match it finds. If the lookup table has the same ID twice (for example, a customer exported twice with different regions), you'll get the first one and no warning.

Check the key column is unique before you rely on it:

=COUNTA(Customers[customer_id]) = COUNTA(UNIQUE(Customers[customer_id]))

TRUE means every ID appears once. For Kolanut's Customers it's TRUE: 90 rows, 90 different IDs. The messy export in the next lesson is a different story.

Approximate match: putting values into bands

Lookups aren't only for IDs. With match_mode set to -1 ("exact match or the next smaller value"), XLOOKUP puts a number into a band. Make a small table called Bands, sorted by its lower limit:

=XLOOKUP([@revenue], Bands[from], Bands[band], , -1)

A line worth ₦260,400 has no exact match in from, so XLOOKUP takes the next smaller value, 200,000, and returns ₦200k to ₦500k. (The two commas leave if_not_found empty.)

Across all order lines: 614 under ₦50k, 1,891 from ₦50k to ₦200k, 1,599 from ₦200k to ₦500k, and 162 of ₦500k and over.

This is often neater than a long IFS: to change the bands, you edit the little table, not the formula.

VLOOKUP: the older way

You'll meet VLOOKUP in almost every workbook built before 2020.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(C2, Customers!A:H, 4, FALSE)

It works, but it has three traps:

1. The default is approximate. Leave out FALSE and VLOOKUP assumes the table is sorted and returns the nearest value, often the wrong customer, with no error.
2. The column number is fragile. Insert a column in Customers and 4 now points at a different column. Every result changes, silently.
3. It only looks right. The ID has to be the first column, so you can't look up an ID from a name.

XLOOKUP fixes all three, which is why it's the one to learn first.

INDEX and MATCH

Before XLOOKUP, careful analysts used INDEX with MATCH. It still works in every version of Excel and in Google Sheets.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
=INDEX(Customers[region], MATCH([@customer_id], Customers[customer_id], 0))

It's two functions working together:

• MATCH(27, Customers[customer_id], 0) finds which row 27 is on: 27 (customer 27 is the 27th row). The 0 means exact match.
• INDEX(Customers[region], 27) returns the 27th value of the region column: Lagos.

Which one to use

Write XLOOKUP. Read the other two when you inherit a workbook, and if a file has to open in Excel 2016 or earlier, use INDEX/MATCH.

03Example

Which region brings in the most revenue?

Once each order line has a region column from XLOOKUP, SUMIFS from lesson 5 does the rest. Type the six region names in K2:K7 and in L2:

=SUMIFS(Orders[revenue], Orders[region], K2)

The total matches the grand total from lesson 4, so no line was lost or double-counted. Lagos alone is just under half of all revenue.

The same pattern with a category column:

After the walkthrough below, your Orders table will look like this:

04Walkthrough

1. Load the lookup tables. In the same workbook as Orders, load customers.csv and products.csv (Data → From Text/CSV, as in lesson 2). Click in each, press Ctrl + T, and name them Customers and Products (Table Design → Table Name).

2. Check the keys are unique. On any spare cell: =COUNTA(Customers[customer_id]) = COUNTA(UNIQUE(Customers[customer_id])). It should say TRUE. Do the same for Products[product_id].

3. Add region. In the first empty column of Orders, type the header region, then in the first row:

   Excel formula

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

   Copy

   The table fills it down every row.

4. Add channel and category the same way, returning Customers[channel] and Products[category] (looking up [@product_id] in Products[product_id]).

5. Check for misses. Filter each new column: there should be no "Not found".

6. Use them. Total Lagos revenue with =SUMIFS(Orders[revenue], Orders[region], "Lagos"), and check you get ₦411,162,300.

7. Combine two lookups. Lagos revenue from Beverages only:

   Excel formula

   =SUMIFS(Orders[revenue], Orders[region], "Lagos", Orders[category], "Beverages")

   Copy

   That's ₦116,162,310.

Summary

05Practice

06More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

07Check your understanding