Generating Complex Formulas

Creating Lookup Formulas With Copilot

When information is split across multiple tables — for example, students in one sheet and their contact details in another — you usually need to bring matching data into one place. Excel has dedicated functions for this, XLOOKUP and VLOOKUP, but writing them by hand can be fiddly. Copilot generates the formula for you from a plain-language prompt.

Asking Copilot To Build The Lookup

When you have two related tables and want to combine them:

1. Go to the table you want to update;
2. Open Copilot Chat;
3. Describe what you want to bring in and from which sheet.

For example: "Add a column with the email address from the Contacts sheet, matched by Student ID." Copilot inserts the new column and writes the appropriate lookup formula behind the scenes.

Inspecting The Formula

After the column appears, click into any of the cells to see the actual formula Copilot used. Depending on the structure of your data, it may pick XLOOKUP, VLOOKUP, or another lookup function.

Two reasons to actually look:

• Verification — sanity-check that it references the right sheet, the right key column, and the right return column;
• Learning — seeing the formula teaches you the function. Next time you can write something similar yourself, or maintain the formula later when the data structure changes.

Why This Beats Copy-Pasting Values

It might be tempting to just copy values from one sheet into the other. A lookup formula is fundamentally different:

• The formula references the source sheet, it doesn't duplicate the data;
• When the source sheet changes, your column updates automatically;
• You only have one source of truth, not two copies drifting out of sync.

This is what makes lookup formulas worth using even when copy-paste would technically work today.