On Monday morning, an operations teammate opens 200 order rows beside the latest product price list. A mug sold for 39 in row 37, while the price list says 42. Another order has a SKU that cannot be found at all. Asking AI to “find the wrong rows” risks mixing valid discounts, historical prices, and data-entry errors. What the team needs is an exception list that can be traced back to individual rows.
This tutorial uses Excel XLOOKUP for a small reconciliation, then uses AI to explain the formulas and suggest checks. It is for ecommerce, purchasing, and finance operations teams that regularly compare two sheets. All sample data is fictional; there is no reason to upload customer orders to an AI service.
Define the two sheets and the question
Create a workbook with sheets named Products and Orders. In Products, column A is SKU, B is product name, and C is the current standard price. Enter three rows in A2:C4: MUG-01 / Blue mug / 38, MUG-02 / White mug / 42, and BAG-03 / Canvas bag / 79. In Orders, column A is SKU, B is the actual unit price, and C is quantity. Enter four rows in A2:C5: MUG-01 / 38 / 2, MUG-02 / 39 / 1, BAG-03 / 79 / 1, and MUG-99 / 35 / 1.
The goal is not to calculate profit or declare an error. It is to compare each order with the current standard price and label it matched, different, or absent from the price list. The three-unit difference for MUG-02 is only a question to investigate: it could be a promotion or an older price-list version. This simple model does not include order dates, coupons, membership prices, or historical changes. To audit historical sale prices, first build a price-version table with effective dates; today's price list cannot adjudicate last year's order.
Two problems to catch before looking anything up
The first is duplicate SKUs. XLOOKUP returns the first match by default. If Products has two MUG-02 rows priced at 42 and 45, the formula will not decide which one is valid. Enter =COUNTIF($A$2:$A$4,A2) in Products D2 and fill down. If any result exceeds 1, stop and investigate a duplicate entry, shared code, or missing version field. Do not delete a row just to make the lookup work. Repeated SKUs in Orders can be normal because separate orders may buy the same product.
The second is a code that looks identical but is not: MUG-02 versus MUG-02 with a trailing space, or the letter O versus the digit 0. Format SKU columns as text and sample-check whitespace, full-width characters, and case rules. If cleaning is needed, work in a copy and preserve the original value. Do not let AI merge every similar code; two codes may identify different products. Check that prices are numeric rather than currency-formatted text.
Microsoft's XLOOKUP documentation confirms exact matching by default, an optional value for unmatched keys, and first-match behavior. We still write 0 explicitly for exact matching so a reviewer can read the formula quickly. The duplicate check uses COUNTIF.
Ask AI for formulas, not a business verdict
You can use ChatGPT or Claude to draft the formulas. Provide the schema and four fictional rows, not a real sheet containing names, phone numbers, addresses, or discount agreements. Adapt this prompt to your column names:
I have two Excel sheets. Products: A = SKU, B = product name, C = current standard price, rows 2–4. Orders: A = SKU, B = actual unit price, C = quantity, rows 2–5. First remind me to check for duplicate SKUs in Products. Then give formulas for Orders column D (lookup price), E (status), and F (unit-price difference). Use exact matching. Return the text NOT_FOUND for a missing SKU, never zero. Round the difference to two decimal places. Explain each formula and give expected results for all four rows. Do not create macros or scripts, delete source data, or assume every price difference is an error. Mention Excel version and argument-separator requirements.
The prompt limits the input, output, and prohibited actions. If the response merely says “use VLOOKUP,” ask for cell-specific references. If it demands the real order file, switch to a redacted sample. AI can draft a candidate formula; people still own the reconciliation decision.
Three formulas and four expected results
After confirming unique SKUs in Products, enter this in Orders D2 and fill to D5:
=XLOOKUP(A2,Products!$A$2:$A$4,Products!$C$2:$C$4,"NOT_FOUND",0)A2 is the order SKU. Dollar signs keep both ranges fixed as you fill down. The fourth argument makes a missing SKU appear as NOT_FOUND, not a zero price; 0 in the fifth argument requests an exact match. Avoid copying entire-column ranges into a large workbook by default. Start with the real data range, then move to an Excel table or maintain the range when it grows.
In E2, label the result. In F2, calculate actual unit price minus current standard price. Fill both formulas down:
=IF(D2="NOT_FOUND","Price list missing",IF(ROUND(B2-D2,2)=0,"Matched","Review difference"))
=IF(ISNUMBER(D2),ROUND(B2-D2,2),"")Expected results: MUG-01 finds 38, is matched, and has a zero difference. MUG-02 finds 42, is marked for review, and has a difference of -3. BAG-03 finds 79, is matched, and has a zero difference. MUG-99 produces NOT_FOUND in D, “Price list missing” in E, and a blank F. A negative difference only means the sale price is lower than today's standard price; it does not mean the order lost money. If a row differs from this test, inspect SKUs and data types before changing formulas to fit an expected answer.
ROUND(...,2) keeps minor floating-point display differences from changing the comparison, but it does not replace business rules about currency, tax, and discounts. If your Excel locale uses semicolons between function arguments, replace the argument commas accordingly. Microsoft's formula troubleshooting guide notes that separators depend on regional settings. Test one cell before filling the entire column.
Route exceptions instead of “fixing” the source sheet
Filter the “Price list missing” rows: check whether the product was retired, the SKU was mistyped, or the current list omitted a new item. Never replace NOT_FOUND with zero; that would let an unknown price enter later calculations. For “Review difference,” pull in order date, campaign ID, and approval evidence. It could be a valid promotion, membership price, manual override, or genuine error. Add a reason and evidence link in a separate column rather than overwriting the original sale price.
For a real workbook, add two gates. First, every Products SKU count must equal one; otherwise stop the bulk lookup. Second, the counts of matched, review, and missing rows must add up to the number of orders. Inspect the first, last, and several exception rows to catch an incomplete fill range. If actual unit price is blank or text, create a separate “data-format error” state instead of treating it as a price difference. Quantity in column C is only needed later if you estimate total exposure by multiplying unit-price difference by quantity. Define how returns, gifts, and tax are treated before doing so.
As the workbook grows or multiple price lists arrive regularly, consider an Excel table or Power Query refresh rather than dragging formulas every week. AI can help draft a field dictionary, test cases, and handover notes; it should not rewrite master data without review. Hand off a copy containing source-file dates, the price-list version, formulas, exception queues, and reviewer name.
When XLOOKUP is unavailable
Microsoft says XLOOKUP is unavailable in Excel 2016 and 2019. If you see #NAME?, check the Excel version before asking AI to “fix” syntax repeatedly. In an older version, an exact-match VLOOKUP can substitute after duplicate SKUs have been ruled out:
=IFERROR(VLOOKUP(A2,Products!$A$2:$C$4,3,FALSE),"NOT_FOUND")This depends on SKU being the first column of the lookup range; FALSE requires an exact match. If the table layout is likely to change, design stable field positions or a more suitable join rather than merely asking AI to change the column index from 3 to 4. Also, IFERROR turns other formula errors into NOT_FOUND; if the result looks odd, run VLOOKUP on its own to inspect the actual error. The older formula only answers which value to retrieve; it cannot resolve effective dates or authorized discounts.
Keep the four-row test and an exception log. The next time someone asks why an order is highlighted, you can point to its original SKU, price-list row, difference, and campaign evidence. That is far more useful than “AI checked it.”