Audit an AI VLOOKUP formula for exact IDs and honest missing matches

Define identity before asking for a formula

Look up the numeric ID in D2 against A2:B4 and return its owner. IDs are identities, not price bands or interval thresholds: only equal IDs qualify. The table is deliberately sorted to remove ordering as a competing explanation.

The exercise uses numeric 100, 200 and 300, without leading zeros or numeric text. If a real system treats 00100 as a distinct string, preserve that identity rather than coercing it to 100 just to make a formula work.

A2:B4 contains numeric IDs and owners: 100→A, 200→B, 300→C. The query is in D2.
Require exact equality. Keep an unmatched state; never borrow a nearby ID.
Write an Excel formula, explain the match argument and predict results for D2=200, 250 and 50.
Do not erase all errors into blanks. Explain why duplicate IDs need a separate check.

A wrong result need not produce an Excel error

Microsoft’s VLOOKUP reference documents approximate matching when range_lookup is omitted and sorting for TRUE mode. In this exercise, =VLOOKUP(D2,$A$2:$B$4,2) can return B from the 200 row when D2 is 250. That is meaningful for a band lookup, but violates this task’s identity rule.

Testing only D2=200 is insufficient: the flawed formula also returns B there. Query 250 distinguishes exact equality from the nearby lower entry and exposes the otherwise plausible mistake.

Keep cases that distinguish the behaviors

With explicit FALSE, 200 returns B, while 250 and 50 return #N/A. Do not blanket-replace every error with an empty cell that downstream work may interpret as merely unfilled. If a readable label is required, IFNA can handle the unmatched case while a separate exception count remains visible.

Microsoft’s XLOOKUP documentation identifies exact matching as its default and notes that Excel 2016 and 2019 do not provide it. Where supported, =XLOOKUP(D2,$A$2:$A$4,$B$2:$B$4,"Not found",0) makes mode 0 explicit. A newer function name does not validate compatibility or data quality.

Verify in the actual workbook

Copy the exercise into a blank workbook and confirm range, column order and numeric types. Enter queries and formulas separately and compare each result with the reference. Record Excel version and regional syntax; adapt separators where necessary without changing field meaning. These are reproducible expectations, not a claim of execution in your workbook.

Then append 200→D and extend the range. Exact matching establishes equality, not uniqueness: a lookup may return the first match while hiding a conflict. Count rows per ID and resolve duplicates under an explicit rule rather than treating return order as a business decision.

The stable-ID and name-collision guide explains identity keys. The AI spreadsheet formula acceptance guide helps preserve inputs, ranges and expectations.