Lookups — VLOOKUP, XLOOKUP, INDEX-MATCH — Excel masterclass

🟢

The Foundation

Level 1 · the hook & basics

Imagine walking into a giant library. You do not wander the shelves hunting — you go to the librarian, give the book's code, and they fetch the exact book. A lookup in Excel is exactly this. You hand Excel a key (something you already know), it searches a table (the catalogue), and it hands back the matching detail.

Take an everyday kirana price list: column A has item names, column B has prices. You know the item is Pen; you want its price. Reading it by eye is fine for five rows, but hopeless for five thousand. A lookup does the scanning for you, instantly, every single time.

One golden rule up front: your key should be unique in the list — like a roll number or an item name — so Excel knows exactly which row you mean.

There are three tools for this one job, and this module covers all three:

VLOOKUP — the old classic. It finds your key down the first column, then counts columns to the right to return the answer.
XLOOKUP — the modern upgrade. You simply point at the answer column; no counting, and it is exact by default.
INDEX-MATCH — the flexible pair. MATCH says which row, INDEX says fetch from this column. It can even look to the left.

Master these and you have cracked the single most-asked skill in every Excel interview and office test.

Think of it as asking a librarian: you give the code, they hand back the book. XLOOKUP = point at the answer.

🗺️

Visual Architecture

the whole system on one screen

Two quick maps: the first shows how the three tools relate; the second helps you pick the right one in seconds.

🧠 The lookup family at a glanceone job, three tools

View Mermaid.js source

mindmap
  root((Lookups))
    VLOOKUP
      Looks right only
      Needs FALSE
      Col index brittle
    XLOOKUP
      Any direction
      Exact by default
      Built in not found
    INDEX MATCH
      MATCH finds the row
      INDEX fetches value
      Can look left
    Errors and match
      NA when missing
      IFERROR to guard
      Exact vs approx

🧭 Which lookup should I use?start at the top and follow the arrows

View Mermaid.js source

flowchart TD
  A[Need a value from a table] --> B{Have XLOOKUP}
  B -->|Yes| C[Use XLOOKUP<br/>exact and any direction]
  B -->|No| D{Key in the left column}
  D -->|Yes| E[VLOOKUP with FALSE]
  D -->|No or need robust| F[INDEX MATCH]
  C --> G[Add if not found for a clean message]
  F --> G

🟡

The Deep Dive

Level 2–3 · core mechanics → expert edge

VLOOKUP means vertical lookup. It searches down the first column of a table for your key, then returns a value from a column to the right. It takes four arguments:

#	Argument	What it means	Example
1	lookup_value	the key you already know	"Pen"
2	table_array	the block to search; the key must sit in its first column	A2:B6
3	col_index_num	which column to return, counted from the left of the block	2
4	range_lookup	FALSE for an exact match, TRUE for approximate	FALSE

So =VLOOKUP("Pen",A2:B6,2,FALSE) reads: find Pen in the first column of A2:B6, then give me the value in column 2 of that block, matching exactly. The famous rule: always end with FALSE. Leave it out and Excel switches to approximate matching, which can silently hand back the wrong row.

Memory hook. VLOOKUP counts columns from the first column of your table — so count carefully. Insert a column and the count shifts, and the answer breaks.

VLOOKUP has three well-known limits. It cannot look left — the key must be the first column and the answer must be to its right. Its col_index is brittle: it is a fixed number, so inserting a column inside the table makes the 2 point at the wrong data. Its horizontal twin, HLOOKUP, is identical but searches across the top row and returns a value from a row below — handy when data runs left to right instead of top down.

XLOOKUP fixes almost every VLOOKUP headache. Instead of counting columns you point at two ranges — the column to search and the column to return:

=XLOOKUP(lookup, lookup_array, return_array, [if_not_found])

It looks in any direction — the return column may be left or right of the key.
It is an exact match by default — no FALSE to remember.
It has a built-in not-found reply: the optional 4th argument, as in =XLOOKUP("Pen",A2:A6,B2:B6,"Not found").
Insert a column and nothing breaks, because you named ranges instead of counting.

If your Excel has XLOOKUP, make it your default lookup.

Before XLOOKUP existed, the robust choice was INDEX-MATCH, and it still works in every version of Excel. It splits the job in two: MATCH finds which row, then INDEX fetches the value from that row.

=MATCH("Pen",A2:A6,0) returns the position of Pen in the list. The 0 means exact match — never forget it.
=INDEX(B2:B6, 1) returns the value at position 1 of the price column.

Find the row. =MATCH("Pen",A2:A6,0) looks down the item column and returns 1 — Pen is the first item.

Fetch the value. Drop that 1 into INDEX on the price column: =INDEX(B2:B6, 1) gives 10. Combined into one: =INDEX(B2:B6, MATCH("Pen",A2:A6,0)).

Because you name the answer column yourself, INDEX-MATCH can look left, survives inserted columns, and can fetch from a completely different column than the one you searched.

Two-way lookup. Need a value by both a row and a column — say the marks for a given student and a given subject? Nest a second MATCH where the column number goes: =INDEX(data, MATCH(student, names, 0), MATCH(subject, headers, 0)). The first MATCH picks the row, the second picks the column. This is INDEX-MATCH-MATCH, the tidiest way to read a grid in both directions.

Approximate match and tiers. Sometimes you do not want an exact hit — you want a band. To turn marks into grades, sort a small band table ascending and use approximate match (VLOOKUP's 4th argument TRUE, or XLOOKUP's match mode). Excel then finds the largest key that is not greater than your value — so 82 lands in the 75 band:

Marks from	Grade
0	Fail
40	Pass
60	First
75	Distinction

Taming #N/A. A lookup returns #N/A when the key simply is not there. Wrap it to show something friendly: =IFERROR(VLOOKUP(...),0) shows 0 on failure, and XLOOKUP's 4th argument does the same job even more cleanly.

Here is the whole family side by side, so you can see why XLOOKUP and INDEX-MATCH have largely retired the bare VLOOKUP:

Feature	VLOOKUP	XLOOKUP	INDEX-MATCH
Looks	right only	any direction	any direction
Exact match	needs FALSE	by default	MATCH with 0
Not-found built in	no	yes, 4th arg	no
Survives an inserted column	no	yes	yes
Works in old Excel	yes	no	yes

🔴

Expert Traps & Hacks

Level 4 · where 90% slip — and the exam edge

Lookups are where most people lose marks — usually to the same handful of mistakes. Learn these five and you will debug any broken lookup in seconds.

Trap 1 — forgetting FALSE. Without the 4th argument, VLOOKUP does an approximate match. On an unsorted list it will cheerfully return the wrong row and never warn you. The rule: every exact VLOOKUP ends in FALSE (or 0).

Trap 2 — a hard-coded column number. col_index is a fixed number like 2. Insert a new column in the middle of your table and that 2 now points at the wrong data, with no error to alert you. XLOOKUP and INDEX-MATCH never count columns, so they survive edits.

Trap 3 — the mystery #N/A. A lookup says #N/A when it cannot find the key. Nine times out of ten it is a stray space (clean it with TRIM), a number stored as text so 101 does not equal "101", or the key is simply not in the first column where VLOOKUP insists it must be.

Trap 4 — MATCH without the 0. Leave off the third argument and MATCH slips into approximate matching, which needs a sorted list and quietly misreports on an unsorted one. Always write MATCH(key, range, 0).

Trap 5 — the wrong direction. Reaching for VLOOKUP when the answer sits to the left of your key is a dead end — it can only look right. That is the moment to switch to INDEX-MATCH or XLOOKUP.

Pro hacks. Make XLOOKUP your everyday default. Stuck on an old file without it? Reach for INDEX-MATCH. Kill ugly errors by wrapping in IFERROR or using XLOOKUP's not-found argument. Before you blame the formula, run TRIM on your keys — invisible spaces cause more #N/A errors than anything else. And in interviews, saying 'I default to XLOOKUP, and use INDEX-MATCH when I must look left or support older files' shows you truly understand lookups.

💻

Interactive Sandbox — Formula Sandbox

type a real formula — the cell fills and turns green

Type a real lookup into the formula bar, press Run, and watch the target cell fill in — it turns green the instant your answer is right. Eight scenarios, gentlest first.

🧮 Formula Sandboxtype it · run it · green when right

0Correct

0Streak

🇬🇧 The complete Excel pathThis module is one of ten. Work through them in order — Grid Basics to Dynamic Arrays — then keep the Function Atlas open as your desk reference. All 10 Excel modules →