Excel · 5 min read

The Excel formula that replaces an hour of copy-paste

If you still match data between two spreadsheets by hand — scrolling, copying, pasting, hoping you got the right row — one formula will give you that hour back. Every week. It's called XLOOKUP, and here's how it works in plain English.

The problem it solves

You have two spreadsheets. One has a list of customer IDs and their names. The other has customer IDs and their latest order value. You need names and order values in one place.

The manual way: sort both, eyeball them side by side, copy across, and pray no rows are misaligned. On a few hundred rows that's an hour of tedious, error-prone work. XLOOKUP does it in seconds and never mis-aligns a row.

What XLOOKUP actually does

In one sentence: "Find this value in that column, and give me back the matching value from this other column."

That's it. If you can say that sentence about your data, you can use XLOOKUP.

The formula

=XLOOKUP(lookup_value, lookup_array, return_array)

Three parts:

lookup_value — what you're searching for (e.g. the customer ID in this row)
lookup_array — the column to search in (the customer IDs in the other sheet)
return_array — the column to pull the answer from (the names, or the order values)

A real example

Say your order sheet has customer IDs in column A, and you want to pull each customer's name from a second sheet called Customers, where IDs are in column A and names in column B:

=XLOOKUP(A2, Customers!A:A, Customers!B:B)

Read it as: "Take the ID in A2, find it in the Customers ID column, and give me the matching name." Drag it down the column and every row fills itself in. Done.

If you've used VLOOKUP before: XLOOKUP is its simpler, less breakable replacement. It doesn't care whether the answer column is left or right of the lookup column, and it doesn't break when someone inserts a column. If you're on a recent version of Excel or Microsoft 365, switch — you won't miss VLOOKUP.

The one extra bit worth knowing

Sometimes the value won't be found (a typo, a missing ID). By default XLOOKUP returns an ugly #N/A. Add a fourth part to show something friendly instead:

=XLOOKUP(A2, Customers!A:A, Customers!B:B, "Not found")

Now missing matches say Not found instead of an error — much easier to spot and fix.

Try it on your own messy sheet

The fastest way to learn this is to open a real spreadsheet you already struggle with and replace one manual matching task. Once it clicks, you'll see it everywhere — reconciliations, joining reports, enriching lists. It's one of those small skills that quietly saves hours.

That's the whole idea behind how I teach: one practical skill at a time, on the kind of data you actually work with — not textbook examples.

Want more of these, in your inbox?

A short, useful email a few times a month — a tip, a template, or a walkthrough you can use at work. Plus my Data & AI Skills Roadmap when you join.

Get the roadmap →