Lookups you can trust

1. Lookups you can trust

Welcome back! In this video, we'll connect data stored in different places, which is a very common step in many types of analysis.

2. Connecting data from two places

In Excel, we often have data stored in multiple tables: one table - say Donations - has a column like Campaign ID, and another - like Campaigns - has the matching campaign names for those IDs. The columns containing unique identifiers are often called "keys". Having multiple tables may seem like an inconvenience, but it's actually a best practice. Many analyses may not require all of the data we have; instead, data is separated into clear, modular domains and combined as needed during the analysis. Copilot can write the formula to connect these sources, and it often chooses XLOOKUP, which is an extremely flexible Excel function.

3. How XLOOKUP works

XLOOKUP needs three things. The lookup value is the key in the current row - the Campaign ID in the Donations table. The lookup array is the entire key column in the other sheet - the Campaign ID column in the Campaigns table. The return array is the column we want to bring over - the Campaign Name column. XLOOKUP finds the row where the lookup array matches our lookup value and returns the corresponding cell from the return array.

4. When there is no match

If there's no match, nothing comes back. Donation D005 points at campaign C99, which is not in the Campaigns table, so its campaign name is left empty. Depending on how the formula is written, you might see an error there instead. Now, instead of writing these XLOOKUPs by-hand, Copilot will be writing them for us. Our job becomes asking for the right connection and verifying what Copilot proposes. Let's give this a go!

5. Example: Copilot and XLOOKUP

We want the campaign name alongside each donation. The names live on a separate Campaigns sheet, so both sheets need to be in the same workbook, and the mode stays on "Allow editing" so Copilot can add the column for us.

6. Example: Copilot and XLOOKUP

Naming both tables and the column that joins them is what stops Copilot guessing. Say only "add the campaign name" and it has to infer which sheet you meant.

7. Example: Copilot and XLOOKUP

Copilot writes the lookup and fills the column, and it shows you the formula it used, which is worth reading: the ranges tell you whether it matched on the column you intended. Now look at row six. Campaign C99 is not on the Campaigns sheet, so there is nothing to match and Copilot returns "Not found". That is not a mistake, it is the answer. Check for unmatched rows before you trust the result.

8. Verification checklist

Whenever Copilot connects two tables for you, run the same four checks. Start with the results: do the matches look right at a glance? Then read the formula, and confirm the tables and ranges are the ones you meant. Then look for two specific things. A blank usually means the key is missing from the lookup list rather than Copilot failing, so check the source before calling it an error. And if a key appears twice in that list, spot-check which of the two values Copilot took. Ask for the connection, then check what came back. That goes for most AI tools, not just this one.

9. Let's practice!

Time to connect data across sheets with Copilot!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.