XLOOKUP: Excel's Matchmaker for Related Lists

Introduce your lists to each other, and unmask the ghosts while you're at it.

Picture two lists that clearly belong together. One is a list of orders, and each order has a Customer ID. The other is a list of customers, with each customer's name and city. They're related, but they've never been properly introduced. So every time someone asks "who placed order 5002?", you end up scrolling back and forth between sheets.

That's a job for a matchmaker, and in Excel, the best matchmaker is XLOOKUP.

If you work with databases, this idea will sound familiar. The Customer ID in the orders list is what database folks call a foreign key, and it points to the customer's primary key in the customer list. XLOOKUP uses that shared value to bring related information together, no database required.

XLOOKUP is available in Microsoft 365, Excel 2021 and later, and Excel for the web. If you're using Excel 2019 or earlier, VLOOKUP or INDEX and MATCH can do similar work.

How XLOOKUP Works

XLOOKUP needs three pieces of information, plus a few optional ones:

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

• lookup_value: what you're looking for, such as the Customer ID on this order.
• lookup_array: where to look for it, such as the Customer ID column in the customer list.
• return_array: what to bring back, such as the Customer Name column.
• if_not_found (optional): what to show when there's no match. This one is the ghost detector.

The examples below use an Orders sheet (Order ID in column A, Customer ID in column B, Order Date in column C) and a Customers sheet (Customer ID in column A, Customer Name in column B, City in column C). Just like the practice workbook, the Orders headings are in row 16 and the first order is in row 17.

Order 5002 has Customer ID 1003. XLOOKUP finds 1003 on the Customers sheet and brings back the name next to it, Sarah Olsen (both highlighted in orange). Order 5009 points to customer 1099, who doesn't exist, so there's nothing to bring back. That's a ghost. Order 5021 is sneakier: customer 1004 is right there on the Customers sheet, yet there's still no match. Notice that its 1004 sits on the left side of the cell with a small green triangle in the corner. Tip 2 explains why.

1. Make Your First Match

1. On the Orders sheet, click cell D16 and type the heading Customer Name.
   Tip: to make your new heading match the others, click C16, click Format Painter (the paintbrush on the Home tab), then click D16.
2. In D17, type:
   =XLOOKUP(B17, Customers!A:A, Customers!B:B)
3. Press Enter. The customer's name appears next to the order.
4. Double-click the fill handle (the small square at the bottom-right corner of the cell) to copy the formula down the column.

Here's how each piece of the formula lines up with XLOOKUP's arguments. The last three arguments are optional, so this formula leaves them out. Without them, XLOOKUP looks for an exact match, searching from the top down.

Customers!A:A means the entire column A on the Customers sheet. Using whole columns keeps the formula simple, and it automatically includes customers you add later.

Outcome: Every order shows its customer's name automatically, so reports are easier to read and nobody has to look up names by hand.

2. Unmask the Ghosts with "If Not Found"

What happens when an order has a Customer ID that doesn't exist in the customer list? Maybe the customer was deleted, or someone mistyped the ID. By default, XLOOKUP shows #N/A. That's accurate, but not very friendly. These orders are orphans: records that point to a parent that isn't there.

1. Add a fourth argument to the formula in D17:
   =XLOOKUP(B17, Customers!A:A, Customers!B:B, "Customer not found")
2. Copy the formula down the column again.
3. Click any cell in your data and press Ctrl Shift L to turn on filters.
4. Use the filter arrow on the Customer Name column to show only Customer not found.

A common ghost in disguise: look at order 5021 in the example above. Customer 1004 exists, so why does it still say "Customer not found"? Because on the Orders sheet, 1004 was stored as text, while on the Customers sheet it's a number. To Excel, the number 1004 and the text "1004" are not the same thing. Two clues give it away: numbers normally line up on the right side of a cell, but text lines up on the left, and Excel marks a number stored as text with a small green triangle in the corner. To fix it, click the cell, click the warning icon that appears, and choose Convert to Number.

Outcome: You get an instant list of orders that don't match a real customer, so you can fix them before they cause wrong totals or embarrassing mistakes in a report.

3. Bring Back Several Columns at Once

XLOOKUP can return more than one column. Instead of writing one formula for the name and another for the city, you can get both at the same time.

1. Click E16 and type the heading City, then make sure the cells below it are empty.
   Tip: to make your new heading match the others, click C16, click Format Painter (the paintbrush on the Home tab), then click E16. (Double-click Format Painter instead, and you can paint both D16 and E16 at once. Press Esc when you're done.)
2. In D17, change the return array to include both columns:
   =XLOOKUP(B17, Customers!A:A, Customers!B:C, "Customer not found")
   The only change is B:B becoming B:C, which tells XLOOKUP to bring back columns B through C.
3. Press Enter. The name appears in D17 and the city appears in E17.
4. Copy the formula down the column.

This is called spilling: a single formula returns several results, and Excel "spills" them into the neighboring cells. If something is already in the way, you'll see a #SPILL! error. Just clear the blocking cells and the results will appear.

Outcome: Fewer formulas to write and maintain, and all the related details show up together.

4. Use Excel Tables for Easier, Self-Updating Formulas

Whole-column references like Customers!A:A work well, but they don't say much about what they're looking up. Turning each list into an Excel Table gives your columns real names, so anyone can read the formula at a glance.

1. If you followed tip 3, first delete the formulas in your Customer Name and City columns. Excel Tables don't support spilled formulas, so it's easiest to start fresh.
2. Click anywhere in the customer list and press Ctrl T. Make sure My table has headers is checked, and click OK.
3. On the Table Design tab, type a name in the Table Name box, such as tblCustomer. Do the same for the orders list (for example, tblOrder).
4. In the first empty column of the orders table, type:
   =XLOOKUP([@[Customer ID]], tblCustomer[Customer ID], tblCustomer[Customer Name], "Customer not found")
5. Press Enter. Excel fills the formula down the entire column automatically.

Same arguments, new names. Only the last two are left out this time. [@[Customer ID]] means "the Customer ID in this row," and tblCustomer[Customer ID] means "the Customer ID column of the customer table," just like Customers!A:A did before.

Outcome: New customers and new orders are included automatically, and anyone reading the formula can tell what it does at a glance.

Why XLOOKUP Beats VLOOKUP

• It looks in any direction. The column you return can be to the left or right of the column you search.
• It uses an exact match by default. No more forgetting to add FALSE at the end.
• It doesn't break when you insert columns. You point to the actual columns instead of counting them.
• It handles missing matches gracefully with the built-in "if not found" option.

Good Relationships Start with Good Keys

XLOOKUP works best when each customer has a unique ID and both lists store that ID the same way. That's the same principle that keeps databases healthy. When the connections between your lists are solid, your reports are accurate, and the ghosts have nowhere to hide.

Have lists that should be connected but aren't? Just contact us and we'd be happy to brainstorm ideas with you.