Faragiri

Learn Lookup and reference Excel

A coffee exporter in Hawassa matches 1,240 lot numbers to prices: why the old function breaks silently, the two that do not, and the four reasons a match fails.

7Sections
4Min read
4Depth

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
Formula returns an answer with no error reported
  • 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.

VLOOKUPPositional block reference
XLOOKUPIndependent range vectors
Column insertionSilently shifts the targeted column positionNamed ranges Contracts[Lot] and Contracts[Price] stay immune
Search directionRestricted strictly to columns to the right of keyReturns data from any column including to the left
Match mode defaultApproximate match unless FALSE is explicitly typedExact match enforced by default without extra arguments
Missing key handlingThrows #N/A requiring outer IFERROR wrapperBuilt-in fourth argument replaces missing matches

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.

Audit of 31 failed lookups

Why 31 coffee lot numbers failed to match their contracts

Out of 1,240 records, 29 failures were caused by formatting discrepancies rather than missing data.

lots

  1. Trailing spacesImported strings with hidden spaces removed by TRIM()24
  2. Stored as textNumeric 4021 matched against text '4021', detected by ISNUMBER()5
  3. No contract yetGenuine absence where #N/A correctly flags missing contracts2

Verifying data types with ISNUMBER and stripping whitespace clears 29 of Tigist's 31 lookup faults.

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.

Non-unique componentsOrigin in A2 and grade in B2 shared across multiple lots
  1. 01Concatenate fieldsFormula =A2&"|"&B2 joins the two attributes on both sheets
  2. 02Insert separatorThe pipe delimiter prevents '12' and '34' colliding with '1' and '234'
  3. 03Execute single lookupXLOOKUP searches the helper column to return the exact contract rate
Exact price matchUnambiguous contract row retrieved without collisions

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.

Does this file have to open in a version older than Excel 2021?
  • no
    XLOOKUPNames 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
  • yes
    INDEX 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.

Alongside this

Above this