Create Lookups With Copilot in Excel: When It Writes XLOOKUP Well and When It Doesn’t

Coding Liquids tutorial cover featuring Sagnik Bhattacharya for creating lookups with Copilot in Excel, with XLOOKUP and review visuals.
Coding Liquids tutorial cover featuring Sagnik Bhattacharya for creating lookups with Copilot in Excel, with XLOOKUP and review visuals.

Copilot in Excel can write XLOOKUP formulas correctly when the table structure is clear, and a well-written prompt saves time on arguments you do not want to remember. It goes wrong when the workbook is messy, the prompt does not state the business rules, or duplicates and missing keys are ignored.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

The problem is that lookup errors can look plausible. Copilot can produce a formula that runs and still points to the wrong column, wrong match mode, or wrong range.

Can Copilot write lookup formulas in Excel?

Copilot in Excel is useful for lookup formulas when the table structure is clear and you already know roughly what the answer should look like. It is less useful when the workbook is messy or the logic depends on subtle business rules that the prompt does not state clearly.

  • The source and lookup tables are already clean and named clearly.
  • You can quickly verify whether the formula is using the right columns.
  • You want a first draft faster than typing the whole formula from scratch.

When does Copilot write XLOOKUP well?

Copilot writes XLOOKUP formulas best when the task is mechanically clear: matching a customer ID to the master table, returning a product category, or pulling a price from the latest rate list. In these cases, the main win is speed, not magic.

If you want to build these skills with an instructor, try The Complete Excel Course with AI Integration on Coding Liquids.

When does Copilot get an XLOOKUP wrong?

Copilot gets lookups wrong mainly through column selection, approximate-versus-exact logic, and ignoring cases such as duplicates or missing keys. These are prompt and review problems, not just AI problems.

If you want help applying this to your own workbooks, I offer a one-to-one AI consultation.

How do I check a Copilot lookup formula?

Check a Copilot lookup formula by confirming the lookup column, the return column, the match mode, and the error handling. Then test the formula on rows where you already know the answer before you fill it across the sheet.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Worked example: customer pricing

A sales sheet needs to pull discount tiers from a master customer table. Copilot can draft the XLOOKUP quickly, but the analyst still checks whether the lookup key is customer ID rather than customer name and whether missing values are handled clearly.

Common mistakes

  • Accepting the first formula because it looks syntactically correct.
  • Prompting with vague field names such as sheet one and sheet two.
  • Skipping test rows before filling the formula down the column.

When to use something else

If the formula itself is not the problem and you need a narrower lookup skill, XMATCH or VLOOKUP vs XLOOKUP may be the better next reads.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

Frequently asked questions

When does Copilot write good lookups?

Copilot writes good lookups when the table structure is clear and you roughly know the expected answer. The win is speed on mechanically clear tasks like matching an ID, returning a category or pulling a price, not magic. If the workbook is messy, expect to correct the formula yourself.

Where do Copilot lookups go wrong?

Copilot lookups go wrong on column selection, approximate-versus-exact match logic, and cases such as duplicates or missing keys. These are prompt-and-review problems as much as AI ones, and a wrong formula can still run without an error, so test it on rows where you already know the answer.

How do I review a generated lookup?

Check four things: the lookup column, the return column, the match mode, and the error handling. Then test the formula on rows where you already know the answer before you fill it across the sheet. A formula that runs without an error can still point at the wrong column.

Will Copilot use XLOOKUP or older functions?

It usually reaches for XLOOKUP on modern Excel, but confirm that rather than assuming it. Also check the match-mode argument, since exact-versus-approximate is the most common silent error. If you need a specific function, name it in the prompt and say whether the match must be exact.

How do I handle missing keys?

Tell Copilot what to return when there is no match, such as a blank or a Not-found label, using XLOOKUP's if-not-found argument. Unhandled misses produce #N/A, which can break downstream formulas. Test with a key you know is missing so you can see what the formula returns.

What about duplicate keys?

Decide which match you want, first or last, and state it in the prompt. A plain lookup returns the first hit, which may not be the latest record, so sort the data or use a position-based approach if the latest matters. Then test with a key you know is duplicated.

Related guides on this site

If you want to keep going without opening dead ends, these are the most useful next reads from this site.

Official references

These official references are useful if you need the product or framework documentation alongside this guide.