Excel Lookup Formula: VLOOKUP, XLOOKUP or INDEX/MATCH

"Excel lookup formula" almost always means one specific job: find a value in one column, and return the matching value from another column in the same row — a customer ID to a name, a product code to a price. Three formulas do this, they are not interchangeable in every case, and the default settings on the oldest of the three are the most common source of a silently wrong answer.

The three options, and where they actually differ

• —VLOOKUP — the value you are searching for must sit in the leftmost column of the range. Its match type defaults to approximate rather than exact, so leaving out the final FALSE argument can return the wrong row entirely on unsorted data, with no error to flag it.
• —XLOOKUP — can search and return any column in either direction, not just left to right, defaults to an exact match, and takes a built-in "if not found" message instead of a bare #N/A. Only available in Excel 365 and Excel 2021 onward, so it does nothing in an older Excel file.
• —INDEX/MATCH — two functions combined to do what XLOOKUP does on its own: MATCH finds the row, INDEX returns the value from it. More to type, but it has worked in every version of Excel for decades, which is the only real reason to still reach for it over XLOOKUP today.

A worked example

Say a Table named Staff holds ID, Name and Department, and you need the Department for ID "E1042" sitting on a different sheet. All three read:

• —VLOOKUP: =VLOOKUP("E1042", Staff, 3, FALSE) — the 3 counts columns from the left edge of the range, and the FALSE is what forces an exact match.
• —XLOOKUP: =XLOOKUP("E1042", Staff[ID], Staff[Department]) — names both columns directly, so a column inserted between them does not shift which number "3" was pointing at.
• —INDEX/MATCH: =INDEX(Staff[Department], MATCH("E1042", Staff[ID], 0)) — the 0 inside MATCH is doing the same job as VLOOKUP’s FALSE: forcing an exact match rather than an approximate one.

Drop the FALSE from the VLOOKUP version and, on an ID column that is not sorted, the formula can return the Department from a nearby row instead of the one actually requested — a number that looks entirely normal and is simply attached to the wrong person.

Where this breaks

• —Leaving out the exact-match argument. VLOOKUP’s default is approximate, and on unsorted data that returns a plausible-looking wrong row rather than an error.
• —Duplicate lookup values. All three formulas return only the first match they find — if two rows share the same ID, the second one’s data is simply never retrieved, with nothing telling you that happened.
• —A type or whitespace mismatch. A lookup value stored as text against a source column of numbers, or a value with a trailing space from a pasted export, returns #N/A even though a human would call it a perfect match.
• —A plain cell-range reference instead of a Table reference. Add new rows to the source data later, and a formula written against a fixed range such as A2:C50 does not expand to include them, while a Table-based reference does.

Where AI actually helps, and where it does not

Drafting the right formula from a plain description is a job AI tools handle well — "look up the Department for an ID in the Staff table, returning an exact match only" comes back close to correct on the first try, because it is a translation job from a stated intent to the right syntax. OpenAI and Anthropic both say the same thing in their own prompting guidance: name the table, the columns and the exact-match requirement, and the draft needs far less correcting. Pasting in one real row from your actual table alongside the request works better than describing the columns from memory.

What it will not do is notice a duplicate ID sitting in your actual data, or flag that the "E1042" you typed has a trailing space the source column does not. A widely cited survey of hallucination in large language models covers why a model can describe a lookup as working correctly with total confidence, without ever actually running it against your sheet — fluent and accurate are not the same property, and a dropped duplicate reads exactly as certain as a correct match.

Ask AI to write the lookup formula. Let the formula itself find the row. Never ask AI to look the value up by reading the numbers in a chat window.

Checks before you trust a lookup

1. 01Test it on a row you already know the right answer to before trusting it on the rest of the sheet.
2. 02Check the lookup column for duplicates — a COUNTIF against the same column catches this in one formula, before a silent first-match problem reaches a report.
3. 03Set the exact-match argument explicitly every time, rather than relying on a default you have to remember.
4. 04If a result is #N/A where you expected a match, check for extra whitespace or a text-versus-number mismatch before assuming the row does not exist.
5. 05Convert the source range into a Table with Ctrl+T so the lookup keeps working as rows are added — see Excel Table for exactly what that does and does not fix automatically.

Deciding in advance what you would check for — duplicates, a type mismatch, a stale range — rather than discovering it once a wrong value has already gone into a report, is the same habit behind NIST’s AI Risk Management Framework, scaled down here to one formula instead of a deployed system.

What to do Monday

Pick one VLOOKUP still missing its exact-match argument and fix it first — it is the single most common source of a lookup that looks fine and occasionally is not. Excel Advanced Functions covers XLOOKUP and FILTER alongside the other functions worth letting AI draft and you test, and How to Search in Excel covers the simpler case of finding a value by eye rather than returning a related one with a formula. If the actual friction is adding new rows to the lookup table reliably rather than the formula itself, Excel Form is the data-entry fix that sits upstream of this problem.

If the lookup needs to run automatically rather than sit in one cell someone checks by hand, Code for Excel covers which of VBA, Office Scripts or Python actually fits that job. Online Excel Viewer is worth knowing about separately if the task is only ever checking a value on a machine without Excel installed, not building the lookup itself. Writing the request precisely the first time — naming the table, the columns, the match type — is the entire argument of How to Write a Prompt That Works on the First Try, and it is most of the difference between a formula that works and one you have to debug afterwards.

Coursium teaches that same judgement — a precise request, and a check before you trust what comes back. Stay ahead of AI by learning the tools on your phone.