📊 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 type, but it is rock-solid, works in every version of Sheets and Excel, survives columns being moved, and can do something the others struggle with: 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 the combo survives inserted or moved columns
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 given situation

⏱️ Estimated Time: 50 minutes

🎯 Project: Rebuild last lesson's price lookup with INDEX/MATCH, 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 result range. So why bother with a third?

Three good reasons. First, INDEX/MATCH works everywhere — every version of Google Sheets and Excel, no matter how old the account, where XLOOKUP may be missing. Second, it is completely immune to column moves, because it never counts columns at all. And third, it unlocks the two-way lookup — pinpointing a value by matching a row label and a column label simultaneously — which is awkward or impossible with a plain VLOOKUP. Understanding INDEX/MATCH also makes you genuinely fluent: you'll finally see how a lookup works under the hood.

🧠 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 — Where Is It?

MATCH answers one question: "Where is this value? What position is it at in a list?" It returns a number — the item's position within the range.

📖 Definition

=MATCH(search_key, range, match_type)

search_key — the value you're locating.
range — a single row or column to search.
match_type — use 0 for an exact match (what you want almost always).

Using the same products catalog from last lesson (IDs in E2:E5):

=MATCH(A2, $E$2:$E$5, 0)

That searches the ID column for the value in A2 and returns its position. The 0 means exact match. If P-002 is the second ID in the list, MATCH returns 2. That's all it does — it doesn't fetch data, it just reports where the match sits.

INDEX — Value at a Position

INDEX answers the other half of the question: "Give me the value at a specific position in this range."

📖 Definition

=INDEX(range, row_number, [column_number])

range — the data to pull from.
row_number — which row of that range to return, counted from 1.
column_number — optional: which column, when the range is more than one column wide. (We'll use this for two-way lookups shortly.)

To grab the 2nd price from the prices column (G2:G5):

=INDEX($G$2:$G$5, 2)

That returns the 2nd value from the prices column — 4. 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

Now nest them: let MATCH find the position, and hand that position to INDEX to fetch the value:

=INDEX($G$2:$G$5, MATCH(A2, $E$2:$E$5, 0))

Read the whole thing: "Find the position of A2 in the IDs column, then return the value at that same position in the prices column." It returns 4 — the same answer VLOOKUP and XLOOKUP gave last lesson, reached a different way.

graph LR A["🔑 A2 = P-002"] --> B["MATCH finds position
P-002 is 2nd in the IDs"] B --> C["INDEX returns row 2
of the prices column"] C --> D["↩️ Result: 4"]

💡 Why INDEX/MATCH survives column changes

Unlike VLOOKUP's hard-coded 3, INDEX/MATCH never counts columns. It points at real ranges — the ID column and the price column — directly. Insert, delete, or rearrange columns in between, and the references adjust with the sheet. That resilience is why INDEX/MATCH was the pro's choice for years, and why it's still a great fallback anywhere XLOOKUP isn't available.

✅ Pro Tip — it can look left, too

Because the search range and the result range are named independently, INDEX/MATCH has the same "look left" freedom as XLOOKUP. Want the ID for a known price? Just swap which column MATCH searches and which column INDEX returns from — the result column can sit to the left of the key with no trouble at all.

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 — like reading a price from a grid where rows are products and columns are regions, or a shipping cost where rows are weights and columns are zones. 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
Pen	2.00	2.10	2.05
Notebook	4.00	4.20	4.10
Stapler	7.00	7.30	7.15

Suppose the row labels (Pen, Notebook, Stapler) 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 Notebook in G1 and South in G2 and it returns 4.20. 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 needs to be nested to manage it. The double-MATCH-into-INDEX approach reads naturally: one MATCH per direction. Once you've built one, you'll spot uses for it everywhere.

Choosing Your Lookup

You now know all three lookups. Here's how they compare, and a simple rule for picking one.

Function	Points at result by	Can look left?	Survives column moves?	Two-way lookup?	Availability
VLOOKUP	Column number (fragile)	No	No	No	Everywhere
XLOOKUP	A result range (robust)	Yes	Yes	Only when nested	Newer — varies by account
INDEX/MATCH	A result range (robust)	Yes	Yes	Yes (double MATCH)	Everywhere

✅ Which lookup, when?

XLOOKUP — your first choice for everyday one-value lookups when it's available. Cleanest to write, looks left, has built-in not-found handling.
INDEX/MATCH — when you need maximum compatibility, when XLOOKUP isn't in your account, or when you need a two-way lookup across a grid. Just as robust, and more flexible.
VLOOKUP — fine for quick, simple, right-ward lookups, and you'll meet it everywhere in other people's sheets, so you must be able to read it.

⚠️ Important Note: Don't agonize over the choice for a throwaway calculation — any of the three will do. The choice matters for sheets that will live: get edited, grow, and be handed to other people. For those, a range-based lookup (XLOOKUP or INDEX/MATCH) protects you from the silent breakage that dooms so many VLOOKUPs.

🎯 Project: INDEX/MATCH & a Rate Grid

Reopen the orders-and-products sheet from last lesson (or rebuild the little Products table). You'll first prove INDEX/MATCH matches your earlier results, then build a genuine two-way lookup against a rate grid.

🏋️ Build it two ways

Objective: Rebuild the price lookup with INDEX/MATCH, then read a rate from a grid by matching a row label and a column label at once.

Instructions (about 25 minutes):

(4 min) In a spare cell, write the price lookup for A2 as INDEX/MATCH, pointing MATCH at the ID column and INDEX at the price column. Confirm it returns the same number your VLOOKUP/XLOOKUP gave last lesson.
(3 min) Now flip it: write an INDEX/MATCH that takes a price and returns the matching ID — proving it can "look left."
(6 min) Add a new tab (e.g. Rates) so you don't overwrite last lesson's Orders and Products tables, and build the rate grid from this lesson there: row labels (Pen, Notebook, Stapler) in A2:A4, column labels (North, South, East) in B1:D1, and the numbers in B2:D4.
(6 min) On the same Rates tab, 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.
(4 min) Change the product in G1 and the region in G2 a few times and watch G3 jump to the correct cell of the grid each time.
(2 min) Wrap the two-way formula in IFNA so a mistyped label shows "Check labels" instead of #N/A.

💡 Hint — starter formulas

Price lookup, INDEX/MATCH:
=INDEX($G$2:$G$5, MATCH(A2, $E$2:$E$5, 0))

Look left — ID from a price:
=INDEX($E$2:$E$5, MATCH(4, $G$2:$G$5, 0))

Rate grid (on its own Rates tab):
        B1: North   C1: South   D1: East
A2: Pen       2.00        2.10        2.05
A3: Notebook  4.00        4.20        4.10
A4: Stapler   7.00        7.30        7.15

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:
=IFNA(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 VLOOKUP/XLOOKUP
You wrote a "look left" INDEX/MATCH that returns an ID 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
A mistyped label shows a friendly "Check labels" via IFNA

🎯 Quick Quiz

Question 1: In =INDEX($G$2:$G$5, MATCH(A2, $E$2:$E$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 $ signs so both the INDEX range and the MATCH range survive being dragged.
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 sheet that must work regardless of account or version.
Name your ranges (Data menu) when a formula gets long — Rates reads far clearer than $B$2:$D$4.

❌ 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.
A key stored as text ("007") won't match a number (7). Compare the raw cell values, not just how they look.

📓 Learning Journal

Keep a learning journal as you work through this course — a document, a note, or even a tab in your own spreadsheet. 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 in your own sheets
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 — and because it points at real ranges, it survives inserted or moved columns and can look left.
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 one-value lookups when available, INDEX/MATCH for maximum compatibility and two-way lookups, and read VLOOKUP because it's everywhere.
Wrap any lookup in IFNA 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 sheet instead of guessing. This is real power-user fluency.

❓ Common Questions at This Stage

Is INDEX/MATCH slower than VLOOKUP or XLOOKUP?

For ordinary sheets, the difference is imperceptible. INDEX/MATCH can actually be faster on very large data because it only scans the one search column. Choose based on clarity 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 sheet you inherit.

🔭 Looking Ahead

In the next lesson — Lesson 4.3: Conditional Aggregation — IFS, AND/OR, IFERROR & the *IF(S) Family — we shift from finding a single matching value to summarizing a whole subset: total sales for one region, count of tasks that are done, averages that respect multiple conditions. If lookups answer "what's the value for this one key," conditional aggregation answers "what's the total for everything matching these rules."

✅ 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 answer questions about whole groups at once. 📊