If you've ever needed to pull a number out of a big grid — a price list where rows are products and columns are regions, or a rate card where rows are job titles and columns are experience levels — a single VLOOKUP won't cut it. You need to match on two directions at once: the right row AND the right column. That's a matrix lookup, and it trips up more people than it should.
I've built this exact formula for sales commission grids, freight rate tables, and exam mark sheets more times than I can count. In this post I'll show you the formula that actually works, why the "easy" ones fail, and how to fix the mistakes that show up almost every time someone tries this for the first time.
1. The Quick Answer (30-Second Summary)
To look up a value from a grid using one criteria to pick the row and another to pick the column, wrap two MATCH functions inside one INDEX function:
=INDEX(DataRange, MATCH(RowCriteria, RowHeaders, 0), MATCH(ColumnCriteria, ColumnHeaders, 0))
In plain English: INDEX pulls a value out of a range once you tell it which row number and which column number to grab. The two MATCH functions are just there to figure out those row and column numbers for you, based on labels you actually recognize (like an employee name or a month).
2. The Anatomy: Breaking Down the Syntax
This looks intimidating the first time you see it because it's a formula nested inside a formula, nested inside another formula. Let's take it apart piece by piece.
| Part | What it means | Example |
|---|---|---|
INDEX(range, row_num, col_num) | Returns the value sitting at the intersection of a given row number and column number inside a range. | INDEX(C2:F10, 3, 2) returns the value in the 3rd row, 2nd column of that block. |
MATCH(search_key, range, 0) | Finds the position of a value inside a single row or single column. The 0 means "exact match" — don't skip this. | MATCH("Priya", B2:B10, 0) might return 4 if "Priya" is the 4th name down. |
First MATCH | Finds the row position, by matching against your row headers (left-hand column). | Matches an employee name against a list of names. |
Second MATCH | Finds the column position, by matching against your column headers (top row). | Matches a month name against a list of months. |
Think of INDEX as the "grab it" function and MATCH as the "where is it" function. You're really just asking two "where is it" questions and handing both answers to one "grab it" function.
3. Step-by-Step Practical Walkthrough
Let's use a realistic scenario: a sales team's monthly commission grid. Column A lists employee names, row 1 lists the months, and the grid in the middle holds each person's sales figure for that month.
| B: Jan | C: Feb | D: Mar | E: Apr | |
|---|---|---|---|---|
| Row 2: XYZ Traders Rep – ABC | 42000 | 38500 | 51000 | 47250 |
| Row 3: DEF | 29800 | 33100 | 30450 | 36700 |
| Row 4: Test User | 51200 | 49900 | 52300 | 55000 |
| Row 5: Example Employee | 36000 | 34200 | 39800 | 41100 |
Say the names sit in A2:A5, the months sit in B1:E1, and the sales figures sit in B2:E5. You want to find what "Test User" sold in March.
1Set up two cells for your criteria
Put the name you're searching for in one cell (say H1) and the month you're searching for in another (say H2). This makes the formula reusable — change the cell contents and the result updates instantly, instead of editing the formula every time.
2Find the row number with MATCH
=MATCH(H1, A2:A5, 0)
With "Test User" in H1, this returns 3 — because Test User is the 3rd name down in A2:A5.
3Find the column number with a second MATCH
=MATCH(H2, B1:E1, 0)
With "Mar" in H2, this returns 3 — because March is the 3rd month across in B1:E1.
4Feed both results into INDEX
=INDEX(B2:E5, MATCH(H1, A2:A5, 0), MATCH(H2, B1:E1, 0))
This returns 52300 — Test User's March figure — directly from the grid. No hardcoded row or column numbers anywhere, so if you insert a new employee row or reorder the months, the formula still finds the right cell.
While typing a nested formula like this, press Ctrl + Enter (Windows/Chrome OS) instead of just Enter if you want to confirm the entry without the cursor jumping to the next row — handy when you're building and re-checking the formula piece by piece.
Making It Fully Dynamic (Optional Upgrade)
If you want to protect this formula against someone accidentally typing "test user" in lowercase, or leaving a trailing space, wrap both search keys with TRIM — spreadsheets treat "Test User " (with a trailing space) as a completely different string from "Test User", even though it looks identical on screen.
=INDEX(B2:E5, MATCH(TRIM(H1), A2:A5, 0), MATCH(TRIM(H2), B1:E1, 0))
4. Excel vs. Google Sheets: What's Different
Good news first: the INDEX(range, MATCH(...), MATCH(...)) pattern is identical in both Excel and Google Sheets — same function names, same argument order, same logic. It's one of the rare formulas that copies over without any edits.
| Area | Excel | Google Sheets |
|---|---|---|
| Core formula | =INDEX(range,MATCH(...),MATCH(...)) | Identical syntax |
| Newer alternative | XLOOKUP nested inside itself, or XLOOKUP combined with XMATCH | XLOOKUP is available too, but doesn't natively support a two-dimensional lookup the way INDEX/MATCH does |
| Array-based option | SUMPRODUCT for multi-criteria math lookups | SUMPRODUCT works the same way here too |
| Autocomplete behavior | Shows argument tooltips inline | Shows a similar helper card, slightly less detailed for deeply nested formulas |
If you're on a recent version of either app and only need a simple two-way lookup (not a true matrix with multiple matching criteria on each axis), XLOOKUP nested inside another XLOOKUP is a valid shortcut in Excel. But INDEX/MATCH/MATCH is more universally compatible and still the most common professional standard, so it's worth learning properly either way.
5. Three Common Mistakes & How to Fix Them
Mistake 1: Forgetting the exact match argument
Leaving out the third argument in MATCH (or entering 1 instead of 0) tells the formula to look for the closest match assuming your data is sorted — which almost never applies to names or labels. This is the single most common reason people get a wrong number back instead of an error.
=MATCH(H1, A2:A5)
Right
=MATCH(H1, A2:A5, 0)
Mistake 2: Extra spaces or mismatched text
If your criteria cell says "Test User" but the source data has "Test User" (two spaces) or a stray space at the end from a copy-paste, MATCH throws a #N/A error because, character for character, the strings don't match. Wrap both sides in TRIM as shown earlier, or run Data → Data cleanup → Trim whitespace across your source range first.
Mistake 3: Mixing up row range and column range sizes
The row range you give the first MATCH must have the exact same number of rows as your INDEX range, and the column range for the second MATCH must have the exact same number of columns. If A2:A5 has 4 names but your data grid is B2:E6 (5 rows), the row numbers MATCH returns won't line up with the actual data, and you'll silently pull the wrong figure without any error at all — which is worse than a visible error, since nothing looks broken.
Always select your name range, header range, and data range together and double check the row/column counts match exactly before locking the formula in. A one-row mismatch is the classic "silently wrong number" bug in matrix lookups.
6. Frequently Asked Questions
Can I use this formula to match on more than two criteria, like a name AND a department AND a month?
Yes, but you'll usually combine INDEX with SUMPRODUCT or an array-based MATCH instead of stacking more plain MATCH functions, since basic MATCH only checks one condition against one range. For two conditions feeding into the row position (like name AND department both needing to match), you'd typically concatenate the criteria, e.g. MATCH(H1&H2, A2:A10&C2:C10, 0) entered so it evaluates as an array formula.
Why does my formula return #N/A instead of a number?
This almost always means one of the two MATCH functions can't find your search value in the header range — usually because of a typo, a text-vs-number mismatch (like a month typed as "March" when your header says "Mar"), or extra whitespace. Check each MATCH function separately in its own cell first to see which one is failing before troubleshooting the full nested formula.
Is INDEX/MATCH/MATCH slower than VLOOKUP for large sheets?
For a genuine two-way matrix lookup, this comparison doesn't really apply, since plain VLOOKUP can't do a two-dimensional lookup at all on its own. Performance-wise, INDEX/MATCH combinations are generally at least as fast as VLOOKUP on large ranges, and often faster, because MATCH only scans a single row or column rather than the whole data block.
Does the order of my data matter for this formula to work?
No — that's one of the main advantages over a sorted lookup. As long as you're using 0 (exact match) as the third argument in both MATCH functions, your row headers and column headers can be in any order, including random order, and the formula will still find the correct intersection.
Got a grid that this formula still doesn't quite crack? Try each MATCH in its own scratch cell first — nine times out of ten, that's where the real problem is hiding.
Comments