Tigist matches 1,240 coffee lot numbers to their contract prices in Hawassa. The sheet worked for two years, then a colleague inserted a column for certification, and every price on it became a moisture percentage.
Nothing reported an error. That is the specific failure of VLOOKUP, and it is why the function is still worth understanding and no longer worth writing.
Why counting columns breaks
=VLOOKUP(A2,Contracts!A:H,6,FALSE) says: find the value from A2 in the first column of that range, then return whatever is in the sixth column.
Positional index hazard
The visible calculation and the three silent failure modes
VLOOKUP runs without throwing an error when an inserted column breaks the formula, because nothing syntactically invalid took place.
- Calculation runs smoothly=VLOOKUP(A2,Contracts!A:H,6,FALSE) evaluates to a valid output cell
- Hardcoded column offset shiftsInserting any column to the left makes index 6 point to wrong data
- Locked rightward directionLookup key must be in column 1, preventing leftward column searches
- Silent approximate match defaultOmitting FALSE returns near-misses on unsorted rows
Because Excel sees a valid cell coordinate, it reports no error while returning an incorrect contract price.
The sixth column is a position, not a name. Insert a column anywhere to the left of it and the sixth column is now something else, the formula still runs, and the answer is confidently wrong. No error value appears because nothing invalid happened.
Two more constraints. The key must be in the first column of the range, so it cannot look leftwards at all. And the fourth argument, FALSE, is what forces an exact match; omitting it gives approximate matching, which on unsorted data returns near-misses that look like answers.
XLOOKUP, which is what to write now
=XLOOKUP(A2,Contracts[Lot],Contracts[Price]) names the column to search and the column to return, separately, as two ranges.
Modern lookup comparison
How XLOOKUP fixes the structural faults of VLOOKUP
XLOOKUP names search and return ranges independently, removing column arithmetic and silent match failures.
Naming the search and return columns separately turns column insertion into a non-breaking edit.
Because both are named rather than counted, inserting a column changes nothing. Because they are separate, the returned column can be anywhere, including to the left of the key. And it matches exactly by default, so the commonest silent fault is gone.
A fourth argument gives what to show when nothing matches, so =XLOOKUP(A2,Contracts[Lot],Contracts[Price],"no contract") is more useful than #N/A and does not require wrapping the whole thing in something else.
XLOOKUP arrived in Microsoft 365 in 2019 and is in Excel 2021 and later. In an older version, or a file that has to open in one, the equivalent is INDEX and MATCH.
INDEX and MATCH, and why it is worth knowing
=INDEX(Contracts[Price],MATCH(A2,Contracts[Lot],0)) reads inside out. MATCH finds which row A2 sits in. INDEX returns that row of the price column. The 0 is the exact-match argument, and it is the one people forget.
Two-function coordination
How MATCH and INDEX interlock to locate and return data
MATCH resolves the row coordinate and INDEX retrieves the value, working in every version of Excel ever released.
- MATCH(A2, Contracts[Lot], 0)
- INDEX(Contracts[Price], row)
- MATCH(A2, Contracts[Lot], 0)Finds the exact row position of the key, where 0 enforces exact matching
- INDEX(Contracts[Price], row)Extracts the value from the price column at the exact row returned by MATCH
Separating row discovery from value retrieval gives INDEX and MATCH full immunity from inserted columns.
It works in every version of Excel ever shipped, it survives inserted columns, and it can look leftwards. It is two functions instead of one, which is the only reason XLOOKUP replaced it.
The four reasons a match fails
The types differ. One side is the number 4021 and the other is the text "4021", which look identical and are not. =ISNUMBER() on each side settles it in seconds.
A trailing space. Imported keys carry them constantly, and TRIM() removes them.
Invisible characters from a web or system export, which TRIM does not touch and CLEAN() does.
And a genuine absence, which is the case where #N/A is the correct answer and the sheet should say so rather than hide it.
Tigist's failures were 31 lots out of 1,240: 24 trailing spaces, 5 stored as text, and 2 that genuinely had no contract yet.
Matching on two things at once
A lot number is unique; a price by origin and grade is not. The reliable answer is to build one key from the parts: a helper column of =A2&"|"&B2 on both sheets, then look that up.
Composite key construction
Bridging non-unique fields to an exact lookup match
Concatenating origin and grade creates an unambiguous single key when neither field is unique on its own.
- 01Concatenate fieldsFormula =A2&"|"&B2 joins the two attributes on both sheets
- 02Insert separatorThe pipe delimiter prevents '12' and '34' colliding with '1' and '234'
- 03Execute single lookupXLOOKUP searches the helper column to return the exact contract rate
A delimiter between joined values prevents accidental substring collisions across different attributes.
The separator matters. Without one, "12" and "34" join to the same string as "1" and "234", which produces wrong matches rather than missing ones.
The habit that prevents most of this
Look up against a table, not a range. =XLOOKUP(A2,Contracts[Lot],Contracts[Price]) keeps working when the contract sheet gains 300 rows; =XLOOKUP(A2,B2:B900,F2:F900) silently stops covering them.
Data structure resilience
Structured table references versus fixed range coordinates
Formulas referencing structured table columns adapt automatically when new records arrive.
- Structured Table Reference=XLOOKUP(A2,Contracts[Lot],Contracts[Price])
- Expands automatically when contract sheet gains 300 rows
- Names columns by header rather than static coordinates
- Self-documenting formulas stay readable across teams
- Fixed Grid Coordinates=XLOOKUP(A2,B2:B900,F2:F900)
- Silently stops covering data when records exceed row 900
- Requires manual formula edits every time new rows arrive
- Coordinates give no visual clue about what data they point to
Reference Stability
Tables expand their boundary ranges dynamically, keeping formulas accurate as source rows increase.
What to use, as of 2026
XLOOKUP, in Microsoft 365 and Excel 2021 or later, which replaced VLOOKUP's column counting and its approximate-match default. It is the answer unless the file has to open somewhere older.
Version compatibility check
One question decides it, and neither answer is VLOOKUP
VLOOKUP is on neither arm. It is worth recognising in an inherited sheet and it is not worth writing, because its failure returns a number rather than an error.
- noXLOOKUPNames both columns, matches exactly by default, and can look leftwards
- =XLOOKUP(A2,Contracts[Lot],Contracts[Price])
- a fourth argument replaces #N/A with your own words
- surviving an inserted column costs nothing
- yesINDEX and MATCHTwo functions, every version ever shipped, and the same immunity
- =INDEX(Contracts[Price],MATCH(A2,Contracts[Lot],0))
- the 0 is the exact-match argument
- forgetting it is the commonest fault
The question is about compatibility rather than capability: both arms survive an inserted column and both match exactly.
INDEX and MATCH for compatibility, in every version, and still the thing to recognise in an inherited sheet.
Google Sheets has XLOOKUP too, so the same formula moves between them unchanged.
A model will convert a VLOOKUP into an XLOOKUP correctly, free on every major assistant, and it is a good use of thirty seconds on an inherited sheet. It cannot see whether your two key columns hold the same type, and that is what actually breaks lookups.
The #N/A this returns and what it means is building one, and why it breaks, and the trailing spaces and text-numbers that cause 29 of Tigist's 31 failures are text, dates and the imported mess. Summarising the matched result by origin and grade is summarising thousands of rows in seconds, under the most used analysis tool in the world.