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 Inventory—has a column like Product ID, and another—like Price List—has the matching prices for those product 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 Product ID in the Inventory table. The lookup array is the entire key column in the other sheet—the Product ID column in the Price List table. The return array is the column we want to bring over—the Price column. XLOOKUP finds the row where the lookup array matches our lookup value and returns the corresponding cell from the return array.

4. How XLOOKUP works

If there's no match, we might get a blank cell or an error, depending on how the formula is set up. 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

As we're wanting to add data from the Price List table to the Inventory table, we'll keep this table open during our interaction with Copilot, and turn on agent mode. Then, we can ask it to add the Product Price column from the Price List sheet to the Inventory sheet. Copilot will work its magic, and should also show the formula it used along with an explanation. To verify everything is correct, we first take a look at the data in the sheet to check for obvious errors. Then, we inspect the formula to see if the tables and ranges align with what we expect. As with other transformations, our job is to set the goals and verify the results-the legwork is taken care of by Copilot. As well as looking at the ranges and tables, watch out for blanks.

6. Verification checklist

They often mean no match in the lookup list, so verify against the source before assuming an error. If a key appears twice in the lookup list, spot-check which value Copilot used. This is the "trust but verify" mindset required with virtually any AI tool.

7. Let's practice!

Time to connect data across sheets like a pro 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.