📊 Lesson 4.2: INDEX/MATCH & Choosing Your Lookup

Last lesson you met VLOOKUP and XLOOKUP. Now we add the third member of the lookup family — INDEX/MATCH, a two-function combo that power users leaned on for years before XLOOKUP existed. It's a little more to write, but it works in every version of Excel ever made, looks in any direction, doesn't break when columns move, and can do something the others can't cleanly: a two-way lookup that matches a row and a column at the same time. We'll finish with a clear decision guide so you always know which of the three to reach for.

📚 What You'll Learn

By the end of this lesson, you will be able to:

Use MATCH to find the position of a value in a list, and INDEX to return the value at a position
Combine them into INDEX/MATCH, and explain why it works in every Excel version and never breaks when columns move
Build a two-way lookup that matches both a row and a column to find a value at their intersection
Choose confidently between VLOOKUP, XLOOKUP, and INDEX/MATCH for any situation

⏱️ Estimated Time: 50 minutes

🎯 Project: Add an INDEX/MATCH version of last lesson's lookup for full compatibility, then build a two-way lookup that reads a value from a small rate grid by matching a row label and a column label at once.

In This Lesson

Why Learn Another Lookup?

Quick recap: a lookup takes a key from one place, finds the matching row somewhere else, and brings back a value. VLOOKUP does it by counting columns; XLOOKUP does it by pointing at a return column. So why bother with a third?

Three good reasons. First, INDEX/MATCH works in every version of Excel — back to the ancient ones — where XLOOKUP may be missing (Excel 2019 and earlier). That makes it the safe choice for workbooks shared with an unknown audience. Second, it is completely immune to column moves, because it points at real columns and never counts positions. And third, it unlocks the two-way lookup — pinpointing a value by matching a row label and a column label at once — which is awkward or impossible with a plain VLOOKUP. Understanding it also makes you genuinely fluent: you finally see how a lookup works under the hood.

We'll reuse last lesson's reference table — codes, products, categories, and prices in A1:D5:

Cell	A — Code	B — Product	C — Category	D — Price
Row 2	SKU-101	Notebook	Stationery	4.50
Row 3	SKU-204	Desk Lamp	Lighting	18.00
Row 4	SKU-330	Mug	Kitchen	7.25
Row 5	SKU-415	Backpack	Bags	32.00

🧠 Mindset — two simple jobs, combined

INDEX/MATCH looks intimidating because it's two functions nested together. But each half does one tiny, obvious job. MATCH answers "where is this?" (a position number). INDEX answers "what's at this position?" (the value). Learn each alone, then snap them together — that's the whole trick.

MATCH and INDEX — The Two Halves

The trick is that each function does one simple job, and you combine them:

MATCH(lookup_value, lookup_array, 0) finds the position of your key in a list. The 0 means exact match. In our table, =MATCH("SKU-204", A2:A5, 0) returns 2, because SKU-204 is the 2nd item in that column.
INDEX(return_array, position) returns the item at a given position in a list. =INDEX(D2:D5, 2) returns 18.00, the 2nd price.

On its own, INDEX needs you to know the position already. But you don't have to know it by hand — MATCH can figure it out for you. Which is exactly where the two meet.

INDEX/MATCH — The Flexible Classic

Nest MATCH inside INDEX and you get a lookup: "return the item from the Price column at whatever position SKU-204 sits in the Code column." Using cell F2 for the key:

=INDEX($D$2:$D$5, MATCH(F2, $A$2:$A$5, 0)) → 18.00

Read it inside-out: MATCH finds which row the code is on, then INDEX pulls the value from that row of the Price column. Like XLOOKUP, it points at real columns (so moving columns won't silently break it) and it can look in any direction — to look up a code from a price, you just swap the two ranges: =INDEX($A$2:$A$5, MATCH(18.00, $D$2:$D$5, 0)).

💡 Why it survives column changes

Unlike VLOOKUP's hard-coded column number, INDEX/MATCH never counts columns. It points at the Code column and the Price column directly. Insert, delete, or rearrange columns in between, and the references adjust with the sheet. That resilience — plus running in every Excel version — is why it was the pro's choice for years and remains the safe pick for widely shared workbooks.

Its one rough edge compared with XLOOKUP is that a miss returns a bare #N/A. You wrap it in IFERROR (which you'll meet properly next lesson) to get a friendly message:

=IFERROR(INDEX($D$2:$D$5, MATCH(F2, $A$2:$A$5, 0)), "Not found")

Two-Way Lookups — Matching a Row AND a Column

Here's where INDEX/MATCH earns its keep and steps beyond a simple one-column lookup. Sometimes the value you want sits at the intersection of a row and a column — a price by product and region, a shipping cost by weight and zone, a fee by tier and month. You need to match both a row label and a column label.

Say you have a small rate grid — products down the side, regions across the top:

	North	South	East
Notebook	4.00	4.20	4.10
Desk Lamp	18.00	18.50	18.20
Backpack	32.00	33.00	32.50

Suppose the row labels live in A2:A4, the column labels (North, South, East) live in B1:D1, and the numbers fill B2:D4. To find the rate for a product named in G1 and a region named in G2, use MATCH twice — once for the row, once for the column — and feed both to INDEX:

=INDEX($B$2:$D$4, MATCH(G1, $A$2:$A$4, 0), MATCH(G2, $B$1:$D$1, 0))

Read it: "in the grid of numbers, return the value at the row where the product matches and the column where the region matches." Put Desk Lamp in G1 and South in G2 and it returns 18.50. Change either label and the answer instantly moves to the right cell in the grid.

🧠 Why this matters

This is the pattern behind rate cards, tax tables, tiered pricing, scoring rubrics, and any "look it up in a table" reference. VLOOKUP can't do it cleanly, and even XLOOKUP has to be nested to manage it. The double-MATCH-into-INDEX approach reads naturally: one MATCH per direction. Build one and you'll spot uses everywhere.

Which Lookup Should You Use?

You now know all three lookups. Here's the honest, practical guidance, boiled down:

Function	Best for	Availability	Watch out for
`XLOOKUP`	New work — the default modern choice; cleanest, most capable	Microsoft 365, web, Excel 2021/2024	Missing in Excel 2019 and earlier (`#NAME?`)
`INDEX`/`MATCH`	Workbooks shared widely or that must run on older Excel; two-way lookups	Every version, and Google Sheets	Two functions to write; wrap in `IFERROR` for clean misses
`VLOOKUP`	Quick, stable lookups; reading existing workbooks	Every version	Always end with `FALSE`; can't look left; breaks if columns move

graph TD A["Need to fetch a value
by matching a key?"] --> G{"Matching a row AND
a column at once?"} G -- "Yes" --> H["Use INDEX with two MATCHes
a two-way lookup"] G -- "No, one key" --> B{"Microsoft 365, the web,
or Excel 2021/2024?"} B -- "Yes" --> C["Use XLOOKUP
exact match, look any direction, if_not_found"] B -- "No, Excel 2019 or older
or shared widely" --> D["Use INDEX and MATCH
works everywhere, any direction"] C --> E["Reading an old workbook
that already uses VLOOKUP?"] D --> E E --> F["Understand VLOOKUP too
and remember to end with FALSE"]

A simple rule of thumb: if you have XLOOKUP, use it for everyday one-value lookups. Use INDEX/MATCH when you must run on older Excel, share widely, or do a two-way lookup. Reach for VLOOKUP mainly when reading or lightly editing a workbook that already uses it. All three do the same fundamental job — the differences are about safety, flexibility, and where they'll run.

🎯 Project: INDEX/MATCH & a Rate Grid

Reopen the lookup sheet from last lesson (or rebuild the little reference table). You'll first make your lookup bulletproof across Excel versions with INDEX/MATCH, then build a genuine two-way lookup against a rate grid.

🏋️ Build it two ways

Objective: Add an INDEX/MATCH version of the price lookup for full compatibility, then read a rate from a grid by matching a row label and a column label at once.

Instructions (about 22 minutes):

(4 min) In your tracker, add a cell (or column) that fetches the price for a code using INDEX/MATCH instead of XLOOKUP. Confirm it returns the same number.
(3 min) Wrap it in IFERROR so a missing code shows "Not found" instead of #N/A.
(3 min) Now flip it: write an INDEX/MATCH that takes a price and returns the matching code — proving it can look left.
(6 min) Build the rate grid: row labels (product names) in A2:A4, column labels (North, South, East) in B1:D1, numbers in B2:D4.
(4 min) In G1 type a product and in G2 a region. In G3 write the two-way INDEX/MATCH(MATCH) that returns the rate at their intersection.
(2 min) Change the labels in G1/G2 a few times and watch G3 jump to the correct cell of the grid each time.

💡 Hint — starter formulas

Price lookup, INDEX/MATCH (if the code is in C2):
=IFERROR(INDEX(Reference!$C$2:$C$5, MATCH(C2, Reference!$A$2:$A$5, 0)), "Not found")

Look left — code from a price:
=INDEX(Reference!$A$2:$A$5, MATCH(18.00, Reference!$C$2:$C$5, 0))

Rate grid:
            B1: North   C1: South   D1: East
A2: Notebook    4.00        4.20        4.10
A3: Desk Lamp  18.00       18.50       18.20
A4: Backpack   32.00       33.00       32.50

Two-way lookup (product in G1, region in G2):
=INDEX($B$2:$D$4, MATCH(G1, $A$2:$A$4, 0), MATCH(G2, $B$1:$D$1, 0))

Graceful version:
=IFERROR(INDEX($B$2:$D$4, MATCH(G1, $A$2:$A$4, 0), MATCH(G2, $B$1:$D$1, 0)), "Check labels")

If the two-way formula errors, check that each MATCH points at a single row or single column, and that the labels in G1/G2 exactly match those in the grid (no trailing spaces).

✅ Project Completion Checklist

An INDEX/MATCH price lookup returns the same result as your earlier XLOOKUP
It's wrapped in IFERROR for a clean miss
You wrote a "look left" INDEX/MATCH that returns a code from a price
A rate grid exists with row labels, column labels, and numbers between them
A two-way INDEX/MATCH(MATCH) returns the value at the row×column intersection
Changing either label moves the result to the correct grid cell

🎯 Quick Quiz

Question 1: In =INDEX($D$2:$D$5, MATCH(F2, $A$2:$A$5, 0)), what job does the MATCH part do?

Question 2: You need to read a value from a grid by matching both a row label and a column label. Which approach fits best?

Best Practices for INDEX/MATCH

✅ Do's

Lock your ranges with $ so both the INDEX range and each MATCH range survive being copied.
Use 0 as MATCH's third argument for an exact match — the safe default.
Reach for INDEX/MATCH for two-way lookups and for any workbook that must run on older Excel or be shared widely.
Turn reference ranges into Excel Tables (Lesson 3.1) so they grow automatically and read clearly.

❌ Don'ts

Don't point MATCH at more than one row or column. Each MATCH searches a single line; give it one.
Don't forget the exact-match 0. Leaving it off can return an approximate, wrong result.
Don't fear the nesting. Build MATCH on its own first, confirm the position number, then wrap it in INDEX.

💡 Pro Tips

Debug a stubborn INDEX/MATCH by pulling the MATCH out into its own cell — if it returns the wrong position, your key or range is off, not INDEX.
Watch for a type mismatch: a code stored as text ("204") won't match a number (204). Keep both sides the same type.

📓 Learning Journal

Keep a learning journal as you work through this course — a separate document, a note, or even a second worksheet right in the workbook you're building. After each lesson, take a few minutes to write down:

Key concepts you learned
Techniques that clicked for you
Questions or confusion points to revisit
Ideas you want to try with your own data
Your progress and feelings about learning this — including where your confidence grew

✍️ This lesson's prompt: Where in your own work is there a "grid" you look things up in — a price by product and region, a fee by tier and month, a score by category and level? Sketch what the row labels and column labels would be, and describe the two-way lookup you'd build. Then note: of VLOOKUP, XLOOKUP, and INDEX/MATCH, which will you default to, and why?

📝 Lesson Summary

🎓 Key Takeaways

MATCH returns the position of a value in a list; INDEX returns the value at a given position.
INDEX/MATCH nests them — MATCH finds the row, INDEX fetches the value — works in every Excel version, looks in any direction, and never breaks when columns move.
A two-way lookup uses INDEX with two MATCHes (one for the row label, one for the column label) to return the value at their intersection — the pattern behind rate cards and tax tables.
Choose XLOOKUP for everyday lookups when available, INDEX/MATCH for compatibility and two-way lookups, and read VLOOKUP because it's everywhere.
Wrap INDEX/MATCH in IFERROR to keep a friendly message on the not-found case.

🎉 What You've Accomplished

You've completed the lookup family. You can now pull a value from another table three different ways, explain how a lookup works under the hood, read a value from the intersection of a row and a column, and choose the right tool for a lasting, widely-shared workbook instead of guessing. That's real, employable Excel fluency.

❓ Common Questions at This Stage

Is INDEX/MATCH slower than VLOOKUP or XLOOKUP?

For ordinary workbooks the difference is imperceptible. On very large data it can actually be faster because it only scans the one lookup column. Choose based on clarity, compatibility, and robustness, not speed, until you're working with tens of thousands of rows.

Can XLOOKUP do a two-way lookup too?

Yes, by nesting one XLOOKUP inside another, but it reads less naturally than INDEX with two MATCHes. Many people find the double-MATCH pattern clearer for grids, which is another reason INDEX/MATCH stays relevant.

Do I really need to memorize all three?

Memorize the concept — key, match, return — and one you'll write from muscle memory (usually XLOOKUP or INDEX/MATCH). Keep the others as recognition knowledge so you can read and repair any workbook you inherit.

🔭 Looking Ahead

In the next lesson — Lesson 4.3: Conditional Logic — IFS, AND/OR, IFERROR & the *IF(S) Family — we teach your spreadsheet to make decisions. You'll build status flags with IFS, combine conditions with AND/OR, catch errors gracefully with IFERROR, and total up categories with SUMIFS and COUNTIFS. Together with lookups, that's the logic layer of a real dashboard.

✅ Before the Next Lesson

Make sure your two-way rate-grid lookup returns the right cell when you change either label
Rebuild the price lookup as INDEX/MATCH once more from memory, without the hint
Write your Learning Journal entry for this lesson

📚 Additional Resources

🌟 Encouragement for the Journey

INDEX/MATCH is the formula that used to separate spreadsheet dabblers from the pros — and you just built it, including the two-way version most people never learn. From here, a lookup will never intimidate you again. Next, we teach your sheet to think. 📊