Lookup Functions

Excel Fundamentals

Chapter 5 · Lookup Functions: VLOOKUP, INDEX/MATCH, and Modern XLOOKUP

Chapter 4's functions all answered questions about a range you already knew the shape of — sum this, average that, count these. This chapter answers a genuinely different question: "given a key I have, find the matching piece of data I don't have." Given a product ID, find its price. Given an employee name, find their department. This is arguably the single most-used category of formula in real-world spreadsheets, which is exactly why Excel has evolved three different ways to do it.

The Problem Lookups Solve

Imagine a small pricing table in A1:B5 — column A holds product codes ("SKU-100," "SKU-200"...), column B holds each one's price. Given a product code typed into D1, you want a formula in E1 that automatically finds the matching row and returns that row's price — without you manually scrolling through the table yourself every time.

VLOOKUP — the Classic Approach

VLOOKUP ("vertical lookup") searches down the first column of a table for a match, then returns a value from a specified column to its right, counting columns from that same first column:

=VLOOKUP(D1, A1:B5, 2, FALSE) 1. D1 → the value to search for 2. A1:B5 → the table to search — must start with the lookup column 3. 2 → return the value from the 2nd column of that table (column B) 4. FALSE → require an EXACT match, don't guess an approximate one

That fourth argument matters enormously. Leave it out, or set it to TRUE, and VLOOKUP switches to approximate match mode — it assumes the first column is sorted ascending and returns the closest value less than or equal to your search value, silently, with no error, even when that's nowhere near what you meant. For looking up an exact product code, always pass FALSE (or the equivalent 0).

VLOOKUP's Real Limitation: It Only Looks Right

VLOOKUP requires the lookup column to be the leftmost column of the range you give it, and can only return a value from a column to its right. If the price were in column A and the product code in column B, plain VLOOKUP simply couldn't look the data up at all — you'd have to physically rearrange the columns first.

The column-index number is also brittle: it's a hard-coded position count, not a reference to an actual column. Insert a new column anywhere inside the table range, and every existing VLOOKUP's column-index number is now silently wrong, still returning a value, just not the one you originally meant.

INDEX/MATCH — the Classic Workaround

Combining two simpler functions solves both problems at once. MATCH finds a value's position within a range; INDEX returns the value found at a given position within a range:

=MATCH(D1, A1:A5, 0) → returns a plain position number, e.g. 3, if D1 is found in the 3rd row of A1:A5 =INDEX(B1:B5, 3) → returns whatever value is in the 3rd position of B1:B5 Combined — MATCH's result becomes INDEX's position argument: =INDEX(B1:B5, MATCH(D1, A1:A5, 0))

Because INDEX's return range and MATCH's lookup range are specified completely independently of each other, the returned column can sit anywhere relative to the lookup column — including to its left, which plain VLOOKUP can never do. This is the classic reason experienced Excel users reached for INDEX/MATCH over VLOOKUP for years, right up until XLOOKUP arrived.

XLOOKUP — the Modern Replacement

Introduced in Excel 2021 / Microsoft 365 (not available in older standalone Excel versions), XLOOKUP folds VLOOKUP's simplicity and INDEX/MATCH's flexibility into a single function:

=XLOOKUP(D1, A1:A5, B1:B5, "Not found") 1. D1 → the value to search for 2. A1:A5 → the range to search within 3. B1:B5 → the range to return a value from — any position, left or right 4. "Not found" → optional — what to show instead of an #N/A error if there's no match

Two genuine improvements over both of its predecessors: the return range can be anywhere relative to the lookup range (like INDEX/MATCH), and it defaults to an exact match — the safer default that VLOOKUP never had.

FeatureVLOOKUPINDEX/MATCHXLOOKUP
Can look left of the lookup columnNoYesYes
Default match typeApproximate (risky)Exact, if written with 0Exact
Survives an inserted columnNo — column index breaksMostly — uses ranges, not counted positionsYes — uses ranges, not counted positions
Built-in "not found" messageNo — shows #N/ANo — shows #N/AYes — optional 4th argument
Available in every Excel versionYesYesNo — 2021 / Microsoft 365 onward only
The single most important habit from this whole chapter
Whichever function you use, always force an EXACT match explicitly — FALSE or 0 as VLOOKUP's or MATCH's last argument; XLOOKUP already defaults to this safely. Approximate-match lookups on unsorted, real-world data are one of the most common sources of confidently wrong numbers in business spreadsheets, precisely because they never raise an error — they just quietly return the wrong row's value.
VLOOKUP's column-index number doesn't update itself
If you insert a new column anywhere inside a VLOOKUP's table range, every formula that used a hard-coded column-index number (like the 2 in this chapter's first example) keeps using that same number — now pointing at a different column than it originally did. INDEX/MATCH and XLOOKUP both sidestep this entirely, because their return range is its own independent reference rather than a counted offset.

Hands-On Exercises

Exercise 1

A table in A1:B5 lists product codes in column A and prices in column B: SKU-100/9.99, SKU-200/14.50, SKU-300/22.00, SKU-400/7.25. Write a VLOOKUP formula that looks up "SKU-300" and returns its price, making sure to force an exact match. State what the formula returns, and explain what would happen if the FALSE argument were left out entirely and the codes were not sorted alphabetically.

📄 View solution
Exercise 2

Using the same table, but this time with product codes in column B and prices in column A (i.e. the price is now to the LEFT of the code), write an INDEX/MATCH formula that looks up "SKU-200" and returns its price. Explain specifically why a plain VLOOKUP could not solve this version of the problem at all.

📄 View solution
Exercise 3

Write an XLOOKUP formula for the original A1:B5 table (codes in A, prices in B) that looks up a code typed into D1, returns the matching price, and displays "Code not found" instead of an error if D1 doesn't match anything in the table. Explain which one XLOOKUP argument makes this possible, and why VLOOKUP and INDEX/MATCH can't do this on their own.

📄 View solution

Chapter 5 Quick Reference

  • =VLOOKUP(value, table, col_index, FALSE) — searches the table's first column, returns from a column to its right; always pass FALSE for an exact match
  • =INDEX(range, MATCH(value, lookup_range, 0)) — MATCH finds a position, INDEX returns the value at that position; the return range can be anywhere, including left of the lookup range
  • =XLOOKUP(value, lookup_range, return_range, [if_not_found]) — modern, defaults to exact match, can look any direction, has a built-in "not found" message; requires Excel 2021 / Microsoft 365
  • An un-forced approximate match is the single most common lookup bug — it never raises an error, it just silently returns the wrong row
  • VLOOKUP's column-index number is a hard-coded count, not a live reference — an inserted column breaks it silently