Tools / troubleshooting

Use Copilot in Excel to Add a Lookup Column Without Trusting the Match

Cihan Uygur·Intermediate

Cihan's view: Ask Copilot for a lookup column, inspect the suggested formula, and reconcile every returned item against the source inventory before using the result.

Choose another work task →

A lookup can return a plausible value from the wrong row. That is why a generated lookup column needs a small reconciliation step before someone uses it for a stock or purchasing decision.

This guide uses a fictional workbook. No Copilot run, screenshot, or business result is claimed.

Evidence label: documentation-based and illustrative. Microsoft documents that Copilot in Excel can create lookup formulas, including formulas such as XLOOKUP, and that generated content should be reviewed, edited, and verified. The workbook, rows, prompt, and expected output below are fictional. Sources: Get data insights with Copilot in Excel and Understand formulas with Copilot in Excel.

What you will make

You will create a reviewable stock lookup with:

  • an order list;
  • an inventory table;
  • a returned stock quantity;
  • a match status;
  • a formula note for each row;
  • a decision about whether the table is safe for human review.

The goal is to check the lookup. It is not permission to place an order automatically.

Before you start

You need Excel with Copilot available on your account and a workbook you are allowed to use. Microsoft lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel for iPad, and Excel Web App on the cited help page, but access and menu labels can vary by account, plan, version, and workspace.

Use a blank practice workbook or a copy. Do not use live purchasing or customer data for this exercise.

Fictional input

Create two sheets in a blank workbook.

On Orders, paste this into cell A1:

order_id,sku,requested_units
O-501,KB-10,4
O-502,MS-20,2
O-503,HD-30,6
O-504,KB-10,1

On Inventory, paste this into cell A1:

sku,item_name,available_units,checked_on
KB-10,Keyboard,7,2026-10-20
MS-20,Mouse,1,2026-10-20
HD-30,Headset,8,2026-10-20

The expected lookup values are 7, 1, 8, and 7 in order. The practice decision should flag O-502 because requested units are greater than available units. That is arithmetic on fictional rows, not a stock claim.

If you can, convert each range to an Excel table and give them clear names such as OrdersTable and InventoryTable. If your surface does not expose that option, keep the ranges tidy and record the actual range used.

Three steps

1. Prepare both tables

  1. Confirm that each table has one header row.
  2. Confirm that sku is spelled the same way in both sheets.
  3. Check that the inventory table has one row per SKU.
  4. Check that the four order IDs are unique.
  5. Confirm that numeric columns contain numbers.
  6. Keep the source sheets unchanged until the lookup formula is reviewed.

The lookup key is sku. It is not the item name and not the order ID.

2. Ask Copilot for a bounded lookup

  1. Open the Orders sheet.
  2. Select the table or the column where you want the proposed stock value.
  3. Open Copilot from the Excel surface.
  4. Paste this prompt:
Use only the Orders and Inventory tables in this workbook.

Do not place an order, edit the source tables, or infer real stock availability. This is a fictional practice workbook.

Add a proposed column named available_units_lookup to Orders by matching Orders.sku to Inventory.sku.

Then explain:
1. the exact lookup formula or method you used;
2. the lookup key and the source return column;
3. what happens if a SKU has no match;
4. whether duplicate SKUs in Inventory could create an unsafe result.

Also propose a proposed_status column with:
- REVIEW when available_units_lookup is UNKNOWN or requested_units is greater than available_units_lookup;
- CHECK when requested_units is less than or equal to available_units_lookup.

Do not hide unmatched or duplicate keys. Show the source SKU for every returned value.
  1. Review the suggested formula before accepting an edit. Microsoft documents that Copilot can create lookup formulas and explain formulas. Treat the explanation as a draft to check, not as proof.
  2. If the formula is not visible, ask Copilot to explain the selected formula, then copy the formula from Excel’s formula bar into your review note.

A no-Copilot alternative is to create the lookup with ordinary Excel tools, then use the same reconciliation checks below. The decision standard does not change.

3. Reconcile every match

Complete this note:

Workbook: inventory lookup practice copy
Lookup key: sku
Return column: available_units
Formula reviewed: [exact formula or UNKNOWN]
O-501: expected 7, returned [value], status [CHECK / REVIEW]
O-502: expected 1, returned [value], status [CHECK / REVIEW]
O-503: expected 8, returned [value], status [CHECK / REVIEW]
O-504: expected 7, returned [value], status [CHECK / REVIEW]
Duplicate inventory SKUs: [value]
Unmatched order SKUs: [value]
Decision: [USE IN HUMAN REVIEW / CORRECT THEN RECHECK / STOP]

Then check the result yourself:

  1. Compare each returned quantity with the matching row in Inventory.
  2. Confirm O-501 and O-504 both resolve to KB-10 and both return 7.
  3. Confirm O-502 is flagged because 2 requested units exceeds 1 available unit.
  4. Confirm O-503 is not flagged because 6 is below 8.
  5. Check that there is no duplicate sku in Inventory.
  6. Check that the formula does not point to the Orders table as its return source.

Checkable artifact

The artifact is the Orders table with the proposed lookup and status columns, plus the reconciliation note. Someone else should be able to inspect the formula, trace every SKU, and repeat the four comparisons without asking Copilot another question.

Do not delete the original columns. Keep the proposed columns separate until a person accepts the review.

Acceptance checks

The workflow passes when:

  • all four order IDs remain present exactly once;
  • each order SKU matches the intended inventory row;
  • the formula uses sku as the key and available_units as the return value;
  • both KB-10 rows return 7;
  • O-502 is marked REVIEW under the stated fictional rule;
  • O-503 is marked CHECK;
  • unmatched and duplicate keys are visible;
  • the exact formula is recorded or marked UNKNOWN;
  • a human records the next decision.

If the output does not match the expected practice values, stop and inspect the ranges. Do not repair the result by changing the expected values.

Troubleshooting

Every lookup returns the same value. Check the selected key column and the return range. The formula may be using a fixed value or the wrong table. Ask Copilot to explain the formula and compare it with the formula bar.

A SKU returns no match. Check spaces, spelling, and the actual table range. Do not trim or rewrite source values without recording the change. Mark the row REVIEW until the key is resolved.

There are duplicate SKUs in inventory. Stop. Decide which row is authoritative before using a lookup. A matching formula does not tell you which duplicate should win.

Copilot changes the source table. Undo or return to the practice copy, record what happened, and inspect the source before continuing. The workflow is for a proposed lookup, not an automatic purchasing action.

The Copilot icon or formula explanation is missing. Confirm the Excel surface and account access, then use ordinary Excel formula tools and the same reconciliation note. Do not claim that Copilot performed an action when it was unavailable.

The formula explanation sounds confident but is vague. Read the exact formula from the formula bar. Verify the key range, return range, and error behavior yourself.

Limits and decision

A lookup check can expose wrong ranges, missing keys, and duplicate keys. It cannot prove that the inventory snapshot is current or that a purchase is commercially justified. The practice date is fictional and should not be treated as live inventory evidence.

TRY this on a fictional workbook or an approved copy when you can inspect every match.

SKIP Copilot when the workbook contains sensitive live data you cannot use in the chosen workspace, or when no one can review the formula and source rows.

USE the result only as a human review table after the formula, keys, return values, exceptions, and decision note agree.

For the product’s documented lookup and formula features, see Get data insights with Copilot in Excel.

About Cihan

Creator and operator focused on practical AI for business professionals. Background and editorial approach →