Article summary
An AI-generated Excel formula should be reviewed as untrusted working code: understand the question, test known examples and examine exceptions before using the result. A formula that calculates successfully can still answer the wrong business question. This guide uses fictional records to show how an apparently reasonable lookup hides duplicate ambiguity, and how small visible checks make the conclusion easier to review.
Set up a reproducible fictional example
Use one worksheet. Put the headers Reference, Currency and Amount in A1:C1, and the four rows below in A2:C5. Put QueryReference, QueryCurrency and QueryAmount in G1:I1. Enter R202, USD and numeric 200 in G2:I2. All records are invented. Keep the source rows unchanged and use separate cells for calculations.
| Excel row | Reference | Currency | Amount |
|---|---|---|---|
| 2 | R201 | USD | 100 |
| 3 | R202 | USD | 200 |
| 4 | R202 | USD | 250 |
| 5 | R203 | EUR | 300 |
Why a plausible lookup gives the wrong conclusion
Consider this tempting formula: =IF(XLOOKUP(G2,$A$2:$A$5,$C$2:$C$5,"MISSING",0)=I2,"MATCH","REVIEW"). It returns MATCH for the supplied R202 query because the first matching reference has amount 200. It does not establish uniqueness or compare currency. Microsoft's XLOOKUP documentation explains the first-match behaviour; selecting a first row is not the same as resolving a duplicate.
Check Microsoft's XLOOKUP behaviour and supported versions
XLOOKUP is available in Microsoft 365 and newer perpetual Excel editions such as Excel 2021, but not in Excel 2016 or 2019. The exercises below use English function names and comma separators. Localised installations may use different names or separators. Before reusing any formula, confirm the workbook's version and input assumptions rather than asking an assistant to keep rewriting an unexplained error.
Make the checks visible in helper cells
| Cell | Purpose | Formula |
|---|---|---|
| J2 | Candidate rows with this reference and currency | =COUNTIFS($A$2:$A$5,G2,$B$2:$B$5,H2) |
| K2 | Candidate rows also agreeing on amount | =COUNTIFS($A$2:$A$5,G2,$B$2:$B$5,H2,$C$2:$C$5,I2) |
| L2 | Candidate rows with a numeric source amount | =SUMPRODUCT(($A$2:$A$5=G2)*($B$2:$B$5=H2)*ISNUMBER($C$2:$C$5)) |
| M2 | Review unless the input and all three checks agree | =IF(OR(G2="",H2="",I2="",NOT(ISNUMBER(I2))),"REVIEW",IF(AND(J2=1,K2=1,L2=1),"MATCH","REVIEW")) |
For R202/USD/200, J2 is 2, K2 is 1 and L2 is 2, so M2 is REVIEW. The amount exists, but the candidate reference/currency is not unique. For R201/USD/100 the three checks are all 1, so the result is MATCH. Breaking the logic into cells lets a reviewer see which condition failed rather than trusting a large nested formula with no explanation.
Test the edges, not only the happy path
| Query | Expected M2 | Reason |
|---|---|---|
| R201 / USD / 100 | MATCH | One numeric source row agrees |
| R202 / USD / 200 | REVIEW | Two candidate rows; the first lookup hides ambiguity |
| R203 / USD / 300 | REVIEW | Source currency is EUR |
| R999 / USD / 100 | REVIEW | No candidate reference |
| R201 / USD / blank | REVIEW | A missing amount is not confirmed zero |
| R201 / USD / text '100' | REVIEW | The query amount is not numeric |
As an additional test, temporarily change the R201 source amount to blank and query numeric zero; the numeric-source check must still force REVIEW. Restore the original input afterwards and retain the test record. The formula has a deliberately limited scope: plain alphanumeric identifiers, case-insensitive comparisons, exact amounts and no grouped settlement. Wildcard characters, case-sensitive keys, tolerances, date windows and one-to-many rules require a separately designed and tested method.
Document what you verified
Keep the prompt or problem statement, source version, proposed formula, test inputs, expected and observed outputs, changes and remaining limits. Do not upload customer records to an unapproved AI service. Treat any suggested correction as another version to test. A reviewer needs to understand the business meaning of the data as well as the spreadsheet syntax.
Review financial data-quality checks before matching
Choose between Excel, Python and AI by task
Explore financial modelling and valuation practice
Technical review: 28 October 2026. The displayed helper formulas and six query cases, plus the blank-source test, were evaluated in a separate unsaved Microsoft Excel workbook, version 16.0, build 20527. The weak lookup also reproduced its false MATCH. This is a bounded example, not a production reconciliation engine; edits to inputs or formulas require a fresh check.